Entity Framework: Why does the database get hit if the data is in the context?

Question

Why would the database be hit to find a record that is already represented in the ObjectContext?

So here's what I thought would happen when you query:

SiteUser someUser = context.SiteUser.Where(role => role.UserID == 1).ToList()[0];

Junk example but the idea is that I want to get a user from the table with the id of 1. Now assume this is the first time through so what I would guess is that it has to create the SiteUser list on the context, query the database, and then fill it's list. Using profiler I see this:

SELECT 
 [Extent1].[UserID] AS [UserID], 
 [Extent1].[MainEmail] AS [MainEmail], 
 [Extent1].[Password] AS [Password], 
 [Extent1].[UserName] AS [UserName]
FROM [TIDBA].[TI_USER] AS [Extent1]
WHERE 1 = [Extent1].[UserID]

Beautiful. It did what I expect and in the SiteUser list (if I dig far enough using Watch) I can see that there is one item in the context SiteUser list and it happens to be the hydrated object that represents this data row.

Next I want to change something without saving:

someUser.UserName = "HIHIHI";

Now say for some reason I want grab it again Using the same context (This is a weird example but it's actually a test so I could prove this happening) :

someUser = context.SiteUser.Where(role => role.UserID == 1).ToList()[0];

What I think would happen is it would look at the SiteUser list on the context since that's what the generated property says. (If not null, return list) Then it would look to see if it's there and return it. No database hit. Guess what profiler says...

SELECT 
 [Extent1].[UserID] AS [UserID], 
 [Extent1].[MainEmail] AS [MainEmail], 
 [Extent1].[Password] AS [Password], 
 [Extent1].[UserName] AS [UserName]
FROM [TIDBA].[TI_USER] AS [Extent1]
WHERE 1 = [Extent1].[UserID]

Hrm. Ok so I start thinking that maybe it's a gut check to see if anything has changed on that data item and update the SiteUser object ONLY on values I haven't changed on the client. (Sort of like context.Refresh(RefreshMode.ClientWins, context.SiteUser) ) So I have it stopped at the :

someUser = context.SiteUser.Where(role => role.UserID == 1).ToList()[0];

Line and I change a value in the database (Password column) and let it hit the database. Nothing changed on the object.

Something doesn't seem right here. It hits the database to select the object I already have hydrated in the context yet it doesn't apply the change I manually made in the database. Why is it hitting the database at all then?

UPDATE Thanks to some links below, I was able to dig in a bit and find this:

Merge Option

Looks like there is an enumeration that is set to tell how to deal with loads. Now after reading that I saw this for MergeOption.AppendOnly:

Objects that already exist in the object context are not loaded from the data source. This is the default behavior for queries or when calling the Load method on an EntityCollection<(Of <(TEntity>)>).

This would suggest that if I have it in the context, there should be no hit to the database. However, this doesn't seem to be true. It would make sense if OverwriteChanges or PreserveChanges were the defaults, but they are not. This seems to be contradictory to what is supposed to happen. Only thing I can think of is that "loaded" just means there are no overwrites. However, it doesn't mean there are no queries to the database.

Answer 1

context.SiteUser is an property of type ObjectQuery. When you execute an ObjectQuery, it will always hit the backing store. That's what they do. If you don't want to execute a database query, then don't use an ObjectQuery.

It sounds like what you really want is a function which says, "If the entity is already materialized in the context, then just return it. If it isn't, then go grab it from the database." As it happens, ObjectContext includes such a function, called GetObjectByKey

GetObjectByKey tries to retrieve an object that has the specified EntityKey from the ObjectStateManager. If the object is currently not loaded into the object context, a query is executed in an attempt to return the object from the data source.

Answer 2

IMO, the reason that EF hits the database a second time is to make sure that there aren't any additional rows in the db that satisfy the query. It's possible that additional relevant rows have been inserted into the table since the first query was issued, and EF is seeing if any of those exist.