Green Tests, Broken Production: The InMemory Provider Problem
The EF Core InMemory provider is the most commonly misused testing tool in the .NET ecosystem. It is fast, requires no setup, and produces green test suites with impressive coverage numbers. It is also a dictionary backed by a concurrent hash map that has no concept of SQL schemas, relational constraints, transaction semantics, or provider-specific LINQ translation. The gap between what InMemory simulates and what your production SQL Server or PostgreSQL database actually does is wide enough to drive an entire class of production bugs through — bugs that your test suite confidently marked as impossible.
The failure pattern is always the same: a required field validation passes in tests, a unique constraint violation is silently accepted, a transaction rollback is a no-op, a LINQ expression that cannot be translated to SQL evaluates fine in-process. Everything green. Then production. Then an incident. This article gives you the complete list of InMemory divergences, a side-by-side comparison of each failure scenario, and the exact migration to SQLite in-memory that turns your false-green suite into one that actually catches the bugs it claims to.
What the InMemory Provider Silently Skips
Understanding the InMemory provider's behaviour requires understanding what it is: a thread-safe dictionary keyed on entity primary keys, with a LINQ-to-objects evaluation engine layered on top. It is not a database simulation. It is an in-process object store that exposes the EF Core API surface. Everything that a relational database enforces at the schema layer — constraints, indexes, referential integrity, transactions — is invisible to it. The EF Core team's own documentation states explicitly that the InMemory provider should not be used for testing relational database behaviour. Most teams find this out the hard way.
// ════════════════════════════════════════════════════════════════════════════
// SCENARIO 1: Required field validation — InMemory ignores it
// ════════════════════════════════════════════════════════════════════════════
public sealed class Product
{
public int Id { get; set; }
[Required] // enforced by SQL NOT NULL constraint
[MaxLength(200)] // enforced by SQL VARCHAR(200)
public string Name { get; set; } = "";
[Required]
public string Sku { get; set; } = ""; // unique in production
}
// ── FALSE GREEN: this test passes against InMemory ────────────────────────
[Fact]
public async Task Save_Product_WithNullName_FalseGreen()
{
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase("test-db")
.Options;
await using var ctx = new AppDbContext(options);
ctx.Products.Add(new Product { Name = null!, Sku = "SKU-001" });
// InMemory: succeeds. Saves null Name without complaint.
// SQL Server: throws DbUpdateException — NOT NULL constraint violation.
await ctx.SaveChangesAsync(); // ← passes in InMemory, explodes in production
}
// ════════════════════════════════════════════════════════════════════════════
// SCENARIO 2: Unique index — InMemory allows duplicates
// ════════════════════════════════════════════════════════════════════════════
// ModelBuilder: modelBuilder.Entity().HasIndex(p => p.Sku).IsUnique();
[Fact]
public async Task Save_DuplicateSku_FalseGreen()
{
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase("test-unique")
.Options;
await using var ctx = new AppDbContext(options);
ctx.Products.Add(new Product { Name = "Widget A", Sku = "SKU-001" });
ctx.Products.Add(new Product { Name = "Widget B", Sku = "SKU-001" }); // duplicate
// InMemory: two products with identical Sku saved without error.
// SQL Server: throws DbUpdateException — unique constraint violation.
await ctx.SaveChangesAsync(); // ← false green
var count = await ctx.Products.CountAsync(p => p.Sku == "SKU-001");
Assert.Equal(2, count); // production would have 1, or would have thrown
}
// ════════════════════════════════════════════════════════════════════════════
// SCENARIO 3: Foreign key constraint — InMemory accepts orphaned records
// ════════════════════════════════════════════════════════════════════════════
[Fact]
public async Task Save_OrderLine_WithInvalidOrderId_FalseGreen()
{
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase("test-fk")
.Options;
await using var ctx = new AppDbContext(options);
// OrderLine references OrderId = 999 — no Order with Id 999 exists
ctx.OrderLines.Add(new OrderLine
{
OrderId = 999, // dangling foreign key
ProductId = Guid.NewGuid(),
Quantity = 1,
UnitPrice = 10m
});
// InMemory: saves the orphaned OrderLine without checking FK integrity.
// SQL Server: throws DbUpdateException — foreign key constraint violation.
await ctx.SaveChangesAsync(); // ← false green
}
// ════════════════════════════════════════════════════════════════════════════
// SCENARIO 4: Transaction rollback — InMemory makes it a no-op
// ════════════════════════════════════════════════════════════════════════════
[Fact]
public async Task Transaction_Rollback_FalseGreen()
{
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase("test-txn")
.Options;
await using var ctx = new AppDbContext(options);
// BeginTransaction against InMemory returns a stub — no real transaction
await using var txn = await ctx.Database.BeginTransactionAsync();
ctx.Products.Add(new Product { Name = "Rollback Me", Sku = "SKU-TXN" });
await ctx.SaveChangesAsync();
// Rollback is a no-op in InMemory — the data is already committed
await txn.RollbackAsync();
// InMemory: product still exists after "rollback" — rollback did nothing
// SQL Server: product does not exist — rollback worked correctly
var exists = await ctx.Products.AnyAsync(p => p.Sku == "SKU-TXN");
Assert.False(exists); // ← FAILS in InMemory (data was not rolled back)
// If your test asserts True here, you have a false green — rollback appeared to work
}
// ════════════════════════════════════════════════════════════════════════════
// SCENARIO 5: LINQ translation gap — InMemory evaluates, SQL throws
// ════════════════════════════════════════════════════════════════════════════
[Fact]
public async Task LINQ_UntranslatableExpression_FalseGreen()
{
var options = new DbContextOptionsBuilder()
.UseInMemoryDatabase("test-linq")
.Options;
await using var ctx = new AppDbContext(options);
ctx.Products.Add(new Product { Name = "Widget", Sku = "SKU-L" });
await ctx.SaveChangesAsync();
// CustomBusinessRule() is a C# method with no SQL translation.
// InMemory evaluates it as in-process LINQ — works fine.
// SQL Server: throws InvalidOperationException at query execution time.
var results = await ctx.Products
.Where(p => CustomBusinessRule(p.Name)) // ← no SQL translation
.ToListAsync();
Assert.Single(results); // ← false green — test passes, production throws
}
private static bool CustomBusinessRule(string name) =>
name.StartsWith("W", StringComparison.OrdinalIgnoreCase);
// ════════════════════════════════════════════════════════════════════════════
// SCENARIO 6: Concurrency token — InMemory ignores RowVersion
// ════════════════════════════════════════════════════════════════════════════
public sealed class Invoice
{
public int Id { get; set; }
public string Number { get; set; } = "";
[Timestamp] // maps to SQL Server rowversion — optimistic concurrency
public byte[] RowVersion { get; set; } = [];
}
// InMemory does not simulate the RowVersion column or check it on update.
// Concurrent update scenarios that should throw DbUpdateConcurrencyException
// succeed silently — the "lost update" problem is invisible in tests.
Scenario 4 — the transaction rollback false green — is the most dangerous on this list because it inverts the test's assertion. If your code path tests that a failed operation correctly rolls back, the test passes in InMemory because the rollback call succeeds without doing anything. The test asserts the right outcome and gets it — for the completely wrong reason. You leave with confidence that your error recovery path works. It does not. The fix requires a provider that implements real transaction semantics.
The Fix: Migrating to SQLite In-Memory in Four Steps
SQLite in-memory is the correct replacement for InMemory in the vast majority of EF Core testing scenarios. It runs entirely in process, requires no external database server, executes fast enough for unit test suites, and enforces relational behaviour — schema constraints, foreign keys (once enabled), transaction semantics, and SQL-translated LINQ — against the same schema your migrations define. The migration from InMemory to SQLite in-memory is mechanical: change the provider, wire up a shared connection, enable foreign keys, and call EnsureCreated(). The test logic itself does not change.
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
// ── STEP 1: Add the SQLite package ───────────────────────────────────────
// dotnet add package Microsoft.EntityFrameworkCore.Sqlite
// dotnet add package Microsoft.Data.Sqlite
// The InMemory package (Microsoft.EntityFrameworkCore.InMemory) can be removed
// from test projects that complete the migration.
// ── STEP 2: Shared SqliteTestFixture base class ───────────────────────────
// SQLite in-memory databases are tied to their SqliteConnection instance.
// If the connection closes, the database is destroyed.
// Keep ONE open connection and pass it to every DbContextOptionsBuilder.
// xUnit IClassFixture or a base class handles the lifetime correctly.
public abstract class SqliteTestFixture : IDisposable
{
private readonly SqliteConnection _connection;
protected readonly DbContextOptions Options;
protected SqliteTestFixture()
{
// "DataSource=:memory:" creates an in-process SQLite database.
// Must keep this connection open for the database to persist.
_connection = new SqliteConnection("DataSource=:memory:");
_connection.Open();
// ── STEP 3: Enable foreign key enforcement ────────────────────────
// SQLite does NOT enforce foreign keys by default — PRAGMA is required.
// Without this, FK violations pass silently — the same false-green
// behaviour you were escaping from InMemory.
using var cmd = _connection.CreateCommand();
cmd.CommandText = "PRAGMA foreign_keys = ON;";
cmd.ExecuteNonQuery();
// Build options using the shared open connection (not a connection string)
Options = new DbContextOptionsBuilder()
.UseSqlite(_connection)
.Options;
// ── STEP 4: Apply schema via EnsureCreated ────────────────────────
// EnsureCreated() applies your EF Core model as a SQLite schema —
// tables, columns, indexes, and constraints — without running migrations.
// Use EnsureCreated() in tests, not MigrateAsync() — migration history
// adds overhead and complexity that integration tests don't need.
using var ctx = new AppDbContext(Options);
ctx.Database.EnsureCreated();
}
// Each test method gets a fresh DbContext pointing to the same schema.
// The schema persists; the change tracker is scoped to the context instance.
protected AppDbContext CreateContext() => new(Options);
public void Dispose()
{
_connection.Dispose(); // closes connection → in-memory database destroyed
}
}
// ── Test class using the fixture ──────────────────────────────────────────
public sealed class ProductRepositoryTests : SqliteTestFixture
{
// ── Previously false-green: now correctly fails ───────────────────────
[Fact]
public async Task Save_Product_WithNullName_ThrowsCorrectly()
{
await using var ctx = CreateContext();
ctx.Products.Add(new Product { Name = null!, Sku = "SKU-001" });
// SQLite enforces NOT NULL — this now throws as production would
var act = async () => await ctx.SaveChangesAsync();
await act.Should().ThrowAsync();
}
// ── Previously false-green: unique constraint now enforced ────────────
[Fact]
public async Task Save_DuplicateSku_ThrowsDbUpdateException()
{
await using var ctx = CreateContext();
ctx.Products.Add(new Product { Name = "Widget A", Sku = "SKU-DUP" });
await ctx.SaveChangesAsync();
await using var ctx2 = CreateContext();
ctx2.Products.Add(new Product { Name = "Widget B", Sku = "SKU-DUP" });
// SQLite enforces the unique index — duplicate Sku now throws correctly
var act = async () => await ctx2.SaveChangesAsync();
await act.Should().ThrowAsync()
.WithMessage("*UNIQUE constraint failed*");
}
// ── Previously false-green: FK constraint now enforced ───────────────
[Fact]
public async Task Save_OrderLine_WithInvalidOrderId_ThrowsCorrectly()
{
await using var ctx = CreateContext();
ctx.OrderLines.Add(new OrderLine
{
OrderId = 99999, // no Order with this ID exists
ProductId = Guid.NewGuid(),
Quantity = 1,
UnitPrice = 10m
});
// SQLite with PRAGMA foreign_keys = ON enforces FK integrity
var act = async () => await ctx.SaveChangesAsync();
await act.Should().ThrowAsync()
.WithMessage("*FOREIGN KEY constraint failed*");
}
// ── Previously false-green: transaction rollback now works ────────────
[Fact]
public async Task Transaction_Rollback_RemovesData_Correctly()
{
await using var ctx = CreateContext();
await using var txn = await ctx.Database.BeginTransactionAsync();
ctx.Products.Add(new Product { Name = "Rollback Test", Sku = "SKU-RB" });
await ctx.SaveChangesAsync();
// SQLite implements real transaction semantics — rollback undoes the save
await txn.RollbackAsync();
await using var verifyCtx = CreateContext();
var exists = await verifyCtx.Products.AnyAsync(p => p.Sku == "SKU-RB");
// This now correctly asserts False — the rollback actually worked
exists.Should().BeFalse(
because: "rolling back the transaction should remove the uncommitted product");
}
}
The PRAGMA foreign_keys = ON command is the single most important line in the migration — and the one most often omitted. SQLite's default of not enforcing foreign keys exists for historical compatibility reasons and is the most surprising SQLite behaviour for developers coming from SQL Server or PostgreSQL. If you migrate from InMemory to SQLite without this pragma, you trade one set of false greens (InMemory's constraint blindness) for a subtler set (SQLite's FK blindness) while believing you've fixed the problem. Enable it in the fixture constructor, verify it with a test that deliberately violates a FK, and trust nothing until that test passes.
LINQ Translation Gaps: Writing Queries That Survive the Provider Switch
Migrating to SQLite in-memory catches constraint and transaction failures. It does not catch all LINQ translation failures — SQLite's SQL dialect differs from SQL Server and PostgreSQL, so some expressions that translate successfully against SQLite still fail against your production provider. The safest approach is to write LINQ queries that stick to the EF Core core translation surface and verify any complex query against the production provider in at least one integration test environment.
// ════════════════════════════════════════════════════════════════════════════
// TRANSLATION FAILURE 1: Custom C# methods in Where clauses
// ════════════════════════════════════════════════════════════════════════════
// WRONG: CustomFilter() has no SQL translation — throws against real providers
var broken = await ctx.Products
.Where(p => CustomFilter(p.Name)) // EF Core cannot translate this to SQL
.ToListAsync();
// RIGHT: use EF Core-translatable expressions only
var correct = await ctx.Products
.Where(p => p.Name.StartsWith("Widget")) // translates to LIKE 'Widget%'
.ToListAsync();
// RIGHT: if you need complex logic, fetch first then filter in memory
// Only acceptable when the result set after SQL filtering is small
var filtered = await ctx.Products
.Where(p => p.IsActive) // SQL filter — narrows the result set
.ToListAsync(); // materialise
var result = filtered.Where(p => CustomFilter(p.Name)).ToList(); // in-memory filter
// ════════════════════════════════════════════════════════════════════════════
// TRANSLATION FAILURE 2: String methods that differ between providers
// ════════════════════════════════════════════════════════════════════════════
// WRONG: IndexOf with StringComparison — not translatable in all providers
var brokenIndexOf = await ctx.Products
.Where(p => p.Name.IndexOf("widget", StringComparison.OrdinalIgnoreCase) >= 0)
.ToListAsync();
// RIGHT: use EF.Functions for provider-aware string operations
var correctLike = await ctx.Products
.Where(p => EF.Functions.Like(p.Name, "%widget%")) // translates correctly
.ToListAsync();
// ════════════════════════════════════════════════════════════════════════════
// TRANSLATION FAILURE 3: DateTimeOffset operations
// ════════════════════════════════════════════════════════════════════════════
// WRONG: .Date property on DateTimeOffset — not supported in all providers
var brokenDate = await ctx.Orders
.Where(o => o.CreatedAt.Date == DateTime.Today) // fails on some providers
.ToListAsync();
// RIGHT: use explicit date range comparison — translates universally
var today = DateTime.UtcNow.Date;
var tomorrow = today.AddDays(1);
var correctDate = await ctx.Orders
.Where(o => o.CreatedAt >= today && o.CreatedAt < tomorrow)
.ToListAsync();
// ════════════════════════════════════════════════════════════════════════════
// TRANSLATION FAILURE 4: .Contains() on an in-memory collection vs SQL IN
// ════════════════════════════════════════════════════════════════════════════
// This pattern works correctly — EF Core translates it to SQL IN (...)
// InMemory evaluates it in-process. Both give the same result here.
// Document it anyway so future maintainers know it is intentionally correct.
var validSkus = new[] { "SKU-001", "SKU-002", "SKU-003" };
var bySkus = await ctx.Products
.Where(p => validSkus.Contains(p.Sku)) // → SQL: WHERE Sku IN ('SKU-001', ...)
.ToListAsync();
// ════════════════════════════════════════════════════════════════════════════
// SAFE QUERY CHECKLIST
// ════════════════════════════════════════════════════════════════════════════
//
// ✓ Where, Select, OrderBy, GroupBy with simple property access
// ✓ Navigation property includes (.Include, .ThenInclude)
// ✓ String: StartsWith, EndsWith, Contains (without StringComparison arg)
// ✓ EF.Functions.Like for pattern matching
// ✓ Aggregate: Count, Sum, Min, Max, Average on numeric columns
// ✓ .Contains(collection) for SQL IN clause
// ✓ FirstOrDefault, SingleOrDefault, AnyAsync, AllAsync
//
// ✗ Custom C# methods in predicates or projections
// ✗ String methods with StringComparison arguments
// ✗ DateTime.Date property (use range comparison instead)
// ✗ Complex multi-level GroupBy with navigation properties
// ✗ Bitwise operations on non-integer types
// ✗ Provider-specific functions without EF.Functions wrapper
//
// When in doubt: log the generated SQL with EnableSensitiveDataLogging
// and verify it is valid SQL for your production provider.
var optionsWithLogging = new DbContextOptionsBuilder()
.UseSqlite(_connection)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging()
.Options;
// Run the query, read the SQL in the test output, verify it makes sense.
The safe query checklist at the bottom of the code block is the practical working guide, not the exception list. Most EF Core LINQ usage falls into the safe category — the failures are specific patterns that consistently catch teams out. The most reliable verification technique is the last snippet: enable SQL logging in test output and read the generated SQL. If the SQL makes no sense, or if EF Core is evaluating a large result set client-side when you expected a narrow server-side filter, the query is wrong regardless of whether the test passes.
Concurrency Tokens, Provider Gaps & When to Use a Real Database
SQLite in-memory closes most of the gap between test behaviour and production behaviour. It does not close all of it. Two scenarios require a real database instance in your test suite: concurrency token verification (optimistic locking with [Timestamp] or IsConcurrencyToken()) and provider-specific features that SQLite does not support — JSON columns, full-text search, geographic types, computed columns with server-side expressions. For these, a Docker-hosted SQL Server or PostgreSQL instance in your CI pipeline is the correct test infrastructure, not a provider approximation.
// ════════════════════════════════════════════════════════════════════════════
// CONCURRENCY TOKEN TESTING — requires real provider or SQLite workaround
// ════════════════════════════════════════════════════════════════════════════
// [Timestamp] maps to SQL Server's rowversion — an 8-byte binary value
// that the database increments automatically on every update.
// SQLite has no equivalent. EF Core's SQLite provider simulates concurrency
// tokens using a BLOB column that it manages manually — close but not identical
// to SQL Server rowversion behaviour under concurrent load.
// The correct pattern for concurrency token tests in SQLite:
// Use IsConcurrencyToken() with a Guid or a manually managed version field
// rather than [Timestamp] — these are provider-agnostic and SQLite supports them.
public sealed class Document
{
public int Id { get; set; }
public string Content { get; set; } = "";
// Provider-agnostic concurrency token — works in SQLite and SQL Server
public Guid RowVersion { get; set; } = Guid.NewGuid();
}
// ModelBuilder: modelBuilder.Entity()
// .Property(d => d.RowVersion).IsConcurrencyToken();
[Fact]
public async Task ConcurrentUpdate_ThrowsDbUpdateConcurrencyException()
{
// Arrange: seed a document
await using var seedCtx = CreateContext();
var doc = new Document { Content = "Original content" };
seedCtx.Documents.Add(doc);
await seedCtx.SaveChangesAsync();
var docId = doc.Id;
// Act: two contexts load the same document simultaneously
await using var ctx1 = CreateContext();
await using var ctx2 = CreateContext();
var doc1 = await ctx1.Documents.FindAsync(docId);
var doc2 = await ctx2.Documents.FindAsync(docId);
// First context updates and saves — increments the RowVersion
doc1!.Content = "Updated by user 1";
doc1.RowVersion = Guid.NewGuid(); // simulate version increment
await ctx1.SaveChangesAsync();
// Second context tries to save with the OLD RowVersion — stale data
doc2!.Content = "Updated by user 2";
// RowVersion still has the original value — mismatch detected by EF Core
var act = async () => await ctx2.SaveChangesAsync();
// SQLite with IsConcurrencyToken() correctly throws here
await act.Should().ThrowAsync();
}
// ════════════════════════════════════════════════════════════════════════════
// PROVIDER SELECTION MATRIX — which tool for which test
// ════════════════════════════════════════════════════════════════════════════
//
// ┌──────────────────────────────────────┬──────────┬──────────┬────────────┐
// │ Test Scenario │ InMemory │ SQLite │ Real DB │
// │ │ │ In-Mem │ (Docker) │
// ├──────────────────────────────────────┼──────────┼──────────┼────────────┤
// │ Domain logic (no DB constraints) │ ✓ │ ✓ │ ✓ │
// │ Required / MaxLength constraints │ ✗ │ ✓ │ ✓ │
// │ Unique index enforcement │ ✗ │ ✓ │ ✓ │
// │ Foreign key constraint │ ✗ │ ✓* │ ✓ │
// │ Transaction rollback / commit │ ✗ │ ✓ │ ✓ │
// │ LINQ translation (core operators) │ ✓ │ ✓ │ ✓ │
// │ LINQ translation (provider-specific) │ ✓** │ ✓** │ ✓ │
// │ Concurrency tokens ([Timestamp]) │ ✗ │ ~ │ ✓ │
// │ JSON column queries │ ✗ │ ✗ │ ✓ │
// │ Full-text search │ ✗ │ ~ │ ✓ │
// │ Geographic / spatial types │ ✗ │ ~ │ ✓ │
// │ Computed columns (server-side) │ ✗ │ ✗ │ ✓ │
// │ Bulk operations (ExecuteUpdate) │ ✗ │ ✓ │ ✓ │
// │ Raw SQL queries │ ✗ │ ✓ │ ✓ │
// │ Global query filters │ ✓ │ ✓ │ ✓ │
// │ Shadow properties │ ✓ │ ✓ │ ✓ │
// ├──────────────────────────────────────┼──────────┼──────────┼────────────┤
// │ Setup complexity │ None │ Minimal │ Moderate │
// │ Execution speed │ Fastest │ Fast │ Slower │
// │ CI infrastructure required │ No │ No │ Yes │
// └──────────────────────────────────────┴──────────┴──────────┴────────────┘
//
// * SQLite FK enforcement requires PRAGMA foreign_keys = ON (see fixture above)
// ** False green risk — both providers may evaluate untranslatable expressions
// in-process without error, while production SQL Server / PostgreSQL throws
// ── Recommendation ────────────────────────────────────────────────────────
// Default: SQLite in-memory for all EF Core tests
// Add: Docker-based real DB for tests touching provider-specific features
// Remove: InMemory provider from test projects testing data access behaviour
//
// The majority of false-green failures are eliminated by the SQLite migration.
// The remaining gaps (JSON, spatial, rowversion) require the real provider
// and are worth the Docker CI infrastructure cost for those specific tests.
The provider selection matrix is the decision reference your team needs permanently, not just during the migration. Post it in your test project's README. Reference it in pull request reviews when a new test is added using the InMemory provider. The matrix makes the decision mechanical: if the test scenario has a checkmark only in the "InMemory" column, the InMemory provider is acceptable for that test. If the scenario has a cross in InMemory but a checkmark in SQLite in-memory, migrate the test. If the scenario has a cross in both, it belongs in the Docker-based integration test suite. Every test has exactly one correct provider — the matrix removes the ambiguity.
The Complete Migration Checklist
Migrating an existing test suite from InMemory to SQLite in-memory is typically a two-hour task for a medium-sized project. The mechanical steps are: replace the NuGet package, replace UseInMemoryDatabase calls with the shared-connection SQLite pattern, enable foreign keys in the fixture, add EnsureCreated(), run the suite, and investigate the tests that newly fail — those are the false greens the InMemory provider was hiding. Each newly failing test is a production bug your test suite just caught for the first time.
The newly failing tests fall into two categories: tests that correctly identify real bugs in your application code (fix the code), and tests that were written to pass against InMemory and never reflected the intended production behaviour (fix the test assertion or the test setup). Both categories represent value recovered from a suite that was previously providing false confidence. Treat each newly failing test as a discovery, not a regression.
// ════════════════════════════════════════════════════════════════════════════
// MIGRATION CHECKLIST: InMemory → SQLite In-Memory
// ════════════════════════════════════════════════════════════════════════════
// ── Step 1: Update packages ───────────────────────────────────────────────
// In your test project .csproj:
//
// REMOVE:
//
//
// ADD:
//
//
// ── Step 2: Find all UseInMemoryDatabase calls ────────────────────────────
// Search: UseInMemoryDatabase
// Replace each with the shared-connection SQLite pattern.
//
// BEFORE:
// var options = new DbContextOptionsBuilder()
// .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
// .Options;
//
// AFTER: use the SqliteTestFixture base class (defined in this article)
// public sealed class MyTests : SqliteTestFixture
// {
// [Fact]
// public async Task MyTest()
// {
// await using var ctx = CreateContext();
// // ... test code unchanged
// }
// }
// ── Step 3: Verify foreign key pragma is active ───────────────────────────
// Add this test to your fixture — if it fails, the pragma is not running.
[Fact]
public async Task SQLite_ForeignKeyPragma_IsEnabled()
{
await using var ctx = CreateContext();
// This raw SQL query returns 1 if FK enforcement is enabled, 0 if not.
var result = await ctx.Database
.SqlQueryRaw("PRAGMA foreign_keys;")
.FirstAsync();
result.Should().Be(1,
because: "foreign key enforcement must be enabled via PRAGMA to catch FK violations");
}
// ── Step 4: Run the suite and triage newly failing tests ──────────────────
// Newly failing tests after the migration fall into three categories:
//
// Category A — Application code bug (highest value discovery):
// The test correctly asserts behaviour that InMemory was silently accepting.
// Your application code has a constraint violation, FK issue, or TX bug.
// FIX: fix the application code. The test is correct.
//
// Category B — Test setup was wrong (second most common):
// The test seeds data in a way that violates constraints — e.g., inserts
// an entity without required fields because InMemory didn't care.
// FIX: fix the test data setup to satisfy constraints, as production would.
//
// Category C — Test assertion was testing InMemory artefact (less common):
// The test was asserting InMemory-specific behaviour (e.g., that a
// rolled-back transaction leaves data in place) rather than correct behaviour.
// FIX: fix the assertion to reflect what the production database actually does.
// ── Step 5: Verify LINQ queries in test output ────────────────────────────
// Enable SQL logging for one test run to surface untranslatable expressions:
protected AppDbContext CreateContextWithLogging() =>
new(new DbContextOptionsBuilder()
.UseSqlite(_connection)
.LogTo(output.WriteLine, LogLevel.Information) // xUnit ITestOutputHelper
.EnableSensitiveDataLogging()
.Options);
// Read the generated SQL in test output.
// Any query that shows "(null)" SQL or unexpectedly large result sets
// before an in-memory filter is a candidate for rewriting.
// ── Step 6: Mark tests that require a real database ──────────────────────
// Tests that genuinely need the production provider belong in a separate
// test class or project marked with a custom trait:
[Trait("Category", "RequiresRealDatabase")]
public sealed class SqlServerSpecificTests
{
// Tests using JSON columns, spatial types, rowversion, etc.
// These are skipped in local dev (no Docker) and run in CI only.
}
// In CI: dotnet test --filter "Category=RequiresRealDatabase"
// In local dev: dotnet test --filter "Category!=RequiresRealDatabase"
// ── Step 7: Document the standard in the test project README ─────────────
// Add to Tests/README.md:
//
// ## Database Provider Policy
// All EF Core tests use SQLite in-memory via SqliteTestFixture.
// Foreign key enforcement is enabled. EnsureCreated() applies the schema.
// Tests requiring provider-specific features (JSON, spatial, rowversion)
// are marked [Trait("Category", "RequiresRealDatabase")] and run in CI only.
// The InMemory provider is NOT used for any test that touches SaveChanges,
// constraints, transactions, or relational LINQ expressions.
Step 3 — the foreign key pragma verification test — is the canary in the migration. If this test fails, your entire SQLite fixture is running without FK enforcement and the migration has not achieved its goal. Run it first, independently, before running any other test in the migrated suite. A passing pragma test is the baseline that makes every subsequent test result meaningful. Without it you have replaced one form of false confidence (InMemory ignoring constraints) with a subtler form (SQLite ignoring FK constraints) and will discover the difference in production rather than in the test suite.