skip to content

In ASP.NET Core database tests, when does a per-test rolled-back transaction work instead of Respawn, and why does it fail through WebApplicationFactory?

level: seniorimportance: should knowfreq 30%

answer

  1. the test must own the connection
  2. BeginTransaction, UseTransaction, never commit
  3. each request gets its own scoped DbContext
  4. the app opens its own pooled connection
  5. PreserveExecutionContext is false by default

basics

~20 s

Rollback works when the test owns the connection: open it, begin a transaction, attach the DbContext with UseTransaction, and dispose without committing. Through WebApplicationFactory each request uses its own DbContext and pooled connection, so its writes commit outside the test's transaction.

solid answer

~40 s

A rolled-back transaction is the cheapest reset there is, but only when everything the test does goes through **one connection the test controls**. For a repository or query test I open an `NpgsqlConnection`, call `BeginTransactionAsync()`, build the `OrdersDbContext` on that connection, call `Database.UseTransactionAsync(tx)`, act and assert, then dispose the transaction without committing. Through `WebApplicationFactory`, the HTTP request runs in the app's own DI scope with its own scoped `DbContext`, which takes its own connection from the pool and commits on `SaveChanges`. Rows the test seeded inside its open transaction are invisible to the request, and the request's writes survive the rollback. An ambient `TransactionScope` does not help either, because `TestServer` does not carry the client's `ExecutionContext` into the request unless `PreserveExecutionContext` is set. So HTTP-level tests reset with Respawn.

code

csharp · 23 lines
csharp
public sealed class OrderRepositoryTests(PostgresFixture db) : IClassFixture<PostgresFixture>, IAsyncLifetime
{
    private NpgsqlConnection _conn = default!;
    private NpgsqlTransaction _tx = default!;
    private OrdersDbContext _context = default!;

    public async ValueTask InitializeAsync()
    {
        _conn = new NpgsqlConnection(db.ConnectionString);
        await _conn.OpenAsync();
        _tx = await _conn.BeginTransactionAsync();
        _context = new OrdersDbContext(new DbContextOptionsBuilder<OrdersDbContext>()
            .UseNpgsql(_conn).Options);
        await _context.Database.UseTransactionAsync(_tx);
    }

    public async ValueTask DisposeAsync()
    {
        await _context.DisposeAsync();
        await _tx.DisposeAsync();      // never committed: rolled back
        await _conn.DisposeAsync();
    }
}

go deeper

for a junior

Remember the rule: a rollback only undoes what ran on the test's own connection and transaction, and it is never committed.

for a middle

Explain the mechanics: BeginTransactionAsync on an open connection, UseNpgsql with that connection, UseTransactionAsync, then dispose without commit.

for a senior

Show why HTTP tests escape the rollback: a scoped DbContext and pooled connection per request, commits inside the endpoint, and TestServer not flowing the ExecutionContext by default.

for a principal

Choose per layer: rollback for parallel data-access tests, Respawn for serial HTTP tests, and accept the cost of two patterns in exchange for speed where it is safe.

