Why can EF Core 10 tests on the in-memory provider pass while the same code fails against SQL Server or PostgreSQL?
answer
- not a relational database
- LINQ runs as .NET code
- transactions throw by default
- no raw SQL at all
- no constraints beyond keys
basics
~20 sEF Core's in-memory provider is not a relational database: it runs LINQ as .NET code over collections, sends no SQL, supports no transactions or raw SQL and enforces no unique indexes or foreign keys, so it hides what production databases add.
solid answer
~40 s`UseInMemoryDatabase` keeps entities in .NET collections and evaluates queries as LINQ over them, so comparisons follow .NET rules: `p.Surname == "okafor"` is case-sensitive there, while SQL Server's default collations compare case-insensitively. No SQL is ever sent, so `FromSql`, `ExecuteSql`, `HasDefaultValueSql` and provider functions such as Npgsql's `EF.Functions.ILike` have nothing to run on, and the set of LINQ it can translate differs from any relational provider's. Starting a transaction throws by default, because the provider sets `InMemoryEventId.TransactionIgnoredWarning` to throw; ignoring that warning makes rollback a no-op. The store enforces no unique indexes or foreign keys, though `UseInMemoryDatabase` does switch on required-property null checks. The EF Core docs discourage it for testing, and it receives no new features.
code
csharp · 16 linesvar options = new DbContextOptionsBuilder<ClinicContext>()
.UseInMemoryDatabase("clinic-tests")
.Options;
await using var db = new ClinicContext(options);
db.Patients.Add(new Patient { Email = "[email protected]", Surname = "Okafor" });
db.Patients.Add(new Patient { Email = "[email protected]", Surname = "Okafor" });
await db.SaveChangesAsync(); // succeeds: the unique index on Email is not enforced
var matches = await db.Patients.CountAsync(p => p.Surname == "okafor");
// 0 here: .NET string equality is case-sensitive;
// SQL Server's default case-insensitive collation would return 2
await db.Database.BeginTransactionAsync();
// throws: TransactionIgnoredWarning is configured to throw by UseInMemoryDatabasego deeper
Recall that the in-memory provider is not relational: no SQL, no transactions, no raw SQL, and string comparisons follow .NET rules.
Explain which differences make a green in-memory test meaningless: case-sensitive comparisons, unenforced unique and foreign keys, the transaction warning and provider functions.
Show how you triage a test that passes in memory but fails in production, and which behaviours must be tested against the real engine.
Set the team's rule for where fake providers are acceptable, weighing suite speed against the production defects they let through.
## What the in-memory provider is `Microsoft.EntityFrameworkCore.InMemory` lets a `DbContext` store its entities in **.NET collections** inside the test process. It was built to test EF Core itself, and the EF Core documentation describes it as not designed for production, not optimized for performance, discouraged as a testing database and receiving **no new features**. The important word is *not relational*: there is no SQL, no query planner, no transaction log and no constraint engine. When a clinic app's tests pass against it and production fails, the provider is usually doing exactly what it was designed to do. ## Where it differs from SQL Server and PostgreSQL | Behaviour | In-memory provider | SQL Server / PostgreSQL | |---|---|---| | How a query runs | LINQ evaluated as .NET code | translated to SQL and run by the engine | | String equality | .NET ordinal, case-sensitive | collation-dependent; SQL Server's defaults are case-insensitive | | Raw SQL (`FromSql`, `ExecuteSql`, `SqlQuery`) | unavailable | supported | | SQL in the model (`HasDefaultValueSql`, `HasComputedColumnSql`) | never executed | run by the database | | Transactions | `BeginTransaction` throws by default | real commit and rollback | | Unique indexes and foreign keys | not enforced | enforced | | Required properties | null checks on by default | `NOT NULL` columns | | Provider functions (`EF.Functions.ILike`, `DateDiffDay`) | cannot run | translated by their provider | Three of these explain most surprises: 1. **Different semantics.** A surname search that matches `Okafor` for the input `okafor` on SQL Server finds nothing in memory, or the reverse when production is PostgreSQL. The test proves the .NET comparison, not the database's. 2. **Different translation.** The in-memory provider has its own query pipeline. The docs say it generally supports fewer query types than even SQLite, and a query can also behave differently from the production translation. 3. **Missing guarantees.** Two patients with the same e-mail save successfully because the unique index is not enforced, and an appointment whose `PatientId` matches no patient saves because the foreign key is not checked. ## Transactions: a failure on purpose Booking code that calls `Database.BeginTransactionAsync()` fails on the in-memory provider: `UseInMemoryDatabase` configures `InMemoryEventId.TransactionIgnoredWarning` to throw. You can ignore it with `ConfigureWarnings(w => w.Ignore(InMemoryEventId.TransactionIgnoredWarning))`, but then the transaction is simply skipped, so a test that relies on rollback undoing changes proves nothing. ## What it does check It is not a free-for-all. `UseInMemoryDatabase` switches on **null checks** for required properties, so a `Patient` saved without its required `Surname` fails, and the change tracker still enforces **key uniqueness** among tracked entities and fixes up relationships as it does with any provider. `EnableNullChecks(false)` turns the null checks off. ## Why provider functions fail there Methods such as Npgsql's `EF.Functions.ILike` or SQL Server's `EF.Functions.DateDiffDay` are extension methods on `DbFunctions`. They compile against any context, but their bodies only throw: they exist so that **their own provider** can recognise them and translate them to SQL. On the in-memory provider nothing translates them, so the query fails as soon as it runs. A test suite that swaps the clinic's PostgreSQL provider for the in-memory one therefore either cannot run the search queries at all, or forces the code to avoid provider functions that production depends on. The same goes for SQL embedded in the model. `HasDefaultValueSql("now()")` or a computed column never executes, so a test that reads a database-generated `CreatedAt` sees the CLR default instead. ## Which tests it can still serve - Code that only needs a working `DbContext` to exist, where no query result is asserted. - Quick checks of simple LINQ shapes whose semantics are the same in .NET and SQL. - Legacy suites, while they are moved elsewhere. For anything that asserts query results, constraints or transactions, the docs recommend testing against the real engine, with SQLite in-memory as a lighter relational option that brings its own differences. How to set those up belongs to the testing topic. ## Reading a suspicious green test When an in-memory test passes and production fails, ask: - Does the query compare strings, dates or decimals whose semantics depend on the database? - Does the code rely on a constraint, a default value or a computed column? - Does it start a transaction, run raw SQL or call an `EF.Functions` method? - Does it read a value the database generates, such as a default or a computed column? Any yes means the test never exercised the behaviour that failed.
- Can you make the in-memory provider accept BeginTransaction calls, and is that a good idea?Yes: `ConfigureWarnings(w => w.Ignore(InMemoryEventId.TransactionIgnoredWarning))` makes it ignore transactions instead of throwing. The transaction is then skipped entirely, so commit and rollback do nothing, and a test that expects a rollback to undo a failed booking passes or fails for reasons unrelated to production.
- Does the in-memory provider reject a Patient whose required Surname is null?Yes. `UseInMemoryDatabase` enables null checks by default, so saving an entity with a null required property throws. That is one of the few database-like checks it performs; `EnableNullChecks(false)` on its options builder turns it off. Unique indexes and foreign keys are still not enforced.
- The in-memory provider behaves like an in-process SQL Server, so green tests prove the queries.
- Disposing a transaction in an in-memory test rolls the changes back.
- FromSql works on the in-memory provider as long as the SQL is simple.
- The in-memory provider skips required-property checks, so nulls always slip through.
- UseInMemoryDatabase enforces unique indexes the way a relational database does.