Efcore: Filtered includes not working with In Memory DB provider

[a-keen]

Using Microsoft.EntityFrameworkCore.InMemory v5.0.0-preview.4.20220.10, I am having an issue with the Filtered Include and the In Memory DB provider.

The following screenshot works when I use a SQL Server provider (Microsoft.EntityFrameworkCore.SqlServer v5.0.0-preview.4.20220.10), as in the extra WorkOrderDocuments are filtered out properly in line 2.

However, when using an In Memory DB provider, it does not filter out the extra WorkOrderDocuments and therefore throws a System.InvalidOperationException : Sequence contains more than one element because there is not a "Single" WorkOrderDocument in the list of WorkOrderDocuments on the workOrder entity. The exception is thrown in the last line when trying to call SingleOrDefault()

[smitpatel]

@a-keen - Please share a small runnable code which demonstrate the issue you are seeing. My suspicion is that entities are already tracked when running the query for InMemory hence the result does not correspond to the filtered include. That is always going to be case for filtered include.

[a-keen]

Hi @smitpatel. Thank you for the response. In creating sample code to reproduce the issue, I discovered that I was unable to. It turns out the entities in my code were already being tracked in memory since they were seeded for the purpose of the unit test being executed like you had said.

What is a good way to clear that state, so that I can seed entities and then query for them without them already being tracked?

[a-keen]

I ended up also putting the expression from the Where() clause in the last SingleOrDefault(). This way, in the ideal case it is always filtering on a list with 1 entity but if other items have already been loaded into memory it will filter them out there.

[smitpatel]

Use different context instances for seeding the database and querying the database, like how it would be in actual business code.