Entity Framework Core - Unit Testing

1. Introduction and Strategy

Testing data access layers requires balancing execution speed with behavioral accuracy. This documentation outlines strategies for unit testing Entity Framework (EF) Core, ranging from in-memory unit tests to high-fidelity integration tests using real database engines.

1.1 Provider Selection Strategy

Three primary approaches exist for simulating the database during testing:

Strategy	Engine	Pros	Cons	Use Case
SQLite In-Memory	Relational (SQLite)	Fast; Enforces foreign keys & constraints; closer to SQL behavior.	Lacks provider-specific features (e.g., PostgreSQL JSONB, T-SQL specific functions).	Recommended default for standard logic & unit tests.
EF Core In-Memory	Non-Relational	Fastest setup.	Unreliable for relational logic; allows referential integrity violations; behaves differently than SQL.	Discouraged for complex applications.
Testcontainers	Real Engine (Postgres/SQL Server)	100% Accuracy; supports all provider-specific features.	Slower startup; requires Docker environment.	Critical for Integration Testing and provider-specific queries.

2. Prerequisites

The following NuGet packages are standard requirements for a robust test suite:

Testing Framework: xUnit (or NUnit/MSTest)
Assertions: FluentAssertions (For readable, natural language assertions)
Database Providers:
- Microsoft.EntityFrameworkCore.Sqlite
- Microsoft.EntityFrameworkCore.InMemory
Data Seeding: Bogus (For generating realistic mock data)
Integration Testing: Testcontainers.PostgreSql (Optional, for advanced scenarios)

3. Domain Model Definition

The following entities serve as the basis for the examples in this documentation. They demonstrate relationships (One-to-Many), Concurrency Tokens, and Soft Deletes.

using System.ComponentModel.DataAnnotations;

// 1. The Parent Entity
public class Student
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public string RollNumber { get; set; }

    public bool IsDeleted { get; set; } // Soft Delete flag

    [Timestamp]
    public byte[] RowVersion { get; set; } // Optimistic Concurrency Token

    // Navigation Property
    public List<StudentSubject> Subjects { get; set; } = new();
}

// 2. The Child Entity
public class StudentSubject
{
    public int Id { get; set; }
    public string SubjectName { get; set; }

    public int StudentId { get; set; }
    public Student Student { get; set; }

    public List<MarkEntry> Marks { get; set; } = new();
}

// 3. The Grandchild Entity
public class MarkEntry
{
    public int Id { get; set; }
    public string CriteriaType { get; set; } // e.g., "Midterm", "Final"
    public double Score { get; set; }
    public double MaxScore { get; set; } = 100;

    public int StudentSubjectId { get; set; }
}

4. Test Infrastructure Setup

4.1 The Context Factory Pattern

To ensure test isolation, a shared DbContext instance must never be used across different tests. The Context Factory creates a clean, isolated database instance for every test run.

SQLite In-Memory Implementation

SQLite in-memory databases persist only as long as the connection is open. The factory must manage this lifecycle.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Data.Common;

public class TestDbContextFactory : IDisposable
{
    private DbConnection _connection;

    private DbContextOptions<AppDbContext> CreateOptions()
    {
        // "Filename=:memory:" creates a unique in-memory database
        _connection = new SqliteConnection("Filename=:memory:");
        _connection.Open();

        return new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(_connection)
            .Options;
    }

    public AppDbContext CreateContext()
    {
        var context = new AppDbContext(CreateOptions());
        // EnsureCreated() builds the schema (tables) in the memory
        context.Database.EnsureCreated(); 
        return context;
    }

    public void Dispose()
    {
        _connection?.Dispose();
    }
}

5. Automated Data Seeding

Generating large datasets for testing analytics or heavy loads is handled using the Bogus library. This allows for deterministic, realistic data generation.

using Bogus;

public static class DataSeeder
{
    public static List<Student> GenerateStudents(int count)
    {
        // 1. Faker for MarkEntry
        var markFaker = new Faker<MarkEntry>()
            .RuleFor(m => m.Score, f => f.Random.Double(40, 100))
            .RuleFor(m => m.MaxScore, 100);

        // 2. Faker for StudentSubject
        var subjectNames = new[] { "Math", "Physics", "Chemistry", "Biology"

Command Palette