Should you use the EF Core in-memory provider for tests?
Quick Answer
The EF Core in-memory provider is not a relational database — it doesn't enforce constraints, doesn't do referential integrity, doesn't translate real SQL, and behaves differently for transactions and concurrency. Microsoft recommends against it for testing query logic. Prefer SQLite (in-memory or file) or a real SQL Server via Testcontainers.
I avoid the EF in-memory provider for anything meaningful. It's not actually relational — it won't fail on a duplicate key, it doesn't enforce foreign keys, and it doesn't translate the SQL your production provider would, so tests pass that would break against SQL Server. Microsoft themselves say don't use it for testing queries. For integration tests I use a real SQL Server in a container via Testcontainers, or SQLite if I need speed and accept the dialect differences. And for genuine unit tests I don't touch a database — I fake the repository.
- In-memory: fine only for testing simple, provider-agnostic code that just adds/reads entities without relying on DB behaviour.
- SQLite: relational, fast, catches constraint violations and most query issues — but its SQL dialect differs from SQL Server (dates, some functions).
- Testcontainers + real SQL Server: highest fidelity, tests the actual provider translation, at the cost of Docker and startup time.
- For pure unit tests, don't use a database at all — mock the repository or use an in-memory fake collection.