skip to content

Database Provider Differences

EF Core's providers compared: SQL Server, Npgsql for PostgreSQL, SQLite and Azure Cosmos DB, each with its own types and limits. Interviewers ask why in-memory tests pass where production fails.

part ofEntity Framework Coreread it →
on this pageshow

explore

questions

6

Why can EF Core 10 tests on the in-memory provider pass while the same code fails against SQL Server or PostgreSQL?

level: middleimportance: must knowfreq 58%

answer

  1. not a relational database
  2. LINQ runs as .NET code
  3. transactions throw by default
  4. no raw SQL at all
  5. no constraints beyond keys

basics

~20 s

EF 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 lines
csharp
var 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 UseInMemoryDatabase

go deeper

for a junior

Recall that the in-memory provider is not relational: no SQL, no transactions, no raw SQL, and string comparisons follow .NET rules.

for a middle

Explain which differences make a green in-memory test meaningless: case-sensitive comparisons, unenforced unique and foreign keys, the transaction warning and provider functions.

for a senior

Show how you triage a test that passes in memory but fails in production, and which behaviours must be tested against the real engine.

for a principal

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.
open as a page

In an EF Core 10 app on PostgreSQL, which packages must share EF Core's major version, and what breaks when they don't?

level: juniorimportance: should knowfreq 40%

basics

~10 s

Every EF Core package moves as one major: the Npgsql provider 10.x, Microsoft.EntityFrameworkCore.Design, test providers such as Microsoft.EntityFrameworkCore.Sqlite and the dotnet-ef tool. Mixing majors brings NU1608 warnings, tool warnings or runtime missing-member failures.

open as a page

With the Npgsql EF Core provider 10, how do you map a C# enum to a native PostgreSQL enum type, and what does MapEnum set up?

level: middleimportance: should knowfreq 32%

basics

~20 s

Call MapEnum<T>() inside UseNpgsql's options callback. The Npgsql provider then maps the enum on the data source it builds and adds the PostgreSQL enum to the model, so migrations emit CREATE TYPE and the column uses that type.

open as a page

Why does an EF Core 10 migration that alters a column on SQLite rebuild the whole table, and what can still go wrong?

level: seniorimportance: should knowfreq 26%

basics

~20 s

SQLite's ALTER TABLE cannot change a column, so EF Core's SQLite provider rebuilds the table: it creates ef_temp_<Table>, copies the rows, drops the original and renames the copy. Tables outside the model, sequences and idempotent scripts stay unsupported.

open as a page

When moving an EF Core 10 clinic app from SQL Server to the Npgsql provider, which mappings and query behaviours change under the same model?

level: seniorimportance: should knowfreq 38%

basics

~20 s

The model stays, but the provider decides the rest: DateTime maps to timestamptz and accepts only UTC values, keys become identity-by-default columns, strings become text and compare case-sensitively, provider EF.Functions change, and SQL Server migrations must be regenerated.

open as a page

In EF Core 10's Azure Cosmos DB provider, why do partition keys and the lack of cross-document joins change how you model and query data?

level: seniorimportance: nice to knowfreq 18%

basics

~20 s

Cosmos DB scales by partition key, so EF Core's Cosmos provider makes the partition key part of each entity's key and targets one partition only when a query supplies it; it cannot join documents, so related data must be embedded in one document.

open as a page