Testing code that uses EF Core
Tests for code that uses EF Core need a database that behaves like the real one. This lesson compares the in-memory provider, SQLite in memory and a real SQL Server in a container with Testcontainers, and shows the repository question honestly.
- SQL Server in DockerMost realistic; use Testcontainers
- SQLite in memoryFast and relational; different SQL
- In-memory providerFastest; not a real database
- Fakes for servicesTest business rules without a database
Why the choice matters
LINQ that works against one provider can fail or behave differently against another. The in-memory provider does not check constraints, run transactions or use SQL at all, so a test can pass while the real query fails or returns different results (for example, case-sensitive string comparison).
Microsoft's guidance is to test against the same database system you use in production whenever possible.
The options
- Real SQL Server in Docker with Testcontainers: most realistic; a few seconds to start once per test run.
- SQLite in memory: fast and relational, but SQL differs from SQL Server (no rowversion, different functions).
- EF Core in-memory provider: fastest, but not a database; Microsoft discourages it for testing.
- Repository or service interfaces with fakes: test business logic without a database, then test queries separately.
SQL Server with Testcontainers and xUnit
public class DbFixture : IAsyncLifetime
{
private readonly MsSqlContainer _sql = new MsSqlBuilder().Build();
public string ConnectionString => _sql.GetConnectionString();
public async Task InitializeAsync()
{
await _sql.StartAsync();
await using var db = CreateContext();
await db.Database.MigrateAsync();
}
public Task DisposeAsync() => _sql.DisposeAsync().AsTask();
public ShopContext CreateContext() =>
new(new DbContextOptionsBuilder<ShopContext>().UseSqlServer(ConnectionString).Options);
}
public class OrderTests(DbFixture fx) : IClassFixture<DbFixture>
{
[Fact]
public async Task Placing_an_order_reduces_stock()
{
await using var db = fx.CreateContext();
await using var tx = await db.Database.BeginTransactionAsync(); // rolled back at the end
var service = new OrderService(db);
await service.PlaceAsync(customerId: 1, productId: 1, quantity: 2);
db.ChangeTracker.Clear();
Assert.Equal(8, (await db.Products.FindAsync(1))!.Stock);
}
}Tips
- Wrap each test in a transaction and roll back, or reset data with a tool such as Respawn.
- Call ChangeTracker.Clear() before asserting so you read from the database, not from memory.
- Run migrations in the fixture so tests also check that migrations work.
- Keep pure business rules in classes that do not need EF Core; they are the cheapest to test.
Interview question
Why is the EF Core in-memory provider a poor choice for testing?
It is not a relational database: it does not enforce foreign keys or unique constraints, ignores transactions, does not run SQL and can behave differently, for example in string comparisons. Tests can pass while the same code fails against SQL Server. A real database in a container, or at least SQLite, catches these problems.
Want the full details? Read the official Microsoft documentation for this topic.