EF Core Testing: SQLite In-Memory vs InMemory Provider — Which One Can You Actually Trust?
20 min read
Intermediate
The test passes. You push. The CI pipeline goes green. Three days later a user reports that deleting a project leaves orphaned tasks in the database — a cascade that never triggered because your test never actually enforced the foreign key constraint. It enforced nothing. The InMemory provider said it was fine, and you believed it.
Choosing the wrong EF Core test provider doesn't give you a red test. It gives you a green test that lies. This tutorial builds a test harness for a small Tasks API, then runs the same suite against both providers — documenting exactly where each one tells the truth and where it doesn't, so you can make an informed choice for every layer of your test pyramid.
What You'll Build
A repeatable EF Core test harness (tutorials/ef-core/EfCoreTestingPlaybook/) that demonstrates the reliability gap between the two in-memory providers:
Tasks API domain — Project and TaskItem entities with a FK relationship, unique constraints, and a concurrency token
Shared test infrastructure — a base fixture that swaps providers without touching test logic
InMemory provider setup — fast, zero-config, with a documented list of what it cannot check
SQLite in-memory setup — open-connection pattern, schema creation, and seed data that survives across test methods
Reliability matrix — constraints, FK enforcement, transaction rollback, LINQ translation, concurrency checks, and relational cascade behaviours tested against both providers
False green catalogue — five tests that pass on InMemory but fail or behave differently against a real database
Decision guide — a one-page checklist for choosing InMemory, SQLite in-memory, or Testcontainers for each test category
Project Setup & Domain Model
A minimal Tasks API — two entities, one relationship, a unique constraint, and a concurrency token. Deliberately simple so the focus stays on the testing mechanics, not the domain.
Terminal — Create Projects
# API project
dotnet new webapi -n TasksApi --no-https --minimal
cd TasksApi
# Test project
dotnet new xunit -n TasksApi.Tests
cd TasksApi.Tests
# EF Core packages for the API
dotnet add ../TasksApi/TasksApi.csproj package Microsoft.EntityFrameworkCore.Sqlite
dotnet add ../TasksApi/TasksApi.csproj package Microsoft.EntityFrameworkCore.InMemory
# Test project references
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.InMemory
dotnet add package Microsoft.Data.Sqlite # for explicit connection control
dotnet add reference ../TasksApi/TasksApi.csproj
Domain Entities
Models/Project.cs & Models/TaskItem.cs
// Models/Project.cs
public class Project
{
public int Id { get; set; }
public string Name { get; set; } = "";
public string OwnerId { get; set; } = "";
// Navigation — one project has many tasks
public ICollection<TaskItem> Tasks { get; set; } = [];
}
// Models/TaskItem.cs
public class TaskItem
{
public int Id { get; set; }
public string Title { get; set; } = "";
public bool IsCompleted { get; set; }
public int ProjectId { get; set; } // FK — must reference an existing Project
// Concurrency token — detects mid-air edits
[Timestamp]
public byte[] RowVersion { get; set; } = [];
public Project Project { get; set; } = null!;
}
DbContext & Model Configuration
Data/TasksDbContext.cs
public class TasksDbContext(DbContextOptions<TasksDbContext> options)
: DbContext(options)
{
public DbSet<Project> Projects { get; set; }
public DbSet<TaskItem> TaskItems { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Project>(entity =>
{
entity.HasKey(p => p.Id);
// Unique constraint — no two projects with the same name per owner
entity.HasIndex(p => new { p.Name, p.OwnerId })
.IsUnique()
.HasDatabaseName("UIX_Projects_Name_Owner");
entity.HasMany(p => p.Tasks)
.WithOne(t => t.Project)
.HasForeignKey(t => t.ProjectId)
.OnDelete(DeleteBehavior.Cascade); // cascade delete tasks with project
});
modelBuilder.Entity<TaskItem>(entity =>
{
entity.HasKey(t => t.Id);
// SQLite doesn't support TIMESTAMP — use rowversion alternative
entity.Property(t => t.RowVersion)
.IsRowVersion()
.IsConcurrencyToken();
});
}
}
Why [Timestamp] Matters for This Tutorial
The [Timestamp] attribute marks RowVersion as a concurrency token. EF Core uses it to detect when two operations try to update the same row simultaneously — the second one throws a DbUpdateConcurrencyException. One of the tests in this tutorial proves that the InMemory provider does not enforce this, while SQLite in-memory does. Understanding this difference is why the concurrency token is part of the domain model from the start.
The Two Providers in Plain Terms
Before any code, a clear mental model of what each provider actually is — because the gap between them isn't about speed, it's about what they simulate.
EF Core InMemory Provider
A pure in-process dictionary. When you call SaveChanges(), EF Core writes entities into a concurrent dictionary keyed by primary key. There is no SQL, no relational engine, no constraint evaluation. Reads are LINQ-to-Objects scans over that dictionary. It is genuinely fast and genuinely zero-config — and it simulates a relational database the way a cardboard box simulates a car.
Tells the truth about: basic CRUD, entity graph traversal, navigation property loading, domain logic that lives entirely in your C# code.
A real relational database engine, embedded in-process, with the data stored in RAM instead of on disk. EF Core uses its full SQL translation pipeline — the same one it uses for SQL Server and PostgreSQL — to generate queries that SQLite executes. Constraints, FKs, unique indexes, and transactions are all enforced by the database engine, not by EF Core or your application code.
Tells the truth about: everything InMemory lies about, plus most LINQ translation failures.
Still can't simulate: SQL Server-specific features (temporal tables, JSON operators, full-text search syntax), provider-specific behaviour differences, production-volume performance characteristics.
SQLite Is a Different Dialect — Know the Gaps
SQLite is a real relational database, but it is not SQL Server or PostgreSQL. It lacks support for some data types (DateTimeOffset stores as text), some constraint checks differ, and some SQL Server or PostgreSQL extensions have no SQLite equivalent. If your production database is SQL Server, SQLite in-memory catches most relational errors but cannot catch SQL Server-specific ones. For those, Testcontainers with a SQL Server image is the only accurate option.
The Right Mental Model: Test Pyramid Layer
Think of each provider as covering a layer of the test pyramid. InMemory sits at the unit test layer — extremely fast, isolated, appropriate for testing domain logic and service behaviour that doesn't depend on relational semantics. SQLite in-memory sits at the integration test layer — tests relational behaviour without Docker, suitable for the majority of repository and data-access tests. Testcontainers sits at the system test layer — exact production parity, slower, used for critical paths where provider behaviour must match precisely.
InMemory Provider: Setup & Gotchas
Setup is genuinely a single line. The gotchas are what this section documents — because none of them produce a test failure. They produce a passing test that is wrong.
Tests/InMemoryFixture.cs — Basic Setup
public class InMemoryFixture : IDisposable
{
public TasksDbContext Context { get; }
public InMemoryFixture()
{
public string DatabaseName { get; } = $"TasksDb_{Guid.NewGuid()}";
// then in the constructor:
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase(databaseName: DatabaseName)
// Suppress the warning about InMemory not supporting transactions
// This is intentional — we know the limitation and accept it for this layer
.ConfigureWarnings(w => w.Ignore(InMemoryEventId.TransactionIgnoredWarning))
.Options;
Context = new TasksDbContext(options);
// No EnsureCreated needed — InMemory has no schema to create
}
public void Dispose() => Context.Dispose();
}
The Four InMemory Gotchas — Demonstrated
// GOTCHA 1: Unique constraints are not enforced
// This will NOT throw — InMemory has no index enforcement
var p1 = new Project { Name = "Alpha", OwnerId = "user-1" };
var p2 = new Project { Name = "Alpha", OwnerId = "user-1" }; // duplicate!
context.Projects.AddRange(p1, p2);
await context.SaveChangesAsync(); // succeeds — should have thrown
// GOTCHA 2: Foreign key constraints are not enforced
// This will NOT throw — InMemory has no FK validation
var orphan = new TaskItem { Title = "Ghost Task", ProjectId = 9999 }; // no such project
context.TaskItems.Add(orphan);
await context.SaveChangesAsync(); // succeeds — should have thrown
// GOTCHA 3: Transaction rollback is silently ignored
// BeginTransaction() returns a no-op transaction — Rollback() does nothing
using var tx = await context.Database.BeginTransactionAsync();
context.Projects.Add(new Project { Name = "Will This Roll Back?", OwnerId = "u1" });
await context.SaveChangesAsync();
await tx.RollbackAsync(); // no-op — the data is still there
// context.Projects.Count() == 1, not 0
// GOTCHA 4: Concurrency tokens are not checked
// Two contexts with the same in-memory database name
var opts = new DbContextOptionsBuilder<TasksDbContext>()
.UseInMemoryDatabase("shared-db")
.Options;
using var ctx1 = new TasksDbContext(opts);
using var ctx2 = new TasksDbContext(opts);
var task = new TaskItem { Title = "Shared Task", ProjectId = 1 };
ctx1.TaskItems.Add(task);
await ctx1.SaveChangesAsync();
// Simulate concurrent edit — both contexts load the same row
var t1 = await ctx1.TaskItems.FindAsync(task.Id);
var t2 = await ctx2.TaskItems.FindAsync(task.Id);
t1!.Title = "Updated by ctx1";
t2!.Title = "Updated by ctx2";
await ctx1.SaveChangesAsync(); // succeeds
await ctx2.SaveChangesAsync(); // SHOULD throw DbUpdateConcurrencyException
// but InMemory lets it through silently
UseInMemoryDatabase Is Not Scoped to a Test by Default
If you pass the same databaseName string to multiple UseInMemoryDatabase calls in the same process, they all share the same in-memory store. This is a common source of test pollution — a test that runs fine in isolation fails when run as part of the full suite because a previous test left data behind. Always use Guid.NewGuid().ToString() as the database name unless you explicitly want a shared store, and always dispose your fixture between test classes.
SQLite In-Memory: Open-Connection Pattern
The single most important thing to understand about SQLite in-memory: the database lives as long as the connection lives. Close the connection and the database is gone. This means you must open a SqliteConnection explicitly, keep it open for the duration of your tests, and pass it to EF Core.
Tests/SqliteFixture.cs — Open-Connection Pattern
public class SqliteFixture : IDisposable
{
private readonly SqliteConnection _connection;
public TasksDbContext Context { get; }
public SqliteFixture()
{
// Open a connection to an in-memory SQLite database
// The database exists as long as this connection is open
_connection = new SqliteConnection("Data Source=:memory:");
_connection.Open(); // MUST be opened before passing to EF Core
// CRITICAL: SQLite does not enforce FK constraints by default.
// Enable per-connection — without this, FK and cascade tests behave like InMemory.
using var cmd = new SqliteCommand("PRAGMA foreign_keys = ON;", _connection);
cmd.ExecuteNonQuery();
var options = new DbContextOptionsBuilder<TasksDbContext>()
.UseSqlite(_connection) // EF Core reuses this open connection
.Options;
Context = new TasksDbContext(options);
// Create the schema — equivalent to running all migrations from scratch
// EnsureCreated() is appropriate here because this is a test database
// Do NOT use EnsureCreated() against a production database
Context.Database.EnsureCreated();
// Seed reference data that tests expect to exist
SeedDatabase();
}
private void SeedDatabase()
{
var project = new Project { Name = "Seed Project", OwnerId = "seed-user" };
Context.Projects.Add(project);
Context.SaveChanges();
// Detach so tests start with a clean change tracker
Context.ChangeTracker.Clear();
}
public void Dispose()
{
Context.Dispose();
_connection.Close(); // closing destroys the in-memory database
_connection.Dispose();
}
}
Creating a Fresh Context Per Test
For tests that need full isolation — no shared change tracker state — create a new DbContext per test while reusing the same underlying connection and therefore the same database.
Tests/SqliteFixture.cs — CreateContext() for Per-Test Isolation
public class SqliteFixture : IDisposable
{
private readonly SqliteConnection _connection;
public SqliteFixture()
{
_connection = new SqliteConnection("Data Source=:memory:");
_connection.Open();
// CRITICAL: Enable FK enforcement — OFF by default in SQLite
using var cmd = new SqliteCommand("PRAGMA foreign_keys = ON;", _connection);
cmd.ExecuteNonQuery();
// Create schema using a bootstrap context
using var bootstrapCtx = CreateContext();
bootstrapCtx.Database.EnsureCreated();
SeedDatabase(bootstrapCtx);
}
// Factory method — each call returns a new DbContext sharing the same connection
// New context = clean change tracker, same database state
public TasksDbContext CreateContext()
{
var options = new DbContextOptionsBuilder<TasksDbContext>()
.UseSqlite(_connection)
.Options;
return new TasksDbContext(options);
}
private static void SeedDatabase(TasksDbContext ctx)
{
ctx.Projects.Add(new Project { Name = "Seed Project", OwnerId = "seed-user" });
ctx.SaveChanges();
}
public void Dispose()
{
_connection.Close();
_connection.Dispose();
}
}
// Usage in a test class
public class TaskRepositoryTests(SqliteFixture fixture) : IClassFixture<SqliteFixture>
{
[Fact]
public async Task CreateTask_WithValidProject_Persists()
{
// Each test gets a fresh context — no change tracker state from previous tests
using var ctx = fixture.CreateContext();
var seedProject = await ctx.Projects.FirstAsync();
var task = new TaskItem { Title = "Write docs", ProjectId = seedProject.Id };
ctx.TaskItems.Add(task);
await ctx.SaveChangesAsync();
// Re-query to confirm persistence — use a new context to bypass cache
using var verifyCtx = fixture.CreateContext();
var saved = await verifyCtx.TaskItems.FindAsync(task.Id);
Assert.NotNull(saved);
Assert.Equal("Write docs", saved.Title);
}
}
EnsureCreated() Skips Migrations
EnsureCreated() creates the schema directly from the current model — it does not run your migration history. This means migration-specific operations (data seeds in migrations, custom SQL in Up() methods, index creation that EF Core's model builder doesn't know about) are absent from the test database. For most test scenarios this is acceptable. If you need migration fidelity — especially when testing that a specific migration doesn't break existing data — use context.Database.Migrate() instead, or use Testcontainers with a real database that can run migrations normally.
Enable Foreign Keys in SQLite
SQLite does not enforce foreign key constraints by default — you must enable them per connection with PRAGMA foreign_keys = ON. Without this, SQLite in-memory behaves like InMemory for FK tests: orphaned rows insert without error. Enable it in your fixture: after opening the connection, run new SqliteCommand("PRAGMA foreign_keys = ON;", _connection).ExecuteNonQuery();. This is the single most important configuration step for SQLite test accuracy.
Reliability Matrix: What Each Provider Enforces
A direct side-by-side of every behaviour that matters for EF Core tests. Each row is a behaviour that has caused real production bugs when tested against the wrong provider.
Reliability Matrix — InMemory vs SQLite vs Production DB
┌─────────────────────────────────┬───────────┬─────────────┬────────────────┐
│ Behaviour │ InMemory │ SQLite :mem │ SQL Server/PG │
├─────────────────────────────────┼───────────┼─────────────┼────────────────┤
│ Basic CRUD │ ✅ Yes │ ✅ Yes │ ✅ Yes │
│ Navigation property loading │ ✅ Yes │ ✅ Yes │ ✅ Yes │
│ Unique constraints │ ❌ No │ ✅ Yes* │ ✅ Yes │
│ Foreign key enforcement │ ❌ No │ ✅ Yes* │ ✅ Yes │
│ Cascade delete │ ❌ No │ ✅ Yes* │ ✅ Yes │
│ Transaction commit/rollback │ ❌ No │ ✅ Yes │ ✅ Yes │
│ Concurrency token (RowVersion) │ ❌ No │ ✅ Yes │ ✅ Yes │
│ LINQ translation failures │ ❌ Silent │ ✅ Throws │ ✅ Throws │
│ Database-generated values │ ⚠️ EF sim │ ✅ Real │ ✅ Real │
│ CHECK constraints │ ❌ No │ ✅ Yes │ ✅ Yes │
│ JSON column queries │ ❌ No │ ❌ No │ ✅ Yes │
│ Full-text search │ ❌ No │ ⚠️ Basic │ ✅ Yes │
│ Temporal tables │ ❌ No │ ❌ No │ ✅ Yes │
│ Stored procedures │ ❌ No │ ⚠️ Basic │ ✅ Yes │
│ Test speed │ ⚡ ~1ms │ ⚡ ~5ms │ 🐢 ~500ms+ │
│ Docker required │ No │ No │ Yes (TC) │
└─────────────────────────────────┴───────────┴─────────────┴────────────────┘
* Requires PRAGMA foreign_keys = ON for FK and cascade; unique constraints work
without it. See Section 4 for the exact fixture setup.
LINQ Translation: The Invisible Failure Mode
When EF Core cannot translate a LINQ expression to SQL, it has two options: throw an exception at query time, or silently fall back to client-side evaluation (loading all rows into memory and filtering in C#). Against InMemory, there is never a SQL translation step — everything evaluates client-side. This means a query that would throw InvalidOperationException: could not be translated against SQL Server passes silently against InMemory. The bug only surfaces in production. SQLite in-memory runs the real translation pipeline, so translation failures surface in tests where they belong.
Example Test Suite: CRUD, Constraints & Transactions
The same four tests run against both fixtures. Each test is written once and parameterised by provider — the differences in results reveal the reliability gap.
Tests/CrudTests.cs — Basic CRUD (Passes on Both)
// Both providers handle basic CRUD correctly
public class CrudTests_SQLite(SqliteFixture fixture) : IClassFixture<SqliteFixture>
{
[Fact]
public async Task AddProject_ThenQueryIt_ReturnsCorrectData()
{
using var ctx = fixture.CreateContext();
var project = new Project { Name = "Test Project", OwnerId = "user-42" };
ctx.Projects.Add(project);
await ctx.SaveChangesAsync();
using var verifyCtx = fixture.CreateContext();
var loaded = await verifyCtx.Projects
.Include(p => p.Tasks)
.FirstAsync(p => p.Id == project.Id);
Assert.Equal("Test Project", loaded.Name);
Assert.Empty(loaded.Tasks);
}
[Fact]
public async Task UpdateTask_ChangesTitle_Persists()
{
using var ctx = fixture.CreateContext();
var project = await ctx.Projects.FirstAsync();
var task = new TaskItem { Title = "Original", ProjectId = project.Id };
ctx.TaskItems.Add(task);
await ctx.SaveChangesAsync();
using var updateCtx = fixture.CreateContext();
var toUpdate = await updateCtx.TaskItems.FindAsync(task.Id);
toUpdate!.Title = "Revised";
await updateCtx.SaveChangesAsync();
using var verifyCtx = fixture.CreateContext();
var verified = await verifyCtx.TaskItems.FindAsync(task.Id);
Assert.Equal("Revised", verified!.Title);
}
}
Tests/ConstraintTests.cs — FK & Unique (SQLite Passes, InMemory Doesn't)
// SQLite: these tests correctly fail when constraints are violated
// InMemory: these tests incorrectly PASS — false greens
public class ConstraintTests_SQLite(SqliteFixture fixture) : IClassFixture<SqliteFixture>
{
[Fact]
public async Task InsertTask_WithNonExistentProject_ThrowsForeignKeyViolation()
{
using var ctx = fixture.CreateContext();
var orphan = new TaskItem { Title = "Orphan", ProjectId = 99999 }; // no such project
ctx.TaskItems.Add(orphan);
// SQLite (with foreign_keys ON): throws DbUpdateException wrapping SqliteException
// InMemory: SaveChanges succeeds — SILENT FALSE GREEN
await Assert.ThrowsAsync<DbUpdateException>(
() => ctx.SaveChangesAsync());
}
[Fact]
public async Task InsertDuplicateProjectName_SameOwner_ThrowsUniqueConstraintViolation()
{
using var ctx = fixture.CreateContext();
ctx.Projects.AddRange(
new Project { Name = "Dup", OwnerId = "owner-1" },
new Project { Name = "Dup", OwnerId = "owner-1" }); // same name + owner
// SQLite: throws DbUpdateException — unique index UIX_Projects_Name_Owner
// InMemory: SaveChanges succeeds — SILENT FALSE GREEN
await Assert.ThrowsAsync<DbUpdateException>(
() => ctx.SaveChangesAsync());
}
[Fact]
public async Task DeleteProject_WithTasks_CascadesToTaskItems()
{
using var ctx = fixture.CreateContext();
var project = new Project { Name = "Cascade Test", OwnerId = "u1" };
ctx.Projects.Add(project);
await ctx.SaveChangesAsync();
ctx.TaskItems.Add(new TaskItem { Title = "Will be deleted", ProjectId = project.Id });
await ctx.SaveChangesAsync();
using var deleteCtx = fixture.CreateContext();
var toDelete = await deleteCtx.Projects.FindAsync(project.Id);
deleteCtx.Projects.Remove(toDelete!);
await deleteCtx.SaveChangesAsync();
// SQLite: cascade delete removes child TaskItems
// InMemory: TaskItems remain — orphaned FK rows, no cascade enforced
using var verifyCtx = fixture.CreateContext();
var orphaned = await verifyCtx.TaskItems
.Where(t => t.ProjectId == project.Id)
.ToListAsync();
Assert.Empty(orphaned); // passes on SQLite, may fail on InMemory
}
}
Tests/TransactionTests.cs — Rollback Behaviour
public class TransactionTests_SQLite(SqliteFixture fixture) : IClassFixture<SqliteFixture>
{
[Fact]
public async Task RolledBackTransaction_LeavesNoPersistentState()
{
using var ctx = fixture.CreateContext();
// Begin an explicit transaction
await using var tx = await ctx.Database.BeginTransactionAsync();
var project = new Project { Name = "Should Disappear", OwnerId = "u1" };
ctx.Projects.Add(project);
await ctx.SaveChangesAsync(); // write inside the transaction
// Roll back — all writes in this transaction are undone
await tx.RollbackAsync();
// SQLite: project is gone — rollback worked
// InMemory: project is still there — rollback was silently ignored
using var verifyCtx = fixture.CreateContext();
var found = await verifyCtx.Projects
.FirstOrDefaultAsync(p => p.Name == "Should Disappear");
Assert.Null(found); // passes on SQLite, fails on InMemory
}
}
False Green Tests & How to Rewrite Them
A false green is a test that passes against InMemory but would fail — or produce different behaviour — against a real database. These are the most dangerous test results because they provide false confidence.
False Green #1: Assuming FK Orphans Are Rejected
Before (False Green on InMemory) → After (Reliable on SQLite)
// ❌ BEFORE — passes on InMemory, lies about FK enforcement
[Fact]
public async Task AddTask_ToDeletedProject_IsRejected()
{
context.TaskItems.Add(new TaskItem { Title = "Orphan", ProjectId = 9999 });
// No exception — InMemory accepts the orphan silently
await context.SaveChangesAsync();
// Test passes but production will have referential integrity violations
}
// ✅ AFTER — switch to SQLite fixture, assert the exception
[Fact]
public async Task AddTask_ToNonExistentProject_ThrowsDbUpdateException()
{
using var ctx = _sqliteFixture.CreateContext();
ctx.TaskItems.Add(new TaskItem { Title = "Orphan", ProjectId = 9999 });
await Assert.ThrowsAsync<DbUpdateException>(
() => ctx.SaveChangesAsync());
}
False Green #2: Concurrency Conflict Not Detected
Concurrency Token — InMemory Ignores It, SQLite Enforces It
// ✅ Correct test using SQLite fixture — catches the concurrency conflict
[Fact]
public async Task ConcurrentUpdate_ThrowsDbUpdateConcurrencyException()
{
using var ctx1 = _sqliteFixture.CreateContext();
using var ctx2 = _sqliteFixture.CreateContext();
// Seed a task — dispose immediately after seeding
using var seedCtx = _sqliteFixture.CreateContext();
var project = await seedCtx.Projects.FirstAsync();
seedCtx.TaskItems.Add(new TaskItem { Title = "Shared", ProjectId = project.Id });
await seedCtx.SaveChangesAsync();
// Both contexts read the same row
var task1 = await ctx1.TaskItems.FirstAsync();
var task2 = await ctx2.TaskItems.FirstAsync();
// First update wins
task1.Title = "Updated by ctx1";
await ctx1.SaveChangesAsync();
// Second update should detect the stale RowVersion and throw
task2.Title = "Updated by ctx2";
await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
() => ctx2.SaveChangesAsync());
// On InMemory: this assertion FAILS — SaveChanges silently overwrites
// On SQLite: this assertion PASSES — RowVersion mismatch is detected
}
RowVersion in SQLite Requires a Trigger Workaround
SQLite does not have a native ROWVERSION or TIMESTAMP type equivalent to SQL Server. EF Core's SQLite provider simulates concurrency tokens using a BLOB column and a trigger that updates it on every UPDATE. EF Core creates this trigger automatically when you call EnsureCreated() on a model with [Timestamp]. You do not need to write the trigger yourself — but you do need to call EnsureCreated() (or the equivalent migration) for the trigger to exist.
Identify False Greens in Your Existing Suite
A quick way to audit your existing InMemory tests: run the same test class against a SQLite in-memory fixture with PRAGMA foreign_keys = ON. Any test that changes from green to red was a false green. Expect failures on anything that inserts data with invalid FK references, relies on unique constraint violations being thrown, or asserts on cascade delete side effects. Each failure is a production bug that your test suite was hiding.
Shared Test Fixture: Swapping Providers Without Rewriting Tests
The cleanest architecture runs the same test logic against both providers, making the difference in results explicit. A shared abstract base class achieves this without duplicating test code.
Tests/TaskRepositoryTestsBase.cs — Abstract Provider-Agnostic Base
// Abstract base — test logic lives here once, runs against any provider
public abstract class TaskRepositoryTestsBase
{
// Subclasses supply the context — InMemory or SQLite
protected abstract TasksDbContext CreateContext();
[Fact]
public async Task CreateTask_Persists()
{
using var ctx = CreateContext();
var project = new Project { Name = "Base Test", OwnerId = "u1" };
ctx.Projects.Add(project);
await ctx.SaveChangesAsync();
using var verify = CreateContext();
Assert.True(await verify.Projects.AnyAsync(p => p.Name == "Base Test"));
}
// Override in subclass to mark as Skip when InMemory can't support the behaviour
[Fact]
public virtual async Task UniqueConstraint_DuplicateName_Throws()
{
using var ctx = CreateContext();
ctx.Projects.AddRange(
new Project { Name = "Clash", OwnerId = "u1" },
new Project { Name = "Clash", OwnerId = "u1" });
await Assert.ThrowsAsync<DbUpdateException>(
() => ctx.SaveChangesAsync());
}
}
// SQLite subclass — all tests run, including constraint tests
public class TaskRepositoryTests_SQLite(SqliteFixture fixture)
: TaskRepositoryTestsBase, IClassFixture<SqliteFixture>
{
protected override TasksDbContext CreateContext() => fixture.CreateContext();
// UniqueConstraint test inherited and expected to pass
}
// InMemory subclass — constraint tests skipped with a meaningful reason
public class TaskRepositoryTests_InMemory(InMemoryFixture fixture)
: TaskRepositoryTestsBase, IClassFixture<InMemoryFixture>
{
protected override TasksDbContext CreateContext()
{
// Create a NEW context pointing at the named in-memory database.
// Do NOT return fixture.Context directly — the base class disposes each context
// returned from CreateContext(), which would destroy the fixture's shared context.
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase(fixture.DatabaseName) // shared store, fresh change tracker
.ConfigureWarnings(w => w.Ignore(InMemoryEventId.TransactionIgnoredWarning))
.Options;
return new TasksDbContext(options);
}
[Fact(Skip = "InMemory provider does not enforce unique constraints — use SQLite fixture for this test")]
public override async Task UniqueConstraint_DuplicateName_Throws()
{
// Skipped — documented reason in Skip message
await Task.CompletedTask;
}
}
Skip vs Delete: Document the Limitation, Don't Hide It
When a test cannot run meaningfully against InMemory, mark it [Fact(Skip = "reason")] rather than deleting it. The skip message becomes living documentation of the provider's limitations — anyone reading the test output sees exactly which behaviours InMemory cannot verify. Deleting the test removes the knowledge. Skipping it preserves it.
When to Use InMemory, SQLite In-Memory, or Testcontainers
The decision is not "always SQLite" or "never InMemory". Each tool fits a specific layer of the test pyramid. Here is a practical checklist.
Decision Checklist — Choose Your Provider
USE InMemory WHEN:
✅ Testing pure domain logic — services, validators, business rules
✅ The test asserts on return values, not database side effects
✅ No unique constraints, FK references, or cascade behaviour involved
✅ No transaction rollback assertions
✅ Speed is critical and you're running thousands of tests
✅ You want zero infrastructure — no files, no connections, no teardown
USE SQLite In-Memory WHEN:
✅ Testing repositories or data-access code that uses EF Core
✅ You need FK enforcement, unique index violations, or cascade deletes
✅ You need transaction rollback to actually roll back
✅ You want LINQ translation failures to surface in tests, not production
✅ Concurrency tokens must be tested
✅ Your schema is SQLite-compatible (no SQL Server-specific types)
✅ You want no Docker dependency but more accuracy than InMemory
USE Testcontainers (real database) WHEN:
✅ Production target is SQL Server, PostgreSQL, or MySQL
✅ Tests exercise SQL Server-specific features: temporal tables, JSON operators,
full-text search, sp_executesql, OUTPUT clauses
✅ You need exact migration fidelity — running the real migration history
✅ You're testing database performance (index usage, query plans)
✅ Provider differences in EF Core behaviour must be eliminated entirely
✅ CI environment has Docker available and test runtime is not critical
NEVER USE InMemory FOR:
❌ Tests that assert a DbUpdateException is thrown on constraint violation
❌ Tests that assert orphaned rows are rejected by FK constraints
❌ Tests that assert cascade deletes removed child rows
❌ Tests that assert a rolled-back transaction left no trace
❌ Tests for DbUpdateConcurrencyException on RowVersion conflict
The Testcontainers Startup Cost Is Real — Plan Your CI Pipeline
A Testcontainers SQL Server image takes 5–15 seconds to start on a warm Docker daemon and longer on cold CI runners. Running 500 isolated tests each with their own container is impractical. The solution is to share a single container across all tests in a collection using xUnit's ICollectionFixture<T>, create isolated schemas or databases per test class, and tear down only what was created. This keeps total Testcontainers test time in the 10–30 second range rather than minutes.
SQLite Has Type Affinity, Not Strict Types
SQLite uses type affinity rather than strict data types. You can insert a string into an integer column and SQLite will not throw — it stores what you give it. This can mask type-mismatch bugs that SQL Server would reject. If type strictness matters for a specific test, use Testcontainers with a SQL Server image. For the vast majority of EF Core tests, this edge case is not a practical concern.
Questions Worth Answering
Why does the EF Core InMemory provider not enforce foreign key constraints?
The InMemory provider is a pure in-process key-value store — it has no relational engine underneath. Foreign key constraints are a relational concept. You can insert a TaskItem with a ProjectId that references a non-existent Project and SaveChanges will succeed. This is by design: the provider prioritises speed for simple unit tests over relational accuracy. If your domain depends on FK constraints being enforced at the database level — cascade deletes, referential integrity checks — you must use SQLite in-memory with PRAGMA foreign_keys = ON or a real database for those tests.
What does "keeping the SQLite connection open" mean and why does it matter?
SQLite's in-memory mode creates a database that exists only for the lifetime of the connection. The moment the last connection closes, the data is gone. In a test fixture, if EF Core opens and closes connections normally between operations, each new connection sees a fresh empty database — your schema and seed data disappear between calls. The fix is to open a SqliteConnection explicitly at test class initialisation, pass it to UseSqlite(_connection), and keep it open for the entire fixture lifetime. Every DbContext created with UseSqlite(_connection) reuses that single open connection and therefore shares the same in-memory database.
Can I use the InMemory provider for LINQ query testing?
Only for simple queries. The InMemory provider evaluates LINQ using LINQ to Objects — not SQL translation. Queries that EF Core would translate to specific SQL constructs (database functions, raw SQL fragments, JSON column operators, full-text search) either evaluate client-side silently or throw at runtime. A LINQ expression that fails translation against SQL Server passes silently against InMemory. SQLite in-memory runs the real EF Core SQL translation pipeline, so translation failures surface in tests rather than in production at 3 a.m.
When should I use Testcontainers instead of either in-memory option?
Use Testcontainers when your application targets SQL Server, PostgreSQL, or MySQL in production and your tests need provider-specific behaviour: JSON column operators, full-text search, row-level locking, temporal tables, generated columns, or stored procedures. Testcontainers spins up a real Docker container with the exact same database engine you use in production, eliminating the provider gap entirely. The practical rule: InMemory for domain logic with no DB-specific behaviour, SQLite in-memory for relational logic on a SQLite-compatible schema, Testcontainers for anything that must match production exactly.