Testing EF Core Applications

Testing data access code is one of the most important yet challenging parts of building reliable applications. In this lesson, we explore why testing EF Core code matters, what strategies are available, and how to choose the right approach for your project.

Why Test Data Access Code?

Data access code is where your application meets the database. Bugs here can corrupt data, lose records, or produce incorrect results. Unlike a UI glitch, a data access bug can have lasting consequences that are difficult to undo.

Testing your EF Core code gives you:

• Confidence in CRUD operations - Verify that entities are created, read, updated, and deleted correctly
• Regression protection - Catch breakages when you modify queries or entity configurations
• Documentation of behavior - Tests describe what your data layer is supposed to do
• Safe refactoring - Change query logic or switch providers knowing your tests will catch mistakes

Unit Tests vs Integration Tests

When testing EF Core code, the distinction between unit and integration tests becomes important.

Unit Tests

Unit tests isolate a single class or method from all external dependencies, including the database. You replace DbContext with mocks or fakes to test business logic in services.

Pros:

• Extremely fast (no database involved)
• Easy to set up and run
• Great for testing service logic that happens to call DbContext

Cons:

• Cannot verify that your LINQ queries translate to correct SQL
• Cannot catch issues with database constraints, indexes, or relationships
• Mocking DbContext is awkward and brittle

Integration Tests

Integration tests exercise your code against a real (or real-like) database. They verify that your entities, DbContext configuration, and queries all work together correctly.

Pros:

• Test the full stack from C# through EF Core to SQL
• Catch configuration issues (missing relationships, wrong column types)
• Verify that LINQ queries actually execute without errors

Cons:

• Slower than unit tests
• Require database setup and teardown
• Can be more complex to write

Recommended Strategy

For most EF Core applications, a combination works best:

1. Integration tests for services and query logic using SQLite in-memory
2. Unit tests for higher-level classes that consume services, using mocked service interfaces
3. A few end-to-end tests against your actual database provider for critical paths

Test Database Options

EF Core offers several options for test databases. Each has trade-offs.

SQLite In-Memory Mode (Recommended)

SQLite in-memory mode creates a lightweight database that lives entirely in memory. It uses a real relational database engine, so your queries execute actual SQL.

csharp
1var connection = new SqliteConnection("Data Source=:memory:");
2connection.Open();
3
4var options = new DbContextOptionsBuilder<AppDbContext>()
5    .UseSqlite(connection)
6    .Options;

Why it is preferred:

• Uses a real SQL engine with proper relational behavior
• Supports foreign keys, constraints, and transactions
• Fast startup and teardown
• Each test can get a fresh, isolated database

EF Core InMemory Provider

The Microsoft.EntityFrameworkCore.InMemory provider stores data in plain .NET collections with no SQL involved.

csharp
1var options = new DbContextOptionsBuilder<AppDbContext>()
2    .UseInMemoryDatabase(databaseName: "TestDb")
3    .Options;

Limitations (why SQLite is preferred):

• Does not enforce foreign key constraints
• Does not validate SQL translation (no SQL is generated)
• Behaves differently from relational databases in subtle ways
• Can give false positives: tests pass but production queries fail
• The EF Core team themselves recommend SQLite over InMemory for testing

Full Database Instance

You can also run tests against a real SQL Server, PostgreSQL, or other production database. This gives maximum fidelity but requires infrastructure setup.

• Best for integration test suites that run in CI/CD pipelines
• Use Docker containers to spin up disposable database instances
• Slower but catches provider-specific behavior

Testing Strategies

Fresh Database Per Test

Each test creates its own database, ensuring complete isolation:

csharp
1// Arrange
2using var connection = new SqliteConnection("Data Source=:memory:");
3connection.Open();
4var options = new DbContextOptionsBuilder<AppDbContext>()
5    .UseSqlite(connection)
6    .Options;
7
8using var context = new AppDbContext(options);
9context.Database.EnsureCreated();
10
11// Act & Assert
12// ... your test code ...
13// Database is automatically destroyed when connection closes

Shared Database with Transaction Rollback

Tests share a database but wrap each test in a transaction that rolls back:

csharp
1using var transaction = context.Database.BeginTransaction();
2// ... perform test operations ...
3transaction.Rollback(); // Undo all changes

Test Fixtures

Create a reusable fixture that sets up the database once and provides fresh contexts:

csharp
1public class DatabaseFixture : IDisposable
2{
3    private readonly SqliteConnection _connection;
4    public DbContextOptions<AppDbContext> Options { get; }
5
6    public DatabaseFixture()
7    {
8        _connection = new SqliteConnection("Data Source=:memory:");
9        _connection.Open();
10        Options = new DbContextOptionsBuilder<AppDbContext>()
11            .UseSqlite(_connection)
12            .Options;
13    }
14
15    public void Dispose() => _connection.Dispose();
16}

A Note on the Repository Pattern

You may encounter the repository pattern in older codebases. It wraps DbContext behind an IRepository<T> interface so that services can be tested with fake implementations instead of a real database.

With modern EF Core, this extra layer is usually unnecessary:

• SQLite in-memory already tests the full pipeline - Your LINQ queries execute as real SQL, constraints are enforced, and you catch translation issues. A fake repository skips all of this.
• The service IS the data access layer - If your service contains the LINQ queries, mocking the repository means you are not testing the actual queries at all.
• Service interfaces achieve the same mockability - If a higher-level class needs to mock a dependency, extract IBookService instead of IRepository<Book>. The benefit is identical without the extra layer.
• DbContext is already a Unit of Work - It tracks changes across multiple entity types and saves them in a single transaction. Wrapping it in another IUnitOfWork is redundant.

The pragmatic approach: inject DbContext directly into services and test with SQLite in-memory. This is what the workshops in this course use.

Key Takeaways

1. Always test data access code - It protects against data corruption and regression
2. Use SQLite in-memory as your primary test database - It is fast, reliable, and uses real SQL
3. Avoid the InMemory provider for most testing - It does not enforce relational behavior
4. Combine unit and integration tests - Unit test service logic, integration test data access
5. Isolate tests - Each test should start with a known database state
6. Skip the repository pattern - SQLite in-memory testing with direct DbContext is simpler and tests the actual queries