C# Entity Framework Linq Query not recognizing new changes

Question

I'm using C#, .NET (4.0) and Entity Framework to connect to SQL CE 4.0.

I query some objects with specific properties, but the query returns only objects that meet search criteria only if that data was already saved to database, which is not that problematic, bigger problem is that if data is changed, but not yet saved to database it will still meet search criteria.

Example:

var query = from location in mainDBContext.Locations
            where location.InUse == true
            select location;

This query returns also objects where location.InUse = false if InUse was true when loaded from DB and then changed later on in code.

This is screen capture from one of the query results objects.

enter image description here

I really don't understand why it does this. I would understand if this query would always query database and I would get the older version of this object (thus InUse would be true).

Thank you for your time and answers.

score 3 · Accepted Answer · edited May 23 '17 at 10:32

That is how EF works internally.

Every entity uniquely identified by its key can be tracked by the context only once - that is called identity map. So it doesn't matter how many times did you execute the query. If the query is returning tracked entities and if it is repeatedly executed on the same context instance it will always return the same instance.

If the instance was modified in the application but not saved to the database your query will be executed on the database where persisted state will be evaluated but materialization process will by default use the current data from the application instead of data retrieved from the database. You can force the query to return state from the database (by setting mainDBContext.Locations.MergeOption = MergeOption.OverwriteChagens) but because of identity map your current modifications will be lost.

Thank you for your answer. Is it possible to execute query on local data? — Ben, Mar 17 '12 at 18:11
[Yes it is possible](http://stackoverflow.com/questions/8210267/entityframeworks-object-cahe-getting-already-retieved-objects/8210523#8210523). — Ladislav Mrnka, Mar 17 '12 at 18:36

score 1 · Answer 2 · answered Sep 05 '13 at 21:03

I just ran into the same thing myself. It's a bit messy, but another option is to examine the Local cache. You can do this, for example:

var query = from location in mainDBContext.Locations.Local
            where location.InUse == true
            select location;

This will only use the local cache not saved to the database. A combination of local and database queries should enable you to get what you want.

score 1 · Answer 3 · answered Mar 17 '12 at 18:00

I'm not really sure what exactly your problem is, but I think you have to know this:

That kind of query always return data that is submitted into DB. When you change some entities in your code but they are not submitted into database the LINQ query will query the data from database, without your in-code changes.
LINQ queries use Deferred Execution, so your 'query' variable is not a list of results, it's just a query definition that is evaluated each time results are needed. You should add .ToList() to evaluate that query and get a list of results in that certain line of code.

An example for .ToList():

var query = (from location in mainDBContext.Locations
                 where location.InUse == true
                 select location).ToList();

C# Entity Framework Linq Query not recognizing new changes

3 Answers3