CODEWITHSUNDEEP
c#entity-framework
Enthusiastic Programmer
Enthusiastic Programmer

Reputation: 307

EF 6.0 incorrect items after where clause

I had a question about EF 6.0 after fetching an item from a DB with EF (FirstOrDefault) modifying a value and then asking for the same item in another collection with a Where clause.

For instance assume the class Foo with 3 properties: Id, Desc, StatusId where Foos is the EntitySet. Initially after the first fetch:

var item = Foos.FirstOrDefault(f => f.Id = 5);
// item values are: Id:5, Desc:"Whatever", StatusId:1 after fetching from database

// Change statusId
item.StatusId = 5

// statusItems will not contain above object HOWEVER...
var statusItems = Foos.Where(f => f.StatusId == 5).ToList();

// allItems will contain item, with StatusId = 5
var allItems = Foos.ToList() // Or FirstOrDefault

Is this behaviour to be expected from EF? If so, can you force the where clause to run over the attached objects in the DbContext without specifying .ToList() first?

A possible fix I found for the above is:

var item = Foos.FirstOrDefault(f => f.Id = 5);
// item value are: Id:5, Desc:"Whatever", StatusId: 1 after fetching from database

// Change statusId
item.StatusId = 5

// statusItems will now contain item
var statusItems = Foos.ToList().Where(f => f.StatusId == 5).ToList();

Another way to get around it is to wrap it in a Transaction I assume and calling SaveChanges after modifying the StatusId property.

I take it there is no way around it, apart from knowing how it works (and as such try to filter on another property that did not change to make sure you don't drag the entire table to the client before filtering)?

Upvotes: 1

Views: 180

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109185

The statement...

var statusItems = Foos.Where(f => f.StatusId == 5)

...always goes to the database. The SQL query only returns object that have StatusId == 5 in the database at that moment. The object item has not been saved yet, so it is not included.

So what you did here was fetch an object having some StatusId (probably not 5), changed it to StatusId = 5 and then fetched more object that already had StatusId = 5. The number of items in the context is now the number of objects from the latest query + 1.

can you force the where clause to run over the attached objects in the DataContext?

(DbContext, by the way) Yes, by querying the local collection:

Foos.Local.Where(f => f.StatusId == 5)

In this situation, this statement will return all Foo items you have in the context so far.

When you do Foos.ToList(), all foos will be fetched from the database. By default, EF will not overwrite items it's already tracking. After all, you might lose changed you made. So this statement will append new foo items to the Local collection that were not there yet.

Upvotes: 1

Bas
Bas

Reputation: 27105

What happens is the MergeOption you are using, most likely AppendOnly (the default value), has specific behavior. Entity Framework holds a list of objects already materialized in memory. To illustrate what is happening:

var item = Foos.FirstOrDefault(f => f.Id = 5);

Entity framework now has (Id=5) in memory

var statusItems = Foos.Where(f => f.StatusId == 5).ToList();

All items are retrieved from the database that have StatusId = 5 inside the database! This does not include the object with (Id=5) because changes have not been synchronized with the database yet using SaveChanges.

var allItems = Foos.ToList();

Now you have a list of all items from the table. The AppendOnly merge option does the following:

Id=1 - Materialize object
Id=2 - Materialize object
...
Id=5 - Already exists! Give the existing instance
...

The conclusion is: when using the AppendOnly option there are two states, the database state and the entity framework cache state. If you query the Foos list it will always go to the database, but the returned objects are matched by primary key value to what already exists. If an existing object is found, that instance will be returned.

That also explains why your second situation will return the object in question, you first retrieve the whole table, then filter it.

Without more context, it is hard to make a suggestion for solving this issue. Most likely, you want to save earlier.

Upvotes: 3

Related Questions