SQLite In-Memory Done Right — Which Is Not the Default
SQLite in-memory is the correct tool for EF Core integration tests that need real relational behaviour without a database server. Fast, dependency-free, constraint-enforcing, transaction-supporting — everything the InMemory provider is not. But "SQLite in-memory" done the way most tutorials show it — passing DataSource=:memory: as a connection string to each DbContext independently — silently creates a separate empty database per context instance and destroys it on dispose. Data seeded in one context is invisible to another. Tests pass because they never actually test cross-context data persistence, which is the entire point of the test.
There are four specific things you must get right for SQLite in-memory to give you genuine relational test coverage: keep one connection open across all context instances in the test, apply the schema once using EnsureCreated(), enable foreign key enforcement with PRAGMA foreign_keys = ON before any test runs, and structure your fixture so each test gets a clean database state without paying the cost of schema recreation. Get all four right and you have a test infrastructure that catches constraint violations, FK failures, and transaction bugs before they reach production. Miss any one of them and you have a faster version of the same false-green problem you were trying to escape.
The Shared Connection Pattern: Why It Exists and How It Works
SQLite in-memory databases have a lifetime rule that is the source of most SQLite testing confusion: the database exists only as long as its connection is open. The moment the last connection to an in-memory database closes, the database is gone — all data, all schema, erased. When you pass a connection string to each DbContextOptionsBuilder, EF Core opens a new connection per context and closes it on disposal. Each context gets a brand-new empty database. Each context's dispose destroys it. No context can see data written by another context. Your seed data is invisible to your queries.
The fix is architecturally simple: open one SqliteConnection explicitly, keep it open for the entire test fixture lifetime, and hand the same open connection object — not a string — to every DbContextOptionsBuilder. All contexts then connect to the same in-memory database. Data written by a seed context is visible to the test context. Data written inside a test is visible to a verification context. The database survives for as long as the fixture lives, and is destroyed cleanly when the fixture disposes the connection.
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
// ── Package requirements ───────────────────────────────────────────────────
// dotnet add package Microsoft.EntityFrameworkCore.Sqlite --project YourTests
// dotnet add package Microsoft.Data.Sqlite --project YourTests
// ── SqliteTestFixture: reusable base class for all EF Core tests ──────────
// Implements IDisposable for xUnit. Adapt Dispose() for NUnit [TearDown]
// or MSTest [TestCleanup] as needed — the connection management is identical.
public abstract class SqliteTestFixture : IDisposable
{
// The ONE connection that keeps the in-memory database alive.
// Private — test classes interact with the database only through CreateContext().
private readonly SqliteConnection _connection;
// Exposed so test classes can pass the same options to additional context types
// (e.g., a read-model DbContext or an admin DbContext sharing the same schema).
protected readonly DbContextOptions Options;
protected SqliteTestFixture()
{
// ── Connection string options ─────────────────────────────────────
// "DataSource=:memory:"
// → Private in-memory DB. Only this connection can see it.
// → Destroyed when connection closes. Correct for isolated test fixtures.
//
// "DataSource=testdb;Mode=Memory;Cache=Shared"
// → Named shared in-memory DB. Multiple connections can share it.
// → Requires all connections to use the same name.
// → More complex lifetime management. Use only if you need multiple
// independent SqliteConnection objects to share the same database.
// → "DataSource=:memory:" with a single shared connection is simpler.
_connection = new SqliteConnection("DataSource=:memory:");
// Open ONCE. Keep open. The database now exists and will persist
// until Dispose() closes this connection.
_connection.Open();
// ── Enable foreign key enforcement ────────────────────────────────
// SQLite does NOT enforce FK constraints by default.
// This PRAGMA must be executed on the connection before any schema
// creation or data operations — it is a connection-level setting.
// Without it: inserting orphaned rows succeeds silently.
// With it: FK violations throw SqliteException as they should.
using (var pragmaCmd = _connection.CreateCommand())
{
pragmaCmd.CommandText = "PRAGMA foreign_keys = ON;";
pragmaCmd.ExecuteNonQuery();
}
// Build DbContextOptions using the open connection — not a string.
// Every context created with these options shares the same database.
Options = new DbContextOptionsBuilder()
.UseSqlite(_connection)
// Suppress the warning about using SQLite for testing scenarios
// that differ from the production provider.
.ConfigureWarnings(w =>
w.Ignore(RelationalEventId.AmbientTransactionWarning))
.Options;
// ── Apply schema once for the fixture lifetime ────────────────────
// EnsureCreated() reads the current EF Core model and creates all
// tables, columns, indexes, and check constraints in one operation.
// It is idempotent — safe to call on an already-created schema.
// Does NOT create __EFMigrationsHistory (no migration overhead in tests).
using var schemaCtx = new AppDbContext(Options);
schemaCtx.Database.EnsureCreated();
// After EnsureCreated(), the in-memory database has the full schema.
// All subsequent contexts created from Options see this schema.
}
// ── Factory method: new context per test operation ────────────────────
// Call this inside each test method — never share a DbContext between tests.
// Each context has its own change tracker, its own transaction scope,
// but all share the same underlying in-memory SQLite database.
protected AppDbContext CreateContext() => new(Options);
// ── Create a second context type sharing the same database ────────────
// Useful for admin contexts, read-model contexts, or reporting contexts
// that use a different DbContext class but the same schema.
protected TContext CreateContext(
DbContextOptions contextOptions)
where TContext : DbContext =>
(TContext)Activator.CreateInstance(typeof(TContext), contextOptions)!;
// ── Cleanup between tests ─────────────────────────────────────────────
// Call this at the start of each test (or in a BeforeEach) to ensure
// a clean state. Deletes rows — NOT the schema — so EnsureCreated()
// does not need to run again. Faster than schema recreation.
protected async Task ClearAllTablesAsync()
{
await using var ctx = CreateContext();
// Delete in reverse dependency order to avoid FK constraint failures.
// Adjust the order to match your entity relationships.
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM OrderLines;");
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM Orders;");
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM Products;");
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM Customers;");
// SQLite resets auto-increment sequences on DELETE — different from
// SQL Server IDENTITY which requires DBCC CHECKIDENT. Account for this
// if your tests assert on specific auto-generated ID values.
}
public void Dispose()
{
// Closing the connection destroys the in-memory database.
// All data and schema are gone — clean slate for the next fixture.
_connection.Dispose();
GC.SuppressFinalize(this);
}
}
// ── xUnit: IClassFixture for schema shared across all tests in a class ────
// Use when: all tests in the class can share one schema, cleaned between tests.
// Use IAsyncLifetime if you need async setup/teardown.
public sealed class ProductTests : SqliteTestFixture
{
// Each [Fact] gets a fresh DbContext. The schema persists across all facts.
// Call ClearAllTablesAsync() at the start of each test that needs isolation.
}
// ── xUnit: ICollectionFixture for schema shared across test classes ────────
// Use when: multiple test classes should share the same in-memory database.
// The fixture is created once per collection and disposed at the end.
[CollectionDefinition("SharedDatabase")]
public sealed class SharedDatabaseCollection
: ICollectionFixture { }
public sealed class SharedDatabaseFixture : SqliteTestFixture { }
[Collection("SharedDatabase")]
public sealed class OrderTests(SharedDatabaseFixture fixture)
{
[Fact]
public async Task Order_Is_Saved_And_Retrievable()
{
await fixture.ClearAllTablesAsync();
await using var ctx = fixture.CreateContext();
// ... test code
}
}
The PRAGMA foreign_keys = ON command must be executed on the connection before EnsureCreated() — not after. The pragma is a connection-level setting that applies to all subsequent operations on that connection. If you execute it after schema creation, the schema is in place but FK enforcement is not yet active for the schema creation DDL that already ran. Execute it immediately after _connection.Open() and before any other database operation. This ordering is the most commonly missed detail in SQLite fixture implementations and the one that produces the subtlest false greens — FK violations during schema setup that succeed silently and leave the database in a state that real databases would reject.
Schema Setup: EnsureCreated vs Migrations & What Each Actually Does
Two mechanisms can apply your EF Core model as a SQLite schema: EnsureCreated() and MigrateAsync(). They produce the same table structure but through different processes with different trade-offs. Understanding what each actually does determines which one belongs in your test fixture.
EnsureCreated() reads your current OnModelCreating configuration and generates the minimum DDL required to create the schema from scratch in a single operation. It creates no migration history table and applies no migration scripts — it directly materialises your current model. It is fast (typically under 10ms for a medium-sized model), idempotent, and produces exactly the schema your current code expects. MigrateAsync() runs each pending migration script in sequence, creates and maintains __EFMigrationsHistory, and is the production database initialisation mechanism. In tests, migration overhead adds noise. Use EnsureCreated() unless you are specifically testing that your migration scripts produce the correct schema — which is a valid but separate concern from testing data access behaviour.
// ════════════════════════════════════════════════════════════════════════════
// OPTION A: EnsureCreated() — correct for data access tests
// ════════════════════════════════════════════════════════════════════════════
protected SqliteTestFixture()
{
// ... connection setup and PRAGMA (see fixture above) ...
using var ctx = new AppDbContext(Options);
// EnsureCreated() in detail:
// 1. Checks if any tables exist (queries sqlite_master)
// 2. If schema is empty: generates CREATE TABLE DDL from the EF model
// 3. Executes the DDL in a single transaction
// 4. Returns true if schema was created, false if it already existed
// 5. Does NOT touch __EFMigrationsHistory
// 6. Does NOT run any migration scripts
// 7. Does NOT apply seed data configured in OnModelCreating
// (use HasData() only for reference data — seed test data in tests)
var created = ctx.Database.EnsureCreated();
// Verify the schema was applied — defensive check for fixture correctness.
// If EnsureCreated() returns false, the schema already existed (unexpected
// in a fresh in-memory database — indicates a fixture setup issue).
if (!created)
throw new InvalidOperationException(
"EnsureCreated() returned false on a fresh in-memory database. " +
"The connection may be shared with a pre-existing schema. " +
"Verify the SqliteConnection is freshly opened.");
}
// ════════════════════════════════════════════════════════════════════════════
// OPTION B: MigrateAsync() — use only for migration script validation tests
// ════════════════════════════════════════════════════════════════════════════
// Use MigrateAsync() in a SEPARATE test class dedicated to migration validation.
// Do NOT use it as the default schema setup — it is 5-10x slower than EnsureCreated().
public sealed class MigrationValidationTests : IAsyncLifetime
{
private SqliteConnection? _connection;
private DbContextOptions? _options;
public async Task InitializeAsync()
{
_connection = new SqliteConnection("DataSource=:memory:");
_connection.Open();
using var pragmaCmd = _connection.CreateCommand();
pragmaCmd.CommandText = "PRAGMA foreign_keys = ON;";
pragmaCmd.ExecuteNonQuery();
_options = new DbContextOptionsBuilder()
.UseSqlite(_connection)
.Options;
// Run all migrations — this validates the migration scripts themselves
await using var ctx = new AppDbContext(_options);
await ctx.Database.MigrateAsync();
}
[Fact]
public async Task AllMigrations_Apply_Successfully()
{
// If InitializeAsync() completed without throwing, all migrations ran.
// This test verifies the migration pipeline is intact.
await using var ctx = new AppDbContext(_options!);
var appliedMigrations = await ctx.Database.GetAppliedMigrationsAsync();
var pendingMigrations = await ctx.Database.GetPendingMigrationsAsync();
appliedMigrations.Should().NotBeEmpty(
because: "at least one migration should have been applied");
pendingMigrations.Should().BeEmpty(
because: "all migrations should be applied after MigrateAsync()");
}
[Fact]
public async Task Migration_ProducesExpected_Schema()
{
await using var ctx = new AppDbContext(_options!);
// Verify specific tables and columns exist as expected
// by querying sqlite_master — provider-agnostic schema inspection
var tables = await ctx.Database
.SqlQueryRaw(
"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
.ToListAsync();
tables.Should().Contain("Orders");
tables.Should().Contain("OrderLines");
tables.Should().Contain("Products");
tables.Should().Contain("__EFMigrationsHistory",
because: "MigrateAsync should create the migrations history table");
}
public async Task DisposeAsync()
{
_connection?.Dispose();
await Task.CompletedTask;
}
}
// ════════════════════════════════════════════════════════════════════════════
// SCHEMA VERIFICATION HELPER — use in any fixture to confirm setup is correct
// ════════════════════════════════════════════════════════════════════════════
protected async Task VerifySchemaAsync()
{
await using var ctx = CreateContext();
// Verify FK pragma is active
var fkEnabled = await ctx.Database
.SqlQueryRaw("PRAGMA foreign_keys;")
.FirstAsync();
fkEnabled.Should().Be(1,
because: "PRAGMA foreign_keys must be ON for constraint tests to be meaningful");
// Verify tables exist
var tableCount = await ctx.Database
.SqlQueryRaw(
"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name != 'sqlite_sequence';")
.FirstAsync();
tableCount.Should().BeGreaterThan(0,
because: "EnsureCreated() should have created at least one table");
}
The migration validation test class is worth having as a permanent CI fixture even if you use EnsureCreated() for all other tests. It answers a specific question that EnsureCreated()-based tests cannot answer: do your migration scripts actually run in sequence without errors? A model that generates a correct schema via EnsureCreated() can have migration scripts with errors — incorrect column types, missing indexes, wrong defaults — that only surface when applying the scripts to a database that already has data from a previous schema version. The migration validation test catches this in CI before the migration reaches production.
Testing Constraint Enforcement: The Assertions That Actually Matter
With the shared connection established and the schema applied, the test suite can now assert on relational behaviour that the InMemory provider silently ignored. Required field violations throw. Unique index violations throw. Foreign key violations throw. Transaction rollbacks actually roll back. Each of these scenarios has a specific test shape: attempt the violation, assert the exception type, assert the exception message contains the expected constraint name. Verify the database state after the failure is what you expect — no partial writes, no orphaned rows.
public sealed class ConstraintEnforcementTests : SqliteTestFixture
{
// ── Required field: NOT NULL constraint ───────────────────────────────
[Fact]
public async Task SaveProduct_WithNullName_ThrowsDbUpdateException()
{
await ClearAllTablesAsync();
await using var ctx = CreateContext();
ctx.Products.Add(new Product
{
Name = null!, // [Required] maps to NOT NULL in SQLite
Sku = "SKU-001",
Price = 9.99m
});
var act = async () => await ctx.SaveChangesAsync();
// SQLite enforces NOT NULL — throws as SQL Server would
await act.Should().ThrowAsync()
.WithMessage("*NOT NULL*",
because: "inserting null into a NOT NULL column must be rejected");
}
// ── Unique index: duplicate value rejection ────────────────────────────
[Fact]
public async Task SaveProduct_WithDuplicateSku_ThrowsDbUpdateException()
{
await ClearAllTablesAsync();
// Seed a product with SKU-DUP
await using var seedCtx = CreateContext();
seedCtx.Products.Add(new Product { Name = "Original", Sku = "SKU-DUP", Price = 10m });
await seedCtx.SaveChangesAsync();
// Attempt to insert a second product with the same SKU
await using var testCtx = CreateContext();
testCtx.Products.Add(new Product { Name = "Duplicate", Sku = "SKU-DUP", Price = 20m });
var act = async () => await testCtx.SaveChangesAsync();
await act.Should().ThrowAsync()
.WithMessage("*UNIQUE constraint failed*",
because: "the HasIndex().IsUnique() configuration must be enforced");
}
// ── Foreign key: orphaned row rejection ───────────────────────────────
[Fact]
public async Task SaveOrderLine_WithNonExistentOrderId_ThrowsDbUpdateException()
{
await ClearAllTablesAsync();
await using var ctx = CreateContext();
ctx.OrderLines.Add(new OrderLine
{
OrderId = 99999, // no Order with this ID exists
ProductId = Guid.NewGuid(),
Quantity = 1,
UnitPrice = 15m
});
var act = async () => await ctx.SaveChangesAsync();
// PRAGMA foreign_keys = ON makes this throw — without it, it would succeed
await act.Should().ThrowAsync()
.WithMessage("*FOREIGN KEY constraint failed*",
because: "inserting an OrderLine with an invalid OrderId must be rejected");
}
// ── Transaction rollback: atomicity verification ───────────────────────
[Fact]
public async Task Transaction_Rollback_RemovesAllWritesFromScope()
{
await ClearAllTablesAsync();
await using var ctx = CreateContext();
// Begin a real SQLite transaction
await using var transaction = await ctx.Database.BeginTransactionAsync();
// Write data within the transaction scope
var customer = new Customer { Name = "Rollback Corp", Email = "test@rollback.com" };
ctx.Customers.Add(customer);
await ctx.SaveChangesAsync();
var order = new Order { CustomerId = customer.Id, Status = "Draft", Total = 250m };
ctx.Orders.Add(order);
await ctx.SaveChangesAsync();
// Simulate an error condition — roll back the entire transaction
await transaction.RollbackAsync();
// Verify both writes were undone — use a FRESH context to avoid
// reading from the change tracker cache rather than the database
await using var verifyCtx = CreateContext();
var customerExists = await verifyCtx.Customers
.AnyAsync(c => c.Email == "test@rollback.com");
var orderExists = await verifyCtx.Orders
.AnyAsync(o => o.Status == "Draft");
customerExists.Should().BeFalse(
because: "the rolled-back customer must not persist in the database");
orderExists.Should().BeFalse(
because: "the rolled-back order must not persist in the database");
}
// ── Cascade delete: FK on delete behaviour ────────────────────────────
[Fact]
public async Task DeleteOrder_CascadesToOrderLines()
{
await ClearAllTablesAsync();
// Seed: one order with two lines
await using var seedCtx = CreateContext();
var order = new Order { Status = "Pending", Total = 100m };
order.Lines.Add(new OrderLine { ProductId = Guid.NewGuid(), Quantity = 1, UnitPrice = 60m });
order.Lines.Add(new OrderLine { ProductId = Guid.NewGuid(), Quantity = 2, UnitPrice = 20m });
seedCtx.Orders.Add(order);
await seedCtx.SaveChangesAsync();
var orderId = order.Id;
var lineCount = order.Lines.Count;
lineCount.Should().Be(2);
// Act: delete the parent order
await using var deleteCtx = CreateContext();
var toDelete = await deleteCtx.Orders.FindAsync(orderId);
deleteCtx.Orders.Remove(toDelete!);
await deleteCtx.SaveChangesAsync();
// Verify: lines cascade-deleted with the order
await using var verifyCtx = CreateContext();
var remainingLines = await verifyCtx.OrderLines
.Where(l => l.OrderId == orderId)
.CountAsync();
remainingLines.Should().Be(0,
because: "cascade delete should remove all OrderLines when the Order is deleted");
}
// ── Concurrency token: optimistic locking with provider-agnostic token ─
[Fact]
public async Task ConcurrentUpdate_WithStaleRowVersion_ThrowsConcurrencyException()
{
await ClearAllTablesAsync();
// Seed a document
await using var seedCtx = CreateContext();
var doc = new Document { Title = "Original", RowVersion = Guid.NewGuid() };
seedCtx.Documents.Add(doc);
await seedCtx.SaveChangesAsync();
var docId = doc.Id;
// Two contexts load the same document (simulating concurrent users)
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 wins — updates successfully
doc1!.Title = "Updated by User 1";
doc1.RowVersion = Guid.NewGuid(); // increment token
await ctx1.SaveChangesAsync();
// Second context tries to update with the OLD RowVersion — stale
doc2!.Title = "Updated by User 2";
// doc2.RowVersion still has the original value → mismatch detected by EF Core
var act = async () => await ctx2.SaveChangesAsync();
await act.Should().ThrowAsync(
because: "EF Core should detect the stale concurrency token and reject the update");
}
}
The fresh verification context in the transaction rollback test is not a style preference — it is a correctness requirement. Reading from the same DbContext that performed the writes returns data from the change tracker cache, not from the database, regardless of whether the transaction was rolled back. The change tracker does not know the transaction was rolled back — it still holds the entity states from the session. A fresh DbContext has an empty change tracker and must hit the database for every query, so it accurately reflects what is actually persisted. Always verify post-operation database state with a fresh context.
Test Data Seeding Strategies: Isolation Without Slowness
Test data isolation — ensuring each test starts from a known state and does not affect other tests — is the hardest operational challenge in a shared in-memory fixture. Two strategies work well in practice: per-test table deletion and per-test transaction wrapping. Table deletion is simpler to implement and works with any test structure. Transaction wrapping is faster but requires your application code under test to not commit transactions internally. Choose based on your application's transaction usage patterns.
// ════════════════════════════════════════════════════════════════════════════
// STRATEGY A: DELETE-PER-TEST (simpler, always correct)
// ════════════════════════════════════════════════════════════════════════════
public sealed class OrderRepositoryTests : SqliteTestFixture
{
// Runs before each test — clean slate guaranteed
private async Task SeedAsync(Action configure)
{
await ClearAllTablesAsync();
await using var ctx = CreateContext();
configure(ctx);
await ctx.SaveChangesAsync();
}
[Fact]
public async Task GetOrderById_ReturnsCorrectOrder()
{
// Arrange: seed exactly the data this test needs
Guid seededOrderId = default;
await SeedAsync(ctx =>
{
var customer = new Customer { Name = "Acme", Email = "acme@test.com" };
var order = new Order { Status = "Pending", Total = 199m, Customer = customer };
order.Lines.Add(new OrderLine
{
ProductId = Guid.NewGuid(),
Quantity = 1,
UnitPrice = 199m
});
ctx.Orders.Add(order);
// Capture the ID via closure after SaveChanges assigns it
seededOrderId = order.Id; // assigned after SaveChangesAsync in SeedAsync
});
// Need the ID after save — use a separate seed approach
await ClearAllTablesAsync();
await using var seedCtx = CreateContext();
var cust = new Customer { Name = "Acme Corp", Email = "acme@corp.com" };
var ord = new Order { Status = "Confirmed", Total = 350m, Customer = cust };
seedCtx.Orders.Add(ord);
await seedCtx.SaveChangesAsync();
seededOrderId = ord.Id;
// Act: query through the repository under test
var repo = new OrderRepository(CreateContext());
var result = await repo.GetByIdAsync(seededOrderId);
// Assert
result.Should().NotBeNull();
result!.Id.Should().Be(seededOrderId);
result.Total.Should().Be(350m);
result.Customer.Name.Should().Be("Acme Corp");
}
}
// ════════════════════════════════════════════════════════════════════════════
// STRATEGY B: TRANSACTION ROLLBACK PER TEST (faster for large test suites)
// ════════════════════════════════════════════════════════════════════════════
// Requirements: application code under test must NOT call CommitAsync()
// on the ambient transaction. If your repositories auto-commit, use Strategy A.
public sealed class ProductServiceTests : SqliteTestFixture, IAsyncLifetime
{
private IDbContextTransaction? _transaction;
private AppDbContext? _testContext;
// Called before each test by xUnit (IAsyncLifetime)
public async Task InitializeAsync()
{
// One context and one transaction span the entire test
_testContext = CreateContext();
_transaction = await _testContext.Database.BeginTransactionAsync();
// Everything this test writes is within this transaction — rolled back in DisposeAsync
}
// Called after each test by xUnit (IAsyncLifetime)
public async Task DisposeAsync()
{
// Rollback undoes ALL writes made during the test — no DELETE needed
if (_transaction is not null)
{
await _transaction.RollbackAsync();
await _transaction.DisposeAsync();
}
_testContext?.Dispose();
}
[Fact]
public async Task CreateProduct_PersistsToDatabase()
{
// All writes in this test are within _transaction — rolled back after
var product = new Product { Name = "Test Widget", Sku = "TW-001", Price = 29.99m };
_testContext!.Products.Add(product);
await _testContext.SaveChangesAsync(); // within the transaction
// Verify within the SAME transaction scope (can see uncommitted data)
var saved = await _testContext.Products
.FirstOrDefaultAsync(p => p.Sku == "TW-001");
saved.Should().NotBeNull();
saved!.Name.Should().Be("Test Widget");
// After DisposeAsync() rolls back this transaction, no trace of this product
// remains in the database — the next test starts completely clean.
}
// ── When transaction rollback is NOT appropriate ──────────────────────
// 1. Application code under test calls CommitAsync() internally
// → The commit finalises the transaction; rollback cannot undo committed data
// 2. Test verifies cross-transaction visibility
// → Uncommitted data is not visible to other connections
// → Use Strategy A (DELETE) for these cases
// 3. Test verifies the exception from a constraint violation
// → Some databases invalidate the transaction after an exception
// → SQLite aborts the statement but not the transaction — usually works
// → Verify with a specific test if constraint exception handling is critical
}
// ════════════════════════════════════════════════════════════════════════════
// HYBRID: per-class transaction + per-test seeding for large suites
// ════════════════════════════════════════════════════════════════════════════
// For test classes with many tests that share common seed data:
// 1. Create a class-level IClassFixture with the shared schema (SqliteTestFixture)
// 2. Seed common reference data once in the fixture constructor
// 3. Wrap each test in its own transaction (Strategy B) to isolate mutations
// 4. Reference data is always visible (pre-committed); test mutations roll back
// This pattern gives you:
// ✓ Fast setup (reference data seeded once, not per-test)
// ✓ Isolated test mutations (transaction rollback)
// ✓ Clean state per test (no leaked writes between tests)
The transaction rollback strategy is 3–5x faster than the delete strategy for large test suites because it avoids the SQL round-trips required to delete rows from every table. The constraint is real but narrow: your application code must not commit the ambient transaction internally. For repositories and services that accept an injected DbContext and call SaveChangesAsync() without wrapping it in their own BeginTransaction/Commit, the transaction rollback strategy works perfectly. The moment your code path calls CommitAsync(), the committed data is permanent for the test database's lifetime and the rollback at test end has nothing to undo for that scope.
WebApplicationFactory Integration: Full HTTP Stack With SQLite In-Memory
The shared connection pattern extends naturally to WebApplicationFactory-based integration tests — tests that send real HTTP requests through your ASP.NET Core pipeline and verify the responses, while still using an in-memory database with no external infrastructure. The SqliteConnection must live on the factory instance, not on the test class, because the factory creates its own DI scopes per request. A connection stored on the test class is disposed before the factory's scoped services can use it.
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
// ── SqliteWebApplicationFactory: replaces the production DbContext ─────────
// Stores the SqliteConnection on the factory — lives for the test class lifetime.
// All HTTP requests routed through this factory share the same in-memory database.
public sealed class SqliteWebApplicationFactory
: WebApplicationFactory, IAsyncLifetime
where TProgram : class
{
private readonly SqliteConnection _connection;
public SqliteWebApplicationFactory()
{
_connection = new SqliteConnection("DataSource=:memory:");
}
// IAsyncLifetime.InitializeAsync — called before any test in the class
public async Task InitializeAsync()
{
_connection.Open();
// Enable FK enforcement on the factory connection
using var cmd = _connection.CreateCommand();
cmd.CommandText = "PRAGMA foreign_keys = ON;";
cmd.ExecuteNonQuery();
// Apply schema using the factory's service provider
// (after ConfigureWebHost has replaced the DbContext registration)
using var scope = Services.CreateScope();
var ctx = scope.ServiceProvider.GetRequiredService();
ctx.Database.EnsureCreated();
await Task.CompletedTask;
}
// IAsyncLifetime.DisposeAsync — called after all tests in the class
public new async Task DisposeAsync()
{
_connection.Dispose();
await base.DisposeAsync();
}
// ConfigureWebHost: replace production DB registration with SQLite in-memory
protected override void ConfigureWebHost(IWebHostBuilder builder)
{
builder.ConfigureServices(services =>
{
// Remove the production DbContext registration
var descriptor = services.SingleOrDefault(
d => d.ServiceType == typeof(DbContextOptions));
if (descriptor is not null)
services.Remove(descriptor);
// Also remove the DbContext itself if registered directly
var ctxDescriptor = services.SingleOrDefault(
d => d.ServiceType == typeof(AppDbContext));
if (ctxDescriptor is not null)
services.Remove(ctxDescriptor);
// Register SQLite in-memory DbContext using the factory's shared connection
services.AddDbContext(options =>
options.UseSqlite(_connection));
// All request scopes get a DbContext connected to _connection.
// All HTTP requests in the test class share the same in-memory database.
});
}
// Helper: create a direct DbContext for seeding and verification
// (bypasses the HTTP pipeline — faster than seeding via HTTP POST)
public AppDbContext CreateDbContext()
{
var options = new DbContextOptionsBuilder()
.UseSqlite(_connection)
.Options;
return new AppDbContext(options);
}
// Helper: clean all tables between tests
public async Task ClearDatabaseAsync()
{
await using var ctx = CreateDbContext();
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM OrderLines;");
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM Orders;");
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM Products;");
await ctx.Database.ExecuteSqlRawAsync("DELETE FROM Customers;");
}
}
// ── Test class using the factory ──────────────────────────────────────────
public sealed class OrderApiTests
: IClassFixture>,
IAsyncLifetime
{
private readonly SqliteWebApplicationFactory _factory;
private readonly HttpClient _client;
public OrderApiTests(SqliteWebApplicationFactory factory)
{
_factory = factory;
_client = factory.CreateClient();
}
public async Task InitializeAsync() =>
await _factory.ClearDatabaseAsync(); // clean state per test
public Task DisposeAsync() => Task.CompletedTask;
[Fact]
public async Task POST_CreateOrder_Returns201_AndPersistsToDatabase()
{
// Arrange: seed a customer directly (bypassing HTTP for speed)
await using var ctx = _factory.CreateDbContext();
var customer = new Customer { Name = "HTTP Test Corp", Email = "http@test.com" };
ctx.Customers.Add(customer);
await ctx.SaveChangesAsync();
var request = new CreateOrderRequest(
CustomerId: customer.Id,
Lines: [new OrderLineRequest(ProductId: Guid.NewGuid(), Quantity: 2, UnitPrice: 49.99m)]);
// Act: send real HTTP request through the full ASP.NET Core pipeline
var response = await _client.PostAsJsonAsync("/api/orders", request);
// Assert HTTP response
response.StatusCode.Should().Be(HttpStatusCode.Created);
var created = await response.Content.ReadFromJsonAsync();
created.Should().NotBeNull();
created!.CustomerId.Should().Be(customer.Id);
// Assert database state — verify persistence, not just response shape
await using var verifyCtx = _factory.CreateDbContext();
var saved = await verifyCtx.Orders
.Include(o => o.Lines)
.FirstOrDefaultAsync(o => o.Id == created.Id);
saved.Should().NotBeNull();
saved!.Lines.Should().HaveCount(1);
saved.Lines.Single().UnitPrice.Should().Be(49.99m);
}
}
The CreateDbContext() helper on the factory is the correct mechanism for seeding data and verifying database state in WebApplicationFactory-based tests. Seeding via HTTP POST requests is slower, introduces coupling between your seed setup and your API contract, and can break if the endpoint's validation rules change. Direct database writes are faster, more stable, and test exactly one thing: the behaviour of the HTTP endpoint under test given a specific database state. Use HTTP for the action under test. Use direct database access for setup and verification.