EntityFramework: How to exclude deleted objects from Queries

Question

The following code demonstrates the problem I am having with Entity Framework. Assume that 'bob' has many records in the sessions table.

I expect sessionCountB to be 1 less than sessionCountA:

using (var context = new MyEFContext)
{
   int sessionCountA = (from a in context.sessions 
                        where a.user = 'bob' select a).Count();

   sessions firstSession = (from a in context.sessions
                                  where a.user = 'bob').FirstOrDefault();

   context.sessions.DeleteObject(firstSession);

   int sessionCountB = (from a in context.sessions 
                        where a.user = 'bob' select a).Count();

    // I expect sessionCountB == sessionCountA - 1
}

I know that DeleteObject only marks an object for deletion - SaveChanges makes the deletion on the database.

But should the deleted object not be excluded from further queries I perform on the same data model, before calling SaveChanges?

Answer 1

solutions

1. if u dont want to see entities that in the deleted state, just filter them explicitly

_context.sessions.Where(a => _context.Entry(a).State != EntityState.Deleted);

2. get "Local" collection after you load the collection (var x = _context.Accounts) from db, y = x.Local will filter entities with deleted state. Note - without Local u also wont see freshly added entities in collection you loaded from db. great example and explanation

Answer 2

When you create a query, I would expect Entity Framework to cache the result from the database, and those exact results when you rerun the exact query on the same context because that's what the database reflected at the time of the query.

Think about what would happen if you added an entity, what would you do about the primary keys? Or what happens when you submitted the changes to the database and you have some foreign key constraint that prevented the delete from succeeding? I would say this is an expected behavior.

As a workaround, you could get all items from the database and store the results in a collection in C#, then do the delete from the server side collection, get the count on that collection and commit the delete to the database later.