I have this situation:
SQL Server database used with EF Core (code-first strategy).
I have an entity containing a boolean flag called Inactive - with the meaning: when TRUE, this entry is regarded as "not active".
Sample code:
var first_result = _dbSet.Where(b => !b.Inactive && some_other_check && some_more);
var first_item = first_result[0];
first_item.Inactive = true;
_dbSet.Update(first_item);
// identical with the first where - see above
var second_result = _dbSet.Where(b => !b.Inactive && some_other_check && some_more);
One would expect, that a comparison like this:
first_result.length() != second_result.length()
Should be TRUE, however this is not the case!
More disturbingly, the second WHERE returns a collection that actually has it first item with Inactive = true which should be impossible give the WHERE condition !Inactive.
I spend some time poring over this and came to the conclusion, that there must be some kind of caching (?) and that I am becoming my first result again with the first item set to Inactive=true...
How can this be? Can somebody please shed some light on this unexpected and (from my point of view) illogical behavior.
Thank you very much in advance!
PS: I have found out (trough trial and error) that calling the SaveChangesAsync method of the involved DbContext in between will make it work as expected
As mentioned in the comment, you haven't committed any changes to the database for the first item.
When you iterate over your second query:
var second_result = _dbSet.Where(b => !b.Inactive && some_other_check && some_more);
EF still builds a query expression to execute against the database, and the first item you modified has not been changed, so it will be returned as "active". The fact that you have updated the tracked instance in EF isn't considered yet. IF you have tracked changes that you want to consider in your final results and cannot save those changes then you can re-query your conditions against the materialized set returned. For instance:
var second_result = _dbSet.Where(b => !b.Inactive && some_other_check && some_more);
first_result.length() != second_result.ToList().Where(b => !b.Inactive).length()
This would reflect the changes to the tracked entities as the instance of the modified row in a materialized set from second_result would be the same reference. This, however, would not work if second_result performs a non-tracking query with .AsNoTracking() in this case the row you modified but did not save would be returned and would not point to the same reference.
Basically if you make changes to entities that you want considered in future queries, commit the changes with SaveChanges.