Update in-memory database testing guidance

[thomasgalliker]

As we all know, InMemoryDatabase does not reflect the behavior of a real, relational database such as MSSQL. Isn't it very dangours to swap the sql database provider against something that is not used in production just to improve test execution speed? What I usually prefer is to write a big bunch of unit tests with mocked data repositories. Those tests are quick and cover the data-consuming services. Later, I also want to make sure that IQueryable.Where(...) etc.. statements are executed properly on the database. In this case there is no way around using the real database provider - or am I wrong? How can InMemoryDatabase check if my IQueryable.Where(...) is correct if it's not executed against a relational database?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: c95e5c7e-882f-76f7-5301-2a5d1ede39d3
• Version Independent ID: 8754aa99-67a7-98ac-a51e-0ba995f96414
• Content: Testing with InMemory - EF Core
• Content Source: entity-framework/core/miscellaneous/testing/in-memory.md
• Product: entity-framework
• GitHub Login: @rowanmiller
• Microsoft Alias: divega

[ajcvickers]

@thomasgalliker Totally agree. I think we should document this more clearly. /cc @divega

[divega]

@thomasgalliker @ajcvickers There may be some specific improvements we can do, but I am not sure what they are exactly. For example, the section https://docs.microsoft.com/ef/core/miscellaneous/testing/in-memory#inmemory-is-not-a-relational-database already goes on to to describe what in-memory is and isn't.

I don't necessarily agree that using the in-memory provider is more dangerous than doing your own repository test doubles, as long as you know what you are doing and I don't think that our documentation is misleading in this regard.

I don't understand what you mean with the IQueryable.Where() scenario. There are more chances that you will get the same results that you would get with a real database if you use the in-memory provider than if you use your own repository test doubles, because the query will be processed through the EF Core stack, with change tracking identity resolution, etc. On the other hand, the in-memory provider falls short when compared to using a real relational database, or even SQLite in in-memory mode. But this page also covers that.

All in all, I would be grateful if you could mention specific paragraphs that you think need improvement or are completely wrong in this page. It would be even better if you could send a PR with the specific changes you would like to propose.

[thomasgalliker]

Would it be an idea to point out a concrete difference between InMemoryProvider and a relational database with help of an example? I’m new to EFCore but I have decent knowledge of EF6. In EF6, my experience is that IQueryable which use “linq to entities” in the background often produce exceptions when run against a relational database - wheareas they run perfectly fine against an in-memory list (which fakes a database table). One example is if you run IQueryable.Where(x => x.Name.ToLowerInvariant() == “test”); ToLowerInvariant simply doesnt cannot be translated to an SQL equivalent (as far as I know).

The thing I would prefer is that people are more aware of the fact that it doesnt help to replace the real database against an in-memory equivalent as it’s just not the same. The argument to “speed up unit tests with this simple replacement” doesnt count, from my opinion. Unit tests are there to ensure quality. If I test my software under different conditions that it later runs in production, I see a certain quality risk.

Can you understand my concerns somehow or am I paranoid :)? Thanks for your time and the great efffort in EF/Core.

[divega]

@thomasgalliker thanks for the additional details. Yes, I understand this particular concern. We should add this difference between in-memory and real databases to the list.

We should also clarify that the idea of speeding up your tests has always been about speeding up the majority of your test runs. Using test double repositories, fake databases like EF Core in-memory or even SQLite in-memory mode is ok as long as from time to time you still check (with a slow run) against the same database you will use in production that all tests pass.

I believe we don’t mention this because we gave it for granted, but it is important and it should be there.

[ajcvickers]

See also discussion here: dotnet/efcore#14866

[thomasgalliker]

I think we can close this. The introduction of this page clearly states the risks of testing against an in-memory database. Thanks for your effort, guys.

[ajcvickers]

See also collation: #2172