Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 41 ยท Testing and Patterns

Testing EF Core Code

Choose how to test EF Core code: why the in-memory provider misleads, SQLite in-memory, real database containers, test isolation, and unit tests with fakes, with runnable stand-ins.

Data access code is hard to test because the interesting behavior lives in the database: constraints, SQL translation, transactions, collation. A test that skips the database can pass while production fails. This lesson compares the testing options EF Core gives you, explains what each one proves, and shows how to keep tests fast and isolated.

EF Core code is read-only on this site. The runnable blocks are stand-ins that show the testing ideas (fakes, test data builders, isolation) in plain C#.

What are you testing?

Start by separating two questions:

  1. Does my business logic work? Pricing rules, validation, state changes. These need no database at all if you structure the code well.
  2. Does my data access work? Does this LINQ query translate? Does the unique index reject duplicates? Does cascade delete behave? These need a real database engine.

Mixing the two (one big test that boots EF Core to check a discount calculation) makes tests slow and fragile. Prefer many fast logic tests and a smaller set of data-access tests.

Option 1: the InMemory provider (use with care)

Microsoft.EntityFrameworkCore.InMemory is a provider that stores entities in a dictionary:

C#
var options = new DbContextOptionsBuilder<ShopContext>()
    .UseInMemoryDatabase("test-" + Guid.NewGuid())
    .Options;

await using var db = new ShopContext(options);
db.Products.Add(new Product { Name = "Pen", Price = 2m });
await db.SaveChangesAsync();

It is fast and needs no setup, but it is not a relational database, and the EF Core team explicitly discourages it for testing:

  • It does not enforce foreign keys, unique indexes, NOT NULL or string lengths.
  • It has no real transactions (BeginTransaction is ignored by default).
  • It does not translate your LINQ to SQL, so a query that the real provider cannot translate (a client-evaluation trap) works fine in the test and throws in production.
  • String comparison is case-sensitive, unlike many SQL Server collations.
  • It does not run ExecuteUpdate, FromSql or concurrency-token checks the same way.

It is acceptable for a quick smoke test of code that only adds and reads simple entities. For anything else, use a relational provider.

Option 2: SQLite in-memory

SQLite runs inside your test process, enforces foreign keys and unique constraints, and translates LINQ to real SQL:

C#
// Keep the connection open: an in-memory SQLite database lives as long as its connection.
var connection = new SqliteConnection("DataSource=:memory:");
await connection.OpenAsync();

var options = new DbContextOptionsBuilder<ShopContext>()
    .UseSqlite(connection)
    .Options;

await using (var db = new ShopContext(options))
{
    await db.Database.EnsureCreatedAsync();      // or MigrateAsync()
}

It is a good middle ground, but remember that SQLite is a different database. It lacks some types (decimal is stored as text or real), has different collation, no rowversion, limited ALTER TABLE, and its SQL differs from SQL Server or PostgreSQL. Features specific to your production provider cannot be tested here.

The most trustworthy test uses the same engine as production. With containers this is practical on a laptop and in CI: libraries such as Testcontainers start a disposable SQL Server or PostgreSQL container for the test run.

C#
public class DatabaseFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _pg = new PostgreSqlBuilder().Build();
    public string ConnectionString => _pg.GetConnectionString();

    public async Task InitializeAsync()
    {
        await _pg.StartAsync();
        await using var db = CreateContext();
        await db.Database.MigrateAsync();        // also tests your migrations
    }

    public ShopContext CreateContext() =>
        new(new DbContextOptionsBuilder<ShopContext>().UseNpgsql(ConnectionString).Options);

    public Task DisposeAsync() => _pg.DisposeAsync().AsTask();
}

Starting a container takes seconds, so share one per test run (an xUnit collection fixture) and give each test a clean state instead of a new container.

Option Speed Enforces constraints Same SQL as production Use for
InMemory fastest no no trivial smoke tests only
SQLite in-memory fast yes no most query and mapping tests
Real engine in a container slower yes yes critical queries, migrations, provider features

Keeping tests isolated

Tests that share data interfere with each other. Common approaches:

  • New database per test class, created from migrations: simple, slower.
  • Transaction per test, rolled back at the end: the test opens a transaction, runs, and never commits. Fast, but the code under test must not commit its own transaction.
  • Respawn-style cleanup: delete all rows (in dependency order) between tests with a library or a generated script.
C# Stand-in: transaction rollback isolation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Writing a good data-access test

A good test arranges data, acts through the real context, and asserts using a fresh context so you read from the database rather than the change tracker's cache:

C#
[Fact]
public async Task Cheapest_returns_lowest_priced_active_product()
{
    // Arrange
    await using (var arrange = fixture.CreateContext())
    {
        arrange.Products.AddRange(
            new Product { Name = "A", Price = 9m, IsActive = true },
            new Product { Name = "B", Price = 3m, IsActive = false },
            new Product { Name = "C", Price = 5m, IsActive = true });
        await arrange.SaveChangesAsync();
    }

    // Act
    await using var act = fixture.CreateContext();
    var result = await new ProductQueries(act).CheapestActiveAsync();

    // Assert
    Assert.Equal("C", result!.Name);
}

Using separate contexts for arrange, act and assert avoids a classic false positive: a query that returns the right entity only because it was already tracked in the same context.

Unit testing logic without EF Core

Keep logic out of the data layer so it can be tested with plain objects:

C# Stand-in: logic tested without a database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

When a service needs data, hide the context behind a small interface and use a hand-written fake in logic tests (more in the repository and unit of work debate). Use the real database only for the interface's actual implementation.

Common pitfalls

  • Testing the mapping only through the in-memory provider, which hides constraint and translation bugs.
  • Sharing one context across arrange and assert, so the tracker hides missing data.
  • Forgetting to dispose contexts and connections (a leaked SQLite connection destroys the in-memory database early, or never).
  • Asserting on generated SQL text, which is brittle; assert on results instead. Use logging only to investigate.
  • Order-dependent tests relying on identity values; assert on content, not on Id == 1.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the EF Core team discourage the InMemory provider for tests?

    Queries and constraints behave differently, so tests can pass while production fails.
  2. 2. What must you do to keep an in-memory SQLite database alive during a test?

    An in-memory SQLite database exists only as long as its connection stays open.
  3. 3. Why use a fresh DbContext for the assert step?

    A reused context may return already-tracked instances, hiding data that was never saved.
  4. 4. Which option gives the most faithful test of provider-specific SQL and migrations?

    Only the real engine runs your exact SQL, collation, types and migrations.

Summary

  • Test business rules without a database; test data access against a real relational engine.
  • Avoid the InMemory provider for anything that depends on constraints, SQL translation or transactions.
  • SQLite in-memory is a fast compromise; containers give production fidelity.
  • Isolate tests with rollbacks, per-class databases or cleanup, and assert through a fresh context.

Next: DbContext in ASP.NET Core and dependency injection