Never Use UseInMemoryDatabase in ASP.NET Tests

A test suite can pass completely against UseInMemoryDatabase and still break the moment the same code runs against real Postgres. Green checkmarks, CI happy, PR merged — and then a query that relied on a foreign key constraint does the wrong thing in production, because nothing in the test run had a foreign key to enforce.

That's the one rule in ASP.NET testing I don't compromise on: never test against UseInMemoryDatabase. It's not a relational database. No constraints, no transactions, and its LINQ translation doesn't match what EF Core actually generates against Postgres or SQL Server. You get green tests that lie to you about what happens in production.

Why the in-memory provider fails you

EF Core's in-memory provider was built for quick prototyping, not for verifying behavior. It stores your entities in a plain in-memory collection and evaluates LINQ against that collection using regular .NET semantics. That sounds convenient until you remember that a real database doesn't work that way.

A unique index violation, a cascade delete, a check constraint, a NOT NULL column — none of that exists in the in-memory provider. Your test can happily insert two rows that would collide on a unique constraint in Postgres, and it'll pass. A query that translates to a specific SQL expression against Npgsql might translate completely differently against the in-memory LINQ provider, or not translate at all. A bug in your query gets masked because the fake provider is more forgiving than the real one.

Transactions are the other big gap. If your code relies on transactional rollback, or on isolation between concurrent operations, the in-memory provider gives you none of that. So you end up testing a database that behaves nothing like the one your users hit.

What to use instead

Testcontainers. Spin up a real Postgres instance in a container for the test run, and point EF Core at it like you would in production.

// Real Postgres per test run — not an in-memory provider
public sealed class ApiFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly PostgreSqlContainer _db = new PostgreSqlBuilder()
        .WithImage("postgres:17-alpine").Build();

    protected override void ConfigureWebHost(IWebHostBuilder builder)
        => builder.ConfigureTestServices(services =>
        {
            services.RemoveAll<DbContextOptions<AppDbContext>>();
            services.AddDbContext<AppDbContext>(o => o.UseNpgsql(_db.GetConnectionString()));
            services.AddSingleton<TimeProvider>(new FakeTimeProvider());
        });

    public async Task InitializeAsync() { await _db.StartAsync(); /* apply migrations */ }
    public new async Task DisposeAsync() => await _db.DisposeAsync();
}

ApiFactory boots the real ASP.NET pipeline through WebApplicationFactory<Program>, but swaps the database connection to point at a containerized Postgres instead of whatever's configured for the app normally. Same constraints, same transaction behavior, same SQL your production database will actually run.

The pyramid, ASP.NET-flavored

Once you accept that integration tests need a real database, the shape of your test suite follows from that.

Integration tests (~60%) — this is where most of the value is. WebApplicationFactory<Program> boots the real pipeline in memory, but against a real database in a container. You're exercising the actual HTTP pipeline, the actual EF Core mappings, the actual constraints. Highest value per line, and it's basically the same idea as Laravel feature tests, if you're coming from that world.

Unit tests (~35%) — domain rules, calculators, validators, mappers. No I/O, so they're fast. This is where you test the logic that doesn't need a database or an HTTP request to verify.

E2E (~5%) — Playwright, reserved for the handful of flows that must never break. Login, checkout, whatever your equivalent is. E2E tests are slow and brittle by nature, so you don't want your whole suite living here.

Here's what a couple of integration tests look like against ApiFactory:

public sealed class PostApiTests(ApiFactory factory) : IClassFixture<ApiFactory>
{
    [Fact]
    public async Task Creates_a_post_for_an_authenticated_user()
    {
        var client = factory.CreateAuthenticatedClient(userId: TestUsers.Author);

        var response = await client.PostAsJsonAsync("/api/posts",
            new { title = "Hello", body = "World" });

        response.StatusCode.ShouldBe(HttpStatusCode.Created);

        var dto = await response.Content.ReadFromJsonAsync<PostDto>();
        dto!.Title.ShouldBe("Hello");
    }

    [Fact]
    public async Task Forbids_editing_someone_elses_post()
    {
        var post = await factory.SeedAsync(new PostBuilder().WithAuthor(TestUsers.Other));
        var client = factory.CreateAuthenticatedClient(userId: TestUsers.Author);

        var response = await client.PutAsJsonAsync($"/api/posts/{post.Id}", new { title = "Hacked" });

        response.StatusCode.ShouldBe(HttpStatusCode.Forbidden);
    }
}

Notice what these tests actually assert on: HTTP status codes and response bodies. Not internal method calls, not whether some service was invoked with the right arguments. You go through the HTTP boundary the same way a real client would. If you start asserting on internal call sequences instead, your tests become coupled to implementation details, and refactoring turns into a chore even when behavior hasn't changed.

The supporting rules

A few things make this pyramid work in practice, beyond just using Testcontainers.

Test data should come from builders, not raw object initializers scattered through every test file. new PostBuilder().WithAuthor(TestUsers.Other) in the second test above is the ASP.NET equivalent of a Laravel factory: a readable way to construct exactly the entity state a test needs.

Isolation matters just as much. Wrap each test in a transaction and roll it back, or reset the database between runs with something like Respawn. What you don't want is shared mutable seed data that one test can quietly corrupt for the next one, because that's how you end up with tests that pass individually and fail when run together, one of the more annoying categories of flakiness to debug.

Time-dependent code needs a FakeTimeProvider instead of assertions built around DateTime.UtcNow. If your code branches on expiry windows or scheduling, you want that time to be a value you control in the test, not whatever the clock happens to say when CI runs. ApiFactory above registers FakeTimeProvider as a singleton for exactly this reason.

And outbound HTTP calls should be faked with a stub HttpMessageHandler or WireMock.NET. A test suite that hits the real internet is a flaky suite, full stop. Any external call, whether a third-party API or a webhook, should be intercepted at the HTTP client level so your tests aren't at the mercy of some other service's uptime.

The toolchain

xUnit is the default runner choice, though NUnit and MSTest work fine if that's what a codebase already uses. Shouldly or FluentAssertions handle assertions. Worth checking FluentAssertions' license terms before using it commercially, since those changed. Mocking is NSubstitute or Moq, take your pick.

None of these choices matter as much as the database decision. You can swap test runners and assertion libraries without much consequence. But if your integration tests run against UseInMemoryDatabase, you don't actually know what your code does against Postgres — you know what it does against a LINQ-over-a-list emulation of a database, which is a different thing that happens to share an interface.

The fix isn't complicated. Testcontainers spins up and tears down fast enough that it doesn't meaningfully slow your suite down, and the confidence it buys you is the whole point of writing integration tests in the first place. If your tests aren't telling you the truth about production, they're not saving you the time debugging in production later — they're just moving the debugging to a worse moment.