InMemory provider queries on tracked entities (unlike SQLServer provider)

[wizofaus]

With the InMemory provider, if you modify the value of an entity returned by EF, then immediately query on it, the query successfully finds the entity.
However when using SQLServer it doesn't, as the change hasn't been committed to the database yet.

E.g.

 class MyContext : DbContext
 {
       internal DbSet<MyObject> Objects { get; set; }

       void Foo()
       {
              Objects.FirstOrDefault().Value = 42;
              Console.WriteLine($"Found: {Objects.FirstOrDefault(o => o.Value == 42) != null}");
       }
 }

Assuming your database contains at least one Object with a value field not equal to 42, then using InMemory, this prints out "Found: true", but using SQLServer, prints out "Found: false".

Adding "SaveChanges();" before performing the query causes the behaviour to be the same, but as it is this discrepancy hid a rather nasty bug in my server that didn't show up in any of the automated testing.

[ghost]

I've noticed the same thing with DB generated fields (i.e. Id's generated by a sequence). With In Memory they get set right away, with Sql Server the only get set on SaveChanges. I also had a nasty bug where I left a SaveChanges out and it passed tests and failed when switching to the real backend

[wizofaus]

There's at least 3 different ways in which In-Memory provider works differently to the SQL-server provider. I'm happy to contribute a fix if there's nobody else working on it.

[ErikEJ]

@wizofaus Have you read this? https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/in-memory

[ajcvickers]

Note for triage: I was able to reproduce the case above. It looks like the in-memory query provider is doing identity resolution to the tracked entity before evaluating the predicate.

@david-waterworth @wizofaus The in-memory database will never behave exactly like SQL Server, or indeed any other relational database. However, we are tracking some issues to add additional functionality--for example, #6872

[wizofaus]

Sure, I'd never expect it to behave exactly the same, and indeed previous In-Memory DbSet libraries for EF 6 (.net Framework) had the same problem (or even worse - no distinction at all between tracked entity data and persisted data). But the fact that "real" databases query against the underlying data rather than current tracked entities would seem to be a useful behaviour to emulate.

[ajcvickers]

@wizofaus The case you reported above is a bug. In-memory queries are made against the in-memory database, not the tracked entity instances.

[smitpatel]

InMemory is working same as SqlServer in 3.1 release. We don't track against tracked entities anymore.