Integration tests against a real database

Unit tests with mocked repositories are fast, but they mostly test that your mocks return what you told them to. The bugs that reach production are usually at the edges: a query EF Core can't translate, a unique index you forgot, a migration that fails, JSON that doesn't bind.

The EF Core in-memory provider doesn't catch those either. It isn't a relational database: no constraints, no transactions, no SQL translation. Microsoft's own guidance discourages using it for testing.

The better option is to run tests against a real database in a container.

#The pieces

• WebApplicationFactory<Program> (from Microsoft.AspNetCore.Mvc.Testing) starts your real API in memory, with its real startup, middleware and dependency injection, and gives you an HttpClient to call it.
• Testcontainers (the Testcontainers.MsSql package, with others for PostgreSQL, MongoDB and more) starts a disposable database container for the test run and removes it afterwards.

#Setting it up with xUnit

public class ApiFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly MsSqlContainer _sql = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
        .Build();

    public async Task InitializeAsync()
    {
        await _sql.StartAsync();

        using var scope = Services.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<OrdersDb>();
        await db.Database.MigrateAsync();
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureTestServices(services =>
        {
            services.RemoveAll<DbContextOptions<OrdersDb>>();
            services.AddDbContext<OrdersDb>(o => o.UseSqlServer(_sql.GetConnectionString()));
        });
    }

    public new async Task DisposeAsync() => await _sql.DisposeAsync();
}

A test then calls the API like a real client would:

public class PlaceOrderTests(ApiFactory factory) : IClassFixture<ApiFactory>
{
    [Fact]
    public async Task Placing_an_order_returns_its_location()
    {
        var client = factory.CreateClient();

        var response = await client.PostAsJsonAsync("/orders", new
        {
            customerId = Guid.NewGuid(),
            items = new[] { new { sku = "ABC-1", quantity = 2 } }
        });

        Assert.Equal(HttpStatusCode.Created, response.StatusCode);
        Assert.NotNull(response.Headers.Location);
    }
}

With top-level statements, add public partial class Program { } at the end of Program.cs so the test project can reference it.

#Keeping it fast

• Share one container per test class or the whole test run with fixtures, rather than one per test. Starting SQL Server takes seconds; running a test takes milliseconds.
• Run migrations, not EnsureCreated, so the tests also prove your migrations work.
• Isolate data between tests, by using unique IDs per test or resetting tables between tests. The Respawn library can wipe data quickly while keeping the schema.

#Replace only what's outside your control

Use real infrastructure you own, like the database. Replace third-party APIs with fakes in ConfigureTestServices, or with a stub HTTP server such as WireMock.Net, so tests don't depend on someone else's sandbox being up.

#In the pipeline

Testcontainers needs Docker. Microsoft-hosted Linux agents in Azure Pipelines, such as ubuntu-latest, have it available, so the same tests run in CI with no extra setup.

#Takeaway

Test your API the way it runs in production: start it with WebApplicationFactory, point it at a real database in a container, and fake only the services you don't own. These tests catch the bugs mocks and in-memory providers miss.