Testing EF Core — In-Memory vs SQLite

Introduction

ShopNest repository tests need a database — EF InMemory is fast but lies about SQL behavior; SQLite in-memory runs real SQL and catches more bugs.

After this article you will

• Compare EF InMemory vs SQLite test providers
• Seed test data and test complex LINQ queries
• Choose fresh DbContext per test vs shared
• Test repository and specification implementations
• Reset databases with Respawn

Prerequisites

• Article 55 — Integration Testing
• ShopNest Order Service / API from prior modules

Concept deep-dive

private ShopNestDbContext CreateContext()
{
    var options = new DbContextOptionsBuilder<ShopNestDbContext>()
        .UseSqlite("DataSource=:memory:")
        .Options;
    var ctx = new ShopNestDbContext(options);
    ctx.Database.OpenConnection();
    ctx.Database.EnsureCreated();
    SeedTestProducts(ctx);
    return ctx;
}

[Fact]
public async Task GetByRegion_ReturnsFilteredCustomers()
{
    using var ctx = CreateContext();
    var repo = new CustomerRepository(ctx);
    var spec = new CustomersByRegionSpec("South");
    var result = await repo.ListAsync(spec);
    Assert.All(result, c => Assert.Equal("South", c.Region));
}

Hands-on — ShopNest Repository Layer Testing

1. Repository tests with SQLite :memory: per test.
2. Test Include/specification SQL-heavy queries.
3. Document InMemory failures (Ignore FK) you hit once.
4. Respawn reset for integration test SQL Server.

Common errors & best practices

• InMemory for testing transactions — not supported reliably.
• Shared context mutated across parallel tests — disable parallel or isolate.
• Assuming SQLite = SQL Server — test critical queries on real SQL in CI.

Interview questions

Q: Why not InMemory?
A: Ignores constraints and provider-specific SQL — SQLite closer to real.

Q: EnsureCreated vs Migrate?
A: EnsureCreated for tests; Migrate if testing migration scripts.

Summary

• Prefer SQLite in-memory over EF InMemory for repos
• Seed minimal data per test for clarity
• Test specifications against real LINQ-to-SQL
• Respawn cleans SQL Server between integration tests

Previous: Integration Testing
Next: Performance and Load Testing

FAQ