Test Migrations, Concurrency, Transactions, Failure Retries, Query Counts, and Performance Regressions

Learning outcomes

1. Reliability tests should target known failure mechanisms

A green CRUD test does not prove that an upgrade preserves data, that two writers cannot lose updates, that a rollback leaves the database unchanged, or that a refactor did not introduce 101 queries. The final testing lesson converts the mechanisms from Chapters 10–18 into small deterministic regression suites.

2. Migration test: upgrade representative old state, not only empty databases

Creating a database directly at the latest migration proves the forward path from nothing. Production upgrades usually start from an older schema containing real-shaped data. Keep one or more representative baseline artifacts—generated from known migrations, never copied customer data—and migrate them forward.

var dbPath = TestDatabase.CopyBaseline("servicehub-chapter22.db");await using var db = CreateSqliteFileContext(dbPath, "tenant-a");var before = await ReadProtectedFixtureRows(db);await db.Database.MigrateAsync();var after = await ReadProtectedFixtureRows(db);Assert.Equal(before.WorkOrderCount, after.WorkOrderCount);Assert.Equal(before.KnownNumbers, after.KnownNumbers);Assert.Empty(await db.Database.GetPendingMigrationsAsync());

For provider-specific migrations, run the same idea on the production-like provider. Verify renamed columns, backfills, indexes/constraints, data type conversions and migration history—not merely that MigrateAsync returned.

3. Concurrency test: synchronize the read, then order the competing writes

await using var a = fixture.CreateContext("tenant-a");await using var b = fixture.CreateContext("tenant-a");var rowA = await a.WorkOrders.SingleAsync(w => w.Id == id);var rowB = await b.WorkOrders.SingleAsync(w => w.Id == id);Assert.Equal(rowA.Revision, rowB.Revision);rowA.ReviseSummary("writer-a");rowA.AdvanceRevision();await a.SaveChangesAsync();rowB.ReviseSummary("writer-b");rowB.AdvanceRevision();await Assert.ThrowsAsync<DbUpdateConcurrencyException>(    () => b.SaveChangesAsync());

This verifies EF optimistic concurrency. It is intentionally distinct from a database lock timeout or deadlock, which follows a different retry/diagnostic policy.

4. Transaction/savepoint test: verify the persisted state from another context

await using var db = fixture.CreateContext("tenant-a");await using var tx = await db.Database.BeginTransactionAsync();var row = await db.WorkOrders.SingleAsync(w => w.Id == id);row.ReviseSummary("temporary");row.AdvanceRevision();await db.SaveChangesAsync();await tx.RollbackAsync();await using var verify = fixture.CreateContext("tenant-a");var persisted = await verify.WorkOrders.AsNoTracking()    .SingleAsync(w => w.Id == id);Assert.NotEqual("temporary", persisted.Summary);

Do not assert only the original context’s tracker after rollback; tracker state and database state can diverge until entries are reloaded/reconciled.

5. Retry tests: classify transient infrastructure faults separately

A DbUpdateConcurrencyException is a business-state conflict and should not be blindly retried by a transient infrastructure policy. Retry tests belong on a provider/environment where the configured execution strategy recognizes the simulated fault. For SQL Server/Azure SQL, a deterministic command interceptor or fault proxy can fail the first attempt with a retryable condition; the test then asserts the command eventually succeeds once and that idempotency prevents duplicate side effects.

attempt 1 -> provider-classified transient failureexecution strategy schedules retryattempt 2 -> succeedscommitted business operation count == 1outbox/business key remains uniqueDbUpdateConcurrencyException is NOT treated as the same retry class

If the environment cannot deterministically produce a provider-recognized transient error, test the application’s retry/idempotency decision object separately and keep the provider integration test optional rather than fabricating an exception type that the real strategy would never retry.

6. Query-count tests expose N+1 without brittle SQL snapshots

public sealed class CommandCounter : DbCommandInterceptor{    private int _count;    public int Count => Volatile.Read(ref _count);    public override InterceptionResult<DbDataReader> ReaderExecuting(        DbCommand command, CommandEventData eventData,        InterceptionResult<DbDataReader> result)    {        Interlocked.Increment(ref _count);        return result;    }}

var counter = new CommandCounter();await using var db = fixture.CreateContext("tenant-a", counter);var result = await service.LoadDashboardAsync(db, ct);Assert.NotEmpty(result);Assert.InRange(counter.Count, 1, 3); // reviewed contract for this endpoint

The range is not a universal tuning value; it is an explicit contract for one known query shape. If the endpoint intentionally changes, review the budget and database plan rather than increasing it automatically.

7. Exact SQL snapshots are useful only when SQL itself is the contract

Provider upgrades can change aliases, parameter names, whitespace, query rewrites, or equivalent SQL. Prefer semantic assertions: required tenant predicate exists, query count is bounded, an index is used, a function translates server-side, a set operation remains one command. Snapshot exact SQL only for a narrow provider-specific boundary where generated SQL shape is intentionally part of the supported contract, and normalize unstable formatting first.

8. Performance regression tests require a disclosed environment

Performance tests should run Release builds, realistic cardinalities/distributions, known indexes/statistics, disclosed tracking mode, warmup/cache state, provider/engine versions, connection pooling, concurrency and network/container topology. Do not invent a universal “query must be under 100 ms” rule. Establish a reviewed baseline for the environment and investigate changes with EF logs plus a real database plan.

provider=Npgsql 10.0.3engine=PostgreSQL 18.6build=Release / .NET 10.0.11rows=250000 work_orders; tenant-a=45000query=dashboard-v3; AsNoTracking projectionwarmup=10; measured=30container=local Docker bridgeplan=attached artifact; indexes recordedbaseline distribution=stored with CI run metadata

9. Deliberate failure: one giant end-to-end test with retries and sleeps

A flaky test that starts two tasks “at the same time,” sleeps for arbitrary milliseconds, mutates schema, expects a timeout, and asserts an exact SQL string can fail for timing rather than correctness. Split the mechanisms: barriers for concurrency, explicit transactions for rollback, controlled provider fault injection for retries, command counters for N+1, and dedicated performance runs for timing.

10. Mandatory capstone lab: build a five-axis reliability suite

1. Migration: copy a representative prior ServiceHub SQLite baseline, migrate to latest, and assert tenant/work-order fixture data survives.
2. Concurrency: run the synchronized two-context Revision conflict and assert exactly one writer wins.
3. Transaction: make a tracked write inside an explicit transaction, roll it back, and verify with a fresh context.
4. Query count: instrument one related-data/dashboard path and set a reviewed command-count budget that catches an N+1 regression.
5. Provider fidelity: run at least one of those tests on PostgreSQL/SQL Server/MySQL production-like infrastructure when that provider is supported by the application.
6. Performance: record a Release-build baseline with versions, data distribution, topology and plan; do not fail the normal unit suite on noisy wall-clock timing.
7. Clean up every disposable database/container and retain only sanitized diagnostics required to investigate failures.

11. Production judgment and bridge

High-value EF tests are mechanism-specific: they reproduce the failure you care about and assert persisted or observable outcomes. Keep fast unit tests for pure decisions, SQLite for low-cost relational coverage, and production-like provider tests for semantics SQLite cannot certify. Chapter 24 now moves from proving correctness to resisting abuse: parameterization, dynamic SQL identifiers, secret handling, least privilege, and sensitive diagnostics.

Authoritative references

• EF Core migrations — upgrade paths, migration history and schema evolution
• EF Core concurrency — DbUpdateConcurrencyException and optimistic conflict handling
• EF Core transactions — explicit transactions, rollback and savepoints
• Connection resiliency — execution strategies, retries, user transactions and ambiguous commits
• EF Core interceptors — command interception for query counts/fault diagnostics
• EF Core performance — measurement discipline and database evidence