## The idea If a test does all its work inside a database transaction and then **rolls back**, the database is exactly as it was before the test, with no delete script and no knowledge of the schema. It is also very fast, because PostgreSQL discards the uncommitted changes. The catch is the condition hidden in the first sentence: **all** the work must happen inside that one transaction, which means on one connection. ## Where it works: the test owns the DbContext Repository tests, query tests and tests of an EF Core configuration create the `DbContext` themselves: 1. open a connection to the Testcontainers database; 2. `BeginTransactionAsync()` on it; 3. build `DbContextOptions` with `UseNpgsql(connection)`, passing the **connection object**, not the string; 4. create the context and call `context.Database.UseTransactionAsync(transaction)` so its commands join that transaction; 5. act and assert; `SaveChangesAsync()` writes inside the transaction and the test can read its own writes; 6. dispose the transaction without `CommitAsync()`, which rolls it back. In xunit.v3 steps 1, 2 and 4 belong in the test class's `InitializeAsync` and step 6 in its `DisposeAsync`, so every test gets the pattern without repeating it. ## Where it fails: the request owns the DbContext An HTTP test through `WebApplicationFactory<Program>` does not create the `DbContext`; the app does. For each request the app creates a DI scope, resolves its own scoped `OrdersDbContext`, and that context opens **its own connection** from Npgsql's pool. - **Seed data is invisible.** The test inserted a customer inside its open transaction; the request's connection reads committed data only, so the endpoint answers 404. - **The app's writes escape.** `SaveChangesAsync()` in the endpoint commits on the app's connection; the test's rollback cannot undo it, so the next test sees the order. - **Forcing one connection changes the app.** Registering a single shared connection and transaction for the factory makes concurrent requests share a connection, which a `DbContext` and an Npgsql connection are not built for, and any endpoint that calls `Database.BeginTransactionAsync()` on a context attached to the test's transaction fails with `The connection is already in a transaction and cannot participate in another transaction.` ## Why TransactionScope does not rescue it An ambient `System.Transactions.TransactionScope` flows with the async `ExecutionContext`, and Npgsql connections enlist in it by default (`Enlist=true`). But `TestServer`, the in-memory server behind `WebApplicationFactory`, does **not** carry the client's `ExecutionContext` into the request: `TestServerOptions.PreserveExecutionContext` defaults to `false`. With `UseKestrel()` the request arrives over a real socket and nothing flows at all. Setting the flag to `true` makes the scope flow, but the suite then depends on ambient-transaction enlistment that the production app never uses; Respawn is the simpler tool. ## Choosing | | Rolled-back transaction | Respawn | |---|---|---| | Speed per test | fastest | one TRUNCATE or a few DELETEs | | Works for repository and query tests | yes | yes | | Works through `WebApplicationFactory` | no | yes | | Code that commits its own transaction | breaks | works | | Parallel tests on one database | yes, each on its own connection | no, a reset wipes everyone's rows | A common split in an order-management API: data-access tests use per-test rollback on a shared container and can run in parallel, while HTTP tests share a factory, run serially in one collection, and reset with Respawn before each test. ## Making the pattern reusable - Put the connection, transaction and context setup in a small base class or helper that test classes inherit, so nobody forgets `UseTransactionAsync` or commits by accident. - Seed reference data, such as order statuses, through migrations once; per-test rows are inserted inside the transaction and vanish with it. - Build every context a test uses on the same open connection and attach it to the same transaction with `UseTransactionAsync`; a context built from the connection string opens a separate connection outside the rollback. - Remember that a rollback hides what a commit would reveal: a deferred constraint checked only at commit time never fires. Tests of such constraints belong in the Respawn group.

  • Why pass the connection object to UseNpgsql rather than the connection string?
    With a string, the `DbContext` opens its own connection from the pool, which is not the one carrying the test's transaction, so `UseTransactionAsync` would fail because the transaction belongs to a different connection. Passing the open `NpgsqlConnection` makes the context use exactly that connection, and the transaction attached to it.
  • The code under test calls Database.BeginTransactionAsync() itself. What happens in a rollback-style test?
    EF Core sees that the connection already has a transaction, the test's one attached with `UseTransactionAsync`, and throws `InvalidOperationException`: the connection is already in a transaction and cannot participate in another. Such code needs Respawn-style resets, or a savepoint-based design, rather than an outer test transaction.

  • Wrap the HttpClient call in a TransactionScope and the endpoint's writes roll back.
  • Each request in WebApplicationFactory reuses the test's DbContext instance.
  • UseTransaction makes every DbContext in the app join the test's transaction.
  • A rolled-back test transaction still lets the endpoint see the seeded rows.
  • Register the DbContext as a singleton in tests so all requests share one transaction.