Why the InMemory Provider Is Not a Relational Database and Where It Misleads Tests

Learning outcomes

1. Same DbContext API does not mean same database semantics

Microsoft.EntityFrameworkCore.InMemory implements enough EF database-provider behavior to store entities in process, but it is not a relational engine. There is no SQL dialect, foreign-key engine, transaction/isolation implementation, query plan, server collation, or migration DDL. Microsoft explicitly discourages using it as a database fake for EF application testing.

2. Build a tiny ServiceHub relational probe so the difference is undeniable

To isolate one mechanism, create a test-only context with a unique work-order number and a required note foreign key. This is not a second domain model for production; it is a diagnostic fixture that lets both providers receive identical EF operations.

public sealed class ProbeContext(DbContextOptions<ProbeContext> options)    : DbContext(options){    public DbSet<ProbeWorkOrder> WorkOrders => Set<ProbeWorkOrder>();    public DbSet<ProbeNote> Notes => Set<ProbeNote>();    protected override void OnModelCreating(ModelBuilder b)    {        b.Entity<ProbeWorkOrder>().HasIndex(x => x.Number).IsUnique();        b.Entity<ProbeNote>()            .HasOne<ProbeWorkOrder>()            .WithMany()            .HasForeignKey(x => x.WorkOrderId)            .IsRequired();    }}public sealed class ProbeWorkOrder { public int Id { get; set; } public string Number { get; set; } = ""; }public sealed class ProbeNote { public int Id { get; set; } public int WorkOrderId { get; set; } }

3. Deliberate false confidence: duplicate unique values

static async Task InsertDuplicateNumbers(ProbeContext db){    db.WorkOrders.AddRange(        new ProbeWorkOrder { Number = "WO-100" },        new ProbeWorkOrder { Number = "WO-100" });    await db.SaveChangesAsync();}

With InMemory, the save can succeed because there is no relational unique index enforcing the model metadata. With SQLite, the database builds a real unique index and the second insert fails with DbUpdateException wrapping a SQLite constraint error. The model is the same; the database contract is not.

InMemory: SaveChangesAsync completes; 2 rows exist.SQLite:    DbUpdateException -> UNIQUE constraint failed ...Conclusion: the InMemory test cannot prove relational uniqueness.

4. Foreign keys expose the same problem

db.Notes.Add(new ProbeNote { WorkOrderId = 999_999 });await db.SaveChangesAsync();

InMemory can store the orphan because no database FK is executing. SQLite with foreign keys enabled rejects it. For ServiceHub Chapter 22, that difference is security-relevant: a tenant-aware composite FK must be tested on a relational engine, not merely observed in EF model metadata.

5. Transactions and raw SQL are not InMemory contracts

Configuring warnings to ignore a transaction warning does not create transaction semantics. It merely makes a test quieter.

6. Query semantics can diverge before SQL even enters the picture

InMemory executes queries according to its own provider behavior and .NET-side semantics; production providers translate to a database language with engine-specific collation, null handling, type coercion, functions and indexes. A query passing InMemory therefore cannot certify SQL Server/PostgreSQL/MySQL translation.

var rows = await db.WorkOrders    .Where(w => w.Number.StartsWith(prefix))    .OrderBy(w => w.Number)    .ToListAsync();

If case sensitivity/collation is a requirement, run the assertion on the production-like provider with its actual collation. Do not infer it from InMemory.

7. When can InMemory still be acceptable?

A narrow legacy test may use InMemory when the contract is simply EF state persistence inside one process and the test deliberately excludes relational behavior. Even then, ask whether a pure unit test would be clearer and faster. Do not build a new application-wide strategy around InMemory just because construction is easy.

dotnet add package Microsoft.EntityFrameworkCore.InMemory --version 10.0.11

8. Mandatory lab: run the same probe twice

1. Create a test project with EF Core 10.0.11, InMemory 10.0.11, and SQLite 10.0.11.
2. Run the duplicate-number operation using UseInMemoryDatabase; record that it succeeds.
3. Run the same operation against a fresh SQLite in-memory database; record the unique-constraint failure.
4. Repeat with an orphan ProbeNote.
5. Write a short table of behaviors that the InMemory test cannot prove.
6. Delete the InMemory database name/fixture and close the SQLite connection.

9. Production judgment and bridge

Do not equate “no external server” with “faithful database fake.” If relational correctness matters, use at least SQLite and add production-provider tests for provider-specific behavior. If database behavior does not matter, prefer a true unit test over an EF fake. Lesson 3 builds the lightweight relational middle ground: SQLite in memory or in a disposable file, used intentionally and with its limitations documented.

Authoritative references

• EF Core InMemory provider — Microsoft warning, purpose, and feature-status note
• Choosing a testing strategy — official comparison of InMemory, SQLite, mocks, repositories, and real database tests
• Testing without the database — documented InMemory and SQLite examples plus transaction limitations
• SQLite provider limitations — relational limitations to keep in mind even after leaving InMemory
• EF Core transactions — real transaction semantics that InMemory cannot certify
• EF Core testing — official testing overview