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:

---

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" };
        var subjectFaker = new Faker<StudentSubject>()
            .RuleFor(s => s.SubjectName, f => f.PickRandom(subjectNames))
            .RuleFor(s => s.Marks, f => 
            {
                var criteria = new[] { "Midterm", "Final", "Assignment" };
                var marks = new List<MarkEntry>();
                foreach (var c in criteria)
                {
                    var mark = markFaker.Generate();
                    mark.CriteriaType = c;
                    marks.Add(mark);
                }
                return marks;
            });

        // 3. Faker for Student
        var studentFaker = new Faker<Student>()
            .RuleFor(s => s.FullName, f => f.Name.FullName())
            .RuleFor(s => s.RollNumber, f => f.Random.AlphaNumeric(6).ToUpper())
            .RuleFor(s => s.Subjects, f => 
            {
                // Each student gets 3-5 subjects automatically
                return subjectFaker.Generate(f.Random.Number(3, 5));
            });

        return studentFaker.Generate(count);
    }
}

---

6. Unit Test Scenarios

6.1 Standard CRUD & Complex Query

Verifies that data integrity holds through nested relationships.

[Fact]
public async Task StudentStatistics_ShouldCalculateCorrectAverage()
{
    // Arrange
    using var factory = new TestDbContextFactory();
    using var context = factory.CreateContext();

    // Seed 50 students (generates ~500+ related rows)
    var students = DataSeeder.GenerateStudents(50);
    context.Students.AddRange(students);
    await context.SaveChangesAsync();

    var targetId = students.First().Id;

    // Act: Fetch complex nested data
    using var readContext = factory.CreateContext(); // Simulate new request
    var student = await readContext.Students
        .Include(s => s.Subjects)
        .ThenInclude(sub => sub.Marks)
        .FirstOrDefaultAsync(s => s.Id == targetId);

    // Logic: Calculate average score across all subjects
    var allMarks = student.Subjects.SelectMany(s => s.Marks).ToList();
    var average = allMarks.Average(m => m.Score);

    // Assert
    student.Should().NotBeNull();
    student.Subjects.Should().HaveCountGreaterThan(2);
    average.Should().BeInRange(40, 100);
}

6.2 Concurrency Handling (Optimistic Locking)

Tests that the system correctly prevents lost updates when two users edit the same record.

[Fact]
public async Task UpdateStudent_ShouldThrowConcurrencyException_OnConflict()
{
    // Arrange
    using var factory = new TestDbContextFactory();

    // Setup initial data
    using (var context = factory.CreateContext())
    {
        context.Students.Add(new Student { Id = 1, FullName = "Original" });
        await context.SaveChangesAsync();
    }

    // Simulate User A and User B retrieving the same record
    using var contextA = factory.CreateContext();
    using var contextB = factory.CreateContext();

    var studentA = await contextA.Students.FindAsync(1);
    var studentB = await contextB.Students.FindAsync(1);

    // Act
    studentA.FullName = "Updated by A";
    await contextA.SaveChangesAsync(); // User A commits first -> RowVersion changes

    studentB.FullName = "Updated by B";

    // Assert
    // User B tries to commit with stale RowVersion
    await Assert.ThrowsAsync<DbUpdateConcurrencyException>(async () => 
        await contextB.SaveChangesAsync());
}

6.3 Transaction Rollback (Data Integrity)

Ensures that if an error occurs mid-operation, no partial data is persisted.

[Fact]
public async Task Registration_ShouldRollback_WhenErrorOccurs()
{
    // Arrange
    using var factory = new TestDbContextFactory();
    using var context = factory.CreateContext();

    // Act
    using var transaction = await context.Database.BeginTransactionAsync();
    try
    {
        // Step 1: Add Student
        context.Students.Add(new Student { FullName = "Transaction Test" });
        await context.SaveChangesAsync();

        // Step 2: Simulate Failure
        throw new InvalidOperationException("Simulated Failure");

        // await transaction.CommitAsync(); // Unreachable
    }
    catch
    {
        // Service layer handles exception, transaction is disposed (rolled back)
    }

    // Assert
    var count = await context.Students.CountAsync();
    count.Should().Be(0, "Transaction should have rolled back changes");
}

---

7. Advanced: Real Database Integration (PostgreSQL)

When accuracy is paramount (e.g., using specific Postgres features) or to resolve the "creation performance" issue, the Transaction Rollback Pattern is used with a real database.

7.1 Performance Strategy

Creating tables (EnsureCreated) is slow on real databases. To mitigate this:

1. Initialize the database once (globally).

2. Wrap each test in a transaction.

3. Rollback the transaction at the end of the test.

   This leaves the database empty and ready for the next test without expensive table drops/creates.

7.2 Integration Test Base Class

public abstract class PostgresTestBase : IDisposable
{
    protected readonly AppDbContext Context;
    private readonly IDbContextTransaction _transaction;

    protected PostgresTestBase()
    {
        // Configure Connection to Local DB or Testcontainer
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseNpgsql("Host=localhost;Database=IntegrationTestDb;Username=postgres;Password=password")
            .Options;

        Context = new AppDbContext(options);

        // Build Schema ONCE (Check if exists first to save time)
        Context.Database.EnsureCreated();

        // Start Transaction for the specific test
        _transaction = Context.Database.BeginTransaction();
    }

    public void Dispose()
    {
        // Discard all changes made during the test
        _transaction.Rollback();
        _transaction.Dispose();
        Context.Dispose();
    }
}

7.3 Usage

The test inherits from the base class. It runs against real PostgreSQL but executes instantly because of the rollback mechanism.

public class StudentRepositoryTests : PostgresTestBase
{
    [Fact]
    public async Task SaveStudent_PersistsToPostgres()
    {
        // Arrange
        var student = new Student { FullName = "Postgres User", RollNumber = "P100" };

        // Act
        Context.Students.Add(student);
        await Context.SaveChangesAsync();

        // Assert
        var dbStudent = await Context.Students.FirstAsync();
        dbStudent.FullName.Should().Be("Postgres User");

        // Dispose() is called automatically here -> Rolling back data
    }
}

---

8. Best Practices Summary