EF Core Fundamentals for Testers

Before diving into testing patterns, it's worth grounding yourself in how EF Core works – not because you'll be building applications, but because the concepts directly shape how you test them.

The DbContext

Everything in EF Core flows through the DbContext. It's the bridge between your C# objects and the database. A DbContext subclass defines which entity types map to which tables, how relationships are configured, and how to connect to the database:

// Typical application DbContext
public class StoreDbContext : DbContext
{
    // Each DbSet maps a C# class to a database table
    public DbSet<User> Users { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }

    public StoreDbContext(DbContextOptions<StoreDbContext> options)
        : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Relationships, constraints, seed data configured here
        modelBuilder.Entity<Order>()
            .HasMany(o => o.Items)
            .WithOne(i => i.Order)
            .HasForeignKey(i => i.OrderId);

        modelBuilder.Entity<User>()
            .HasIndex(u => u.Email)
            .IsUnique();
    }
}

Entity Classes

EF Core maps plain C# classes (entities) to database tables. Properties map to columns, and navigation properties define relationships between entities:

public class Order
{
    public int OrderId { get; set; }         // Maps to OrderId INT (primary key)
    public int UserId { get; set; }          // Maps to UserId INT (foreign key)
    public DateTime OrderDate { get; set; }  // Maps to OrderDate DATETIME
    public decimal TotalAmount { get; set; } // Maps to TotalAmount DECIMAL
    public string Status { get; set; }       // Maps to Status VARCHAR

    // Navigation properties – EF Core loads related data
    public User Customer { get; set; }
    public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
}

public class User
{
    public int UserId { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime CreatedDate { get; set; }
    public bool IsActive { get; set; }

    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

Change Tracking and SaveChanges

The biggest conceptual shift from ADO.NET and Dapper is change tracking. EF Core watches every entity it loads. When you modify a property, EF Core notes the change. When you call SaveChangesAsync(), it generates and executes the necessary SQL for all pending changes in a single transaction.

// EF Core tracks this entity after loading it
var order = await context.Orders.FindAsync(orderId);

// Modify the property – EF Core detects this change
order.Status = "Shipped";

// EF Core generates: UPDATE Orders SET Status = 'Shipped' WHERE OrderId = @orderId
await context.SaveChangesAsync();

This is the core of what your application code does, and understanding it explains why testing EF-based applications requires a different approach than testing raw SQL code. There's no explicit SQL to intercept – the SQL is generated from object operations.

// Core EF package
dotnet add package Microsoft.EntityFrameworkCore

// In-memory provider (for unit-style tests)
dotnet add package Microsoft.EntityFrameworkCore.InMemory

// SQLite provider (for more realistic in-process tests)
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

// SQL Server provider (for integration tests against a real database)
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

// Design-time tools (for migrations, optional in test projects)
dotnet add package Microsoft.EntityFrameworkCore.Design

You don't need to know how to build an EF Core application to test one effectively – but knowing how DbContext, entities, and change tracking work gives you the vocabulary to reason about test behavior and debug failures confidently.

Configuring DbContext for Tests

In production, a DbContext is usually configured through dependency injection with a SQL Server connection string. In tests, you need to swap the database provider without changing the application code. EF Core's options-based configuration makes this straightforward.

DbContextOptions – The Configuration Handle

The DbContextOptions<T> object passed to the DbContext constructor controls everything about how it connects and behaves. In tests, you create an options instance pointing at a test-specific provider instead of production SQL Server:

// Helper method to create a test DbContext
private StoreDbContext CreateTestContext(string databaseName = null)
{
    databaseName ??= Guid.NewGuid().ToString();  // Unique DB per test by default

    var options = new DbContextOptionsBuilder<StoreDbContext>()
        .UseInMemoryDatabase(databaseName)        // Swap SQL Server for in-memory
        .Options;

    return new StoreDbContext(options);
}

The key insight: StoreDbContext accepts any DbContextOptions. It doesn't care whether those options point at SQL Server, SQLite, or an in-memory database. Your application code, written against the DbContext API, works identically regardless of the underlying provider.

One Context Per Test

A critical testing practice: never share a DbContext instance across tests. Each test should create its own context. The same DbContext should generally not be used for both writing test data and then reading it to verify – create two separate instances pointing at the same database:

[Test]
public async Task CreateOrder_PersistsToDatabase()
{
    var dbName = Guid.NewGuid().ToString();

    // Context 1: Write test data and exercise application code
    await using (var writeContext = CreateTestContext(dbName))
    {
        var service = new OrderService(writeContext);
        await service.CreateOrder(userId: 1, totalAmount: 150.00m);
    }  // writeContext is disposed – SaveChanges is committed (to in-memory DB)

    // Context 2: Verify results independently
    await using (var readContext = CreateTestContext(dbName))
    {
        var order = await readContext.Orders
            .FirstOrDefaultAsync(o => o.UserId == 1);

        Assert.That(order, Is.Not.Null);
        Assert.That(order.TotalAmount, Is.EqualTo(150.00m));
    }
}

Using a separate read context ensures you're testing that data was actually persisted, not just reading it from the first context's cache. EF Core's first-level cache (the identity map) would return the same object you just wrote if you queried with the same context – giving you false confidence.

Injecting DbContext Through Tests

When your application services receive DbContext through dependency injection, inject the test version the same way:

// Application service – receives DbContext via DI
public class OrderService
{
    private readonly StoreDbContext _context;

    public OrderService(StoreDbContext context)
    {
        _context = context;
    }

    public async Task<int> CreateOrder(int userId, decimal totalAmount)
    {
        var order = new Order
        {
            UserId = userId,
            TotalAmount = totalAmount,
            OrderDate = DateTime.UtcNow,
            Status = "Pending"
        };

        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        return order.OrderId;
    }
}

// Test – passes a test DbContext directly
[Test]
public async Task CreateOrder_AssignsStatus_Pending()
{
    await using var context = CreateTestContext();
    var service = new OrderService(context);  // Inject test context

    var orderId = await service.CreateOrder(userId: 1, totalAmount: 99.99m);

    Assert.That(orderId, Is.GreaterThan(0));

    var saved = await context.Orders.FindAsync(orderId);
    Assert.That(saved.Status, Is.EqualTo("Pending"));
}

The options pattern is EF Core's deliberate design choice for testability. The fact that you can swap providers without touching application code is not incidental – it was planned specifically because the team knew testing was a primary use case.

The In-Memory and SQLite Providers

EF Core ships two providers designed specifically for testing. They're similar in purpose but meaningfully different in behavior. Choosing the right one for each scenario prevents subtle test failures.

The InMemory Provider

The InMemory provider stores all data in memory as .NET objects. It's the fastest option and requires no file system access or process setup:

var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseInMemoryDatabase("TestStore")
    .Options;

Its major limitation: it doesn't enforce database constraints. Foreign keys, unique indexes, and check constraints are all ignored. You can insert an OrderItem with a non-existent OrderId and the InMemory provider won't complain – even though SQL Server would throw a foreign key violation.

// ⚠️ This succeeds in InMemory, but would fail in SQL Server
await using var context = CreateInMemoryContext();

// Insert an order item with OrderId = 999 (no such order exists)
context.OrderItems.Add(new OrderItem
{
    OrderId = 999,  // FK violation – ignored by InMemory
    ProductId = 1,
    Quantity = 2,
    UnitPrice = 29.99m
});

await context.SaveChangesAsync();  // No exception thrown

This matters enormously for test reliability. A test written against InMemory might pass while the same application code fails against a real database.

Use InMemory for: Testing business logic that doesn't depend on database constraints. Validating LINQ query correctness. Testing service layer behavior where the data access layer is intentionally simple.

The SQLite In-Memory Provider

The SQLite in-memory provider runs an actual SQLite database entirely in memory. SQLite enforces most SQL constraints – foreign keys (when enabled), unique indexes, not-null constraints – making tests far more representative of production behavior:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

private StoreDbContext CreateSqliteContext()
{
    // Keep the connection open – SQLite in-memory DB lives as long as the connection
    var connection = new SqliteConnection("Filename=:memory:");
    connection.Open();

    // Enable foreign key enforcement (SQLite doesn't do this by default)
    using var fkCommand = connection.CreateCommand();
    fkCommand.CommandText = "PRAGMA foreign_keys = ON;";
    fkCommand.ExecuteNonQuery();

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

    var context = new StoreDbContext(options);
    context.Database.EnsureCreated();  // Create schema from EF model

    return context;
}

The connection must stay open for the in-memory database to persist. Once the connection closes, the database evaporates. This is actually useful for isolation – wrap the connection in a test class field and dispose it in teardown.

Managing the SQLite Connection Lifecycle

public class OrderTests : IDisposable
{
    private readonly SqliteConnection _connection;
    private readonly StoreDbContext _context;

    public OrderTests()
    {
        _connection = new SqliteConnection("Filename=:memory:");
        _connection.Open();

        // Enable foreign key constraints
        using var cmd = _connection.CreateCommand();
        cmd.CommandText = "PRAGMA foreign_keys = ON;";
        cmd.ExecuteNonQuery();

        var options = new DbContextOptionsBuilder<StoreDbContext>()
            .UseSqlite(_connection)
            .Options;

        _context = new StoreDbContext(options);
        _context.Database.EnsureCreated();
    }

    public void Dispose()
    {
        _context.Dispose();
        _connection.Close();  // Database is destroyed here
        _connection.Dispose();
    }

    [Test]
    public async Task CreateUser_UniqueEmail_Succeeds()
    {
        _context.Users.Add(new User
        {
            Email = "[email protected]",
            FirstName = "Test",
            LastName = "User",
            CreatedDate = DateTime.UtcNow,
            IsActive = true
        });

        await _context.SaveChangesAsync();

        Assert.That(await _context.Users.CountAsync(), Is.EqualTo(1));
    }
}

Prefer SQLite in-memory over the InMemory provider for any test where constraint enforcement matters. The small amount of extra setup is always worth catching constraint violations that InMemory silently ignores.

Seeding Test Data with EF Core

With ADO.NET and Dapper, test data creation meant writing INSERT statements. With EF Core, you can use the same object-oriented API your application uses – and EF Core handles the SQL. This can make test data creation more readable and refactoring-safe.

Direct Entity Addition

The simplest approach: add entities to DbSet properties and call SaveChangesAsync(). EF Core resolves relationships and generates correct INSERT order automatically:

private async Task SeedOrderScenario(StoreDbContext context)
{
    // EF Core handles FK order – you can add in any order
    var user = new User
    {
        Email = $"test-{Guid.NewGuid():N}@example.com",
        FirstName = "Test",
        LastName = "Customer",
        CreatedDate = DateTime.UtcNow,
        IsActive = true
    };

    var product = new Product
    {
        Name = "Widget Pro",
        Price = 49.99m,
        CategoryId = 1,
        StockQuantity = 100,
        IsActive = true
    };

    // Add both – EF Core determines insert order
    context.Users.Add(user);
    context.Products.Add(product);
    await context.SaveChangesAsync();  // user.UserId and product.ProductId are now set

    // Create order using the generated IDs
    var order = new Order
    {
        UserId = user.UserId,       // UserId populated after SaveChanges
        OrderDate = DateTime.UtcNow,
        TotalAmount = 49.99m,
        Status = "Pending"
    };

    context.Orders.Add(order);
    await context.SaveChangesAsync();  // order.OrderId is now set

    // Create order item
    context.OrderItems.Add(new OrderItem
    {
        OrderId = order.OrderId,
        ProductId = product.ProductId,
        Quantity = 1,
        UnitPrice = product.Price
    });

    await context.SaveChangesAsync();
}

Navigation Property Seeding

EF Core's navigation properties allow you to build complete object graphs and add them with a single Add call. EF Core figures out the insert order and sets foreign keys automatically:

private async Task SeedWithNavigationProperties(StoreDbContext context)
{
    var user = new User
    {
        Email = $"nav-{Guid.NewGuid():N}@example.com",
        FirstName = "Nav",
        LastName = "Test",
        CreatedDate = DateTime.UtcNow,
        IsActive = true,
        // Populate navigation property directly
        Orders = new List<Order>
        {
            new Order
            {
                OrderDate = DateTime.UtcNow,
                TotalAmount = 150.00m,
                Status = "Pending",
                Items = new List<OrderItem>
                {
                    new OrderItem
                    {
                        ProductId = 1,  // Assume product 1 exists
                        Quantity = 3,
                        UnitPrice = 50.00m
                    }
                }
            }
        }
    };

    // Single Add – EF Core inserts User, then Order, then OrderItem in correct FK order
    context.Users.Add(user);
    await context.SaveChangesAsync();

    // All IDs are set after SaveChanges
    Console.WriteLine($"User: {user.UserId}, Order: {user.Orders.First().OrderId}");
}

This approach is particularly clean when you're seeding complex scenarios that reflect realistic application state.

EF Core Test Data Factory

A factory class wrapping EF Core operations combines the readability of navigation properties with the reusability of the factory pattern:

public class EfTestDataFactory
{
    private readonly StoreDbContext _context;

    public EfTestDataFactory(StoreDbContext context)
    {
        _context = context;
    }

    public async Task<User> CreateUser(
        string email = null,
        string firstName = "Test",
        bool isActive = true)
    {
        var user = new User
        {
            Email = email ?? $"user-{Guid.NewGuid():N}@example.com",
            FirstName = firstName,
            LastName = "User",
            CreatedDate = DateTime.UtcNow,
            IsActive = isActive
        };

        _context.Users.Add(user);
        await _context.SaveChangesAsync();

        return user;  // Return entity with populated UserId
    }

    public async Task<Order> CreateOrder(
        User user,
        decimal totalAmount = 100.00m,
        string status = "Pending")
    {
        var order = new Order
        {
            UserId = user.UserId,
            OrderDate = DateTime.UtcNow,
            TotalAmount = totalAmount,
            Status = status
        };

        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        return order;
    }

    public async Task<Product> CreateProduct(
        string name = null,
        decimal price = 29.99m,
        int stock = 50)
    {
        var product = new Product
        {
            Name = name ?? $"Product-{Guid.NewGuid().ToString()[..8]}",
            Price = price,
            CategoryId = 1,
            StockQuantity = stock,
            IsActive = true
        };

        _context.Products.Add(product);
        await _context.SaveChangesAsync();

        return product;
    }
}

Tests using the factory read cleanly:

[Test]
public async Task ShipOrder_UpdatesStatusAndSetsShipDate()
{
    await using var context = CreateTestContext();
    var factory = new EfTestDataFactory(context);

    var user = await factory.CreateUser();
    var order = await factory.CreateOrder(user, totalAmount: 75.00m);

    // Act: Exercise application code
    var service = new ShippingService(context);
    await service.ShipOrder(order.OrderId);

    // Assert: Verify using a fresh context
    await using var verifyContext = CreateTestContext(/* same db name */);
    var shipped = await verifyContext.Orders.FindAsync(order.OrderId);

    Assert.That(shipped.Status, Is.EqualTo("Shipped"));
    Assert.That(shipped.ShipmentDate, Is.Not.Null);
}

EF Core's approach to seeding – building object graphs and persisting them – often results in test setup code that more closely mirrors the application's own data creation paths, which makes tests feel more realistic and catch more integration issues.

Testing EF-Based Application Code

The most valuable application of EF Core in testing isn't using it to create test data – it's testing the application code that uses EF Core. When a repository, service, or use case directly depends on DbContext, your test needs to provide a DbContext that works correctly but doesn't require a production database connection.

Testing Repository Classes

Many .NET applications implement the repository pattern – classes that wrap DbContext and provide domain-specific data access methods. Testing these directly with a real database is slow. Testing them with a mock produces false confidence. Testing with SQLite in-memory gives you genuine verification at high speed:

// Application code – the repository under test
public class OrderRepository
{
    private readonly StoreDbContext _context;

    public OrderRepository(StoreDbContext context)
    {
        _context = context;
    }

    public async Task<IReadOnlyList<Order>> GetPendingOrdersForUser(int userId)
    {
        return await _context.Orders
            .Where(o => o.UserId == userId && o.Status == "Pending")
            .OrderByDescending(o => o.OrderDate)
            .ToListAsync();
    }

    public async Task<decimal> GetUserLifetimeValue(int userId)
    {
        return await _context.Orders
            .Where(o => o.UserId == userId && o.Status != "Cancelled")
            .SumAsync(o => o.TotalAmount);
    }
}

// Test class
public class OrderRepositoryTests : IDisposable
{
    private readonly SqliteConnection _connection;
    private readonly StoreDbContext _seedContext;

    public OrderRepositoryTests()
    {
        _connection = new SqliteConnection("Filename=:memory:");
        _connection.Open();

        var options = new DbContextOptionsBuilder<StoreDbContext>()
            .UseSqlite(_connection)
            .Options;

        _seedContext = new StoreDbContext(options);
        _seedContext.Database.EnsureCreated();
    }

    private StoreDbContext CreateContext() =>
        new StoreDbContext(new DbContextOptionsBuilder<StoreDbContext>()
            .UseSqlite(_connection)
            .Options);

    [Test]
    public async Task GetPendingOrders_ReturnsOnlyPendingStatus()
    {
        // Seed: one pending, one shipped order for same user
        _seedContext.Orders.AddRange(
            new Order { UserId = 1, TotalAmount = 50m, Status = "Pending",
                        OrderDate = DateTime.UtcNow.AddDays(-2) },
            new Order { UserId = 1, TotalAmount = 75m, Status = "Shipped",
                        OrderDate = DateTime.UtcNow.AddDays(-5) });
        await _seedContext.SaveChangesAsync();

        await using var testContext = CreateContext();
        var repo = new OrderRepository(testContext);

        var result = await repo.GetPendingOrdersForUser(userId: 1);

        Assert.That(result.Count, Is.EqualTo(1));
        Assert.That(result[0].Status, Is.EqualTo("Pending"));
    }

    [Test]
    public async Task GetLifetimeValue_ExcludesCancelledOrders()
    {
        _seedContext.Orders.AddRange(
            new Order { UserId = 2, TotalAmount = 100m, Status = "Shipped",
                        OrderDate = DateTime.UtcNow.AddDays(-10) },
            new Order { UserId = 2, TotalAmount = 200m, Status = "Cancelled",
                        OrderDate = DateTime.UtcNow.AddDays(-5) });
        await _seedContext.SaveChangesAsync();

        await using var testContext = CreateContext();
        var repo = new OrderRepository(testContext);

        var ltv = await repo.GetUserLifetimeValue(userId: 2);

        Assert.That(ltv, Is.EqualTo(100m));  // Cancelled order excluded
    }

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

Testing with IDbContextFactory

Modern .NET applications often use IDbContextFactory<T> instead of injecting DbContext directly. This avoids lifetime mismatches in web applications. Testing with a factory is equally straightforward:

// Application service using factory pattern
public class ReportService
{
    private readonly IDbContextFactory<StoreDbContext> _contextFactory;

    public ReportService(IDbContextFactory<StoreDbContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }

    public async Task<SalesSummary> GenerateDailySummary(DateTime date)
    {
        await using var context = await _contextFactory.CreateDbContextAsync();

        var orders = await context.Orders
            .Where(o => o.OrderDate.Date == date.Date && o.Status != "Cancelled")
            .ToListAsync();

        return new SalesSummary
        {
            Date = date,
            TotalOrders = orders.Count,
            TotalRevenue = orders.Sum(o => o.TotalAmount)
        };
    }
}

// Test – create a factory that produces test contexts
[Test]
public async Task GenerateDailySummary_CalculatesCorrectTotals()
{
    var options = new DbContextOptionsBuilder<StoreDbContext>()
        .UseInMemoryDatabase(Guid.NewGuid().ToString())
        .Options;

    // Seed test data
    await using (var seedContext = new StoreDbContext(options))
    {
        var today = DateTime.UtcNow.Date;
        seedContext.Orders.AddRange(
            new Order { UserId = 1, OrderDate = today, TotalAmount = 100m, Status = "Shipped" },
            new Order { UserId = 1, OrderDate = today, TotalAmount = 50m, Status = "Pending" },
            new Order { UserId = 2, OrderDate = today, TotalAmount = 200m, Status = "Cancelled" });
        await seedContext.SaveChangesAsync();
    }

    // Create a test factory using the same options
    var factory = new TestDbContextFactory<StoreDbContext>(options);
    var service = new ReportService(factory);

    var summary = await service.GenerateDailySummary(DateTime.UtcNow.Date);

    Assert.That(summary.TotalOrders, Is.EqualTo(2));   // Cancelled excluded
    Assert.That(summary.TotalRevenue, Is.EqualTo(150m));
}

// Simple factory implementation for tests
public class TestDbContextFactory<TContext> : IDbContextFactory<TContext>
    where TContext : DbContext
{
    private readonly DbContextOptions<TContext> _options;

    public TestDbContextFactory(DbContextOptions<TContext> options)
    {
        _options = options;
    }

    public TContext CreateDbContext() =>
        (TContext)Activator.CreateInstance(typeof(TContext), _options)!;

    public Task<TContext> CreateDbContextAsync(CancellationToken ct = default) =>
        Task.FromResult(CreateDbContext());
}

Testing repository and service code through EF Core with in-memory providers gives you genuine coverage of LINQ query logic, relationship loading, and persistence behavior – the aspects of EF-based code that are most likely to break in subtle ways.

Integration Testing with a Real Database

In-memory providers are valuable, but they can't catch everything. Database-specific query behavior, constraint enforcement that differs from SQLite, transaction isolation, and stored procedures all require a real database connection. For these scenarios, EF Core's schema management tools make test database lifecycle management practical.

EnsureCreated and EnsureDeleted

EF Core can create and destroy databases on demand using the database's connection:

public class IntegrationTestBase
{
    protected StoreDbContext Context { get; private set; }

    private static readonly string TestConnectionString =
        "Server=(localdb)\\MSSQLLocalDB;Database=StoreTests;Trusted_Connection=true;";

    [OneTimeSetUp]
    public void CreateDatabase()
    {
        var options = new DbContextOptionsBuilder<StoreDbContext>()
            .UseSqlServer(TestConnectionString)
            .Options;

        // Create schema from EF model (no migrations needed)
        using var setupContext = new StoreDbContext(options);
        setupContext.Database.EnsureDeleted();  // Remove any previous test DB
        setupContext.Database.EnsureCreated();  // Create fresh from EF model
    }

    [SetUp]
    public void SetupContext()
    {
        var options = new DbContextOptionsBuilder<StoreDbContext>()
            .UseSqlServer(TestConnectionString)
            .Options;

        Context = new StoreDbContext(options);
    }

    [TearDown]
    public void CleanupData()
    {
        // Clean data between tests without destroying schema
        Context.OrderItems.RemoveRange(Context.OrderItems);
        Context.Orders.RemoveRange(Context.Orders);
        Context.Products.RemoveRange(Context.Products);
        Context.Users.RemoveRange(Context.Users);
        Context.SaveChanges();

        Context.Dispose();
    }

    [OneTimeTearDown]
    public void DestroyDatabase()
    {
        using var cleanupContext = new StoreDbContext(
            new DbContextOptionsBuilder<StoreDbContext>()
                .UseSqlServer(TestConnectionString).Options);

        cleanupContext.Database.EnsureDeleted();
    }
}

Applying Migrations to Test Databases

If your tests need to validate migration behavior, or your team uses migrations as the source of truth for the schema, run them against a test database instead:

[OneTimeSetUp]
public void ApplyMigrationsToTestDatabase()
{
    var options = new DbContextOptionsBuilder<StoreDbContext>()
        .UseSqlServer(TestConnectionString)
        .Options;

    using var context = new StoreDbContext(options);

    // Apply all pending migrations
    context.Database.Migrate();  // Uses migration history, safe to run repeatedly
}

This approach ensures your tests run against the exact schema your migrations produce – catching cases where the EF model and migrations have drifted out of sync.

Transaction-Based Cleanup with EF Core

For faster test isolation than data truncation, combine EF Core with ADO.NET-style transaction rollback:

public class TransactionalEfTestBase : IDisposable
{
    protected StoreDbContext Context { get; private set; }
    private IDbContextTransaction _transaction;

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<StoreDbContext>()
            .UseSqlServer(TestConnectionString)
            .Options;

        Context = new StoreDbContext(options);

        // Start a transaction – EF Core wraps the ADO.NET transaction
        _transaction = await Context.Database.BeginTransactionAsync();
    }

    [TearDown]
    public async Task Teardown()
    {
        // Roll back everything in this test – no cleanup queries needed
        await _transaction.RollbackAsync();
        _transaction.Dispose();
        Context.Dispose();
    }
}

The IDbContextTransaction wraps the underlying SqlTransaction, so all EF Core operations participate in the same transaction. Rolling it back undoes all changes instantly – the same pattern used with ADO.NET and Dapper, but expressed through EF Core's API.

Real database integration tests are slower but irreplaceable for validating constraint behavior, migration correctness, and database-specific query semantics. Run them in CI but not on every local build – the in-memory and SQLite tests cover the majority of your scenarios much faster.

Common EF Core Testing Pitfalls

EF Core's abstractions introduce some non-obvious failure modes in tests. These pitfalls appear regularly and are worth recognizing before they waste hours of debugging time.

Pitfall: Context Caching Masking Failures

EF Core maintains an identity map – a first-level cache of all loaded entities within a context. Querying for an entity you already loaded returns the cached version, not a fresh database read:

// ❌ This test will always pass, even if SaveChanges doesn't work
[Test]
public async Task UpdateOrder_ChangesStatus()
{
    await using var context = CreateTestContext();

    var order = new Order { UserId = 1, TotalAmount = 50m, Status = "Pending",
                            OrderDate = DateTime.UtcNow };
    context.Orders.Add(order);
    await context.SaveChangesAsync();

    order.Status = "Shipped";
    await context.SaveChangesAsync();

    // Same context! Returns cached object, not a fresh DB read
    var loaded = await context.Orders.FindAsync(order.OrderId);
    Assert.That(loaded.Status, Is.EqualTo("Shipped"));  // Always passes – it's the same object
}

// ✅ Use AsNoTracking() or a new context for genuine verification
[Test]
public async Task UpdateOrder_ChangesStatus_Verified()
{
    var dbName = Guid.NewGuid().ToString();

    await using (var writeContext = CreateTestContext(dbName))
    {
        var order = new Order { UserId = 1, TotalAmount = 50m, Status = "Pending",
                                OrderDate = DateTime.UtcNow };
        writeContext.Orders.Add(order);
        await writeContext.SaveChangesAsync();

        order.Status = "Shipped";
        await writeContext.SaveChangesAsync();
    }

    await using var readContext = CreateTestContext(dbName);
    var loaded = await readContext.Orders
        .AsNoTracking()  // Skip cache, load fresh from database
        .FirstAsync();

    Assert.That(loaded.Status, Is.EqualTo("Shipped"));
}

Pitfall: Forgetting EnsureCreated

The SQLite in-memory database starts empty. Without EnsureCreated(), tables don't exist and every operation throws:

// ❌ Missing EnsureCreated – tables don't exist
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlite("Filename=:memory:")
    .Options;
var context = new StoreDbContext(options);
context.Users.Add(new User { ... });
await context.SaveChangesAsync();  // SqliteException: no such table: Users

// ✅ Always call EnsureCreated after creating a SQLite context
context.Database.EnsureCreated();

Pitfall: InMemory Database Name Collisions

EF Core's InMemory provider shares databases by name across the entire application domain. If two tests use the same database name, they share data – often causing failures in parallel test runs:

// ❌ Hardcoded name – tests share data if run in parallel
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseInMemoryDatabase("TestStore")
    .Options;

// ✅ Unique name per test – guaranteed isolation
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseInMemoryDatabase(Guid.NewGuid().ToString())
    .Options;

Pitfall: Lazy Loading in Tests

If EF Core is configured with lazy loading proxies, navigation properties load automatically when accessed. In tests, this can trigger unexpected database queries after the context is disposed:

// ❌ If lazy loading is enabled, accessing Items after context disposal throws
Order order;
await using (var context = CreateTestContext(dbName))
{
    order = await context.Orders.FindAsync(orderId);
}  // Context disposed

var itemCount = order.Items.Count;  // LazyLoadingException – context is gone

// ✅ Explicitly include navigation properties you'll need
await using (var context = CreateTestContext(dbName))
{
    order = await context.Orders
        .Include(o => o.Items)  // Eager load – no lazy loading needed
        .FirstAsync(o => o.OrderId == orderId);
}

var itemCount = order.Items.Count;  // Safe – Items were loaded before disposal

These pitfalls are less about EF Core being difficult and more about its abstractions behaving differently than direct SQL. Once recognized, they're easy to avoid – and recognizing them early saves significant debugging time.