Why Your EF Core Tests Are Lying to You (And How SQLite In-Memory Fixes It)

Introduction

Picture this: You’ve written a comprehensive test suite for your .NET application. All green. CI passes. You deploy with confidence. Then, at 2 AM, your phone buzzes—production is throwing foreign key constraint violations that your tests never caught.

If you’ve been using Entity Framework Core’s InMemory provider for testing, you’ve likely experienced this nightmare. The truth is, EF Core’s InMemory provider doesn’t behave like a real database—and that gap can silently introduce bugs that only surface in production.

Let’s explore why this happens and how a simple SQLite in-memory connection can save your sanity.

The Problem: InMemory Provider’s Hidden Limitations

When Microsoft introduced Microsoft.EntityFrameworkCore.InMemory, it seemed like the perfect solution for fast, isolated tests. No database setup, no cleanup, blazing fast execution. What’s not to love?

Quite a lot, actually.

What InMemory Doesn’t Do

Here’s the uncomfortable truth about EF Core’s InMemory provider:

A Real-World Example

Consider this innocent-looking test:

[Fact]
public void CreateOrder_ShouldSucceed()
{
    // Arrange
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseInMemoryDatabase("TestDb")
        .Options;

    using var context = new AppDbContext(options);
    
    // Act - Creating an order for a user that doesn't exist!
    var order = new Order 
    { 
        UserId = 999,  // This user doesn't exist
        Total = 100.00m 
    };
    
    context.Orders.Add(order);
    context.SaveChanges();  // This succeeds with InMemory!
    
    // Assert
    Assert.Equal(1, context.Orders.Count());  // ✅ Test passes!
}

This test passes with flying colors. But in production, with a real database? You’ll get:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint 
"FK_Orders_Users_UserId". The conflict occurred in database "Production", 
table "dbo.Users", column 'Id'.

Your tests gave you false confidence. The InMemory provider happily accepted an order referencing a non-existent user because it simply doesn’t check foreign key constraints.

The Solution: SQLite In-Memory Database

SQLite offers an elegant middle ground: real relational database behavior with in-memory speed. The magic happens with a single connection string:

var connection = new SqliteConnection("Data Source=:memory:");

This creates a private, in-memory database that:

• Enforces foreign key constraints (when enabled)
• Validates unique constraints
• Supports transactions
• Handles cascade deletes properly
• Runs entirely in memory (no disk I/O)
• Disappears when the connection closes (automatic cleanup)

The Critical Connection Lifecycle

Here’s the catch with SQLite in-memory databases: the database only exists while the connection is open. Close the connection, and everything vanishes. This is actually perfect for testing—guaranteed isolation between tests—but you need to manage the connection carefully.

public class SqliteTestFixture : IDisposable
{
    private readonly SqliteConnection _connection;
    public AppDbContext Context { get; }

    public SqliteTestFixture()
    {
        // Create and OPEN the connection - this creates the database
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();  // Critical! Database exists only while open

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

        Context = new AppDbContext(options);
        Context.Database.EnsureCreated();  // Create schema
    }

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

Foreign Keys Are Enabled by Default

Good news: when using Microsoft.Data.Sqlite (which EF Core uses), foreign key enforcement is enabled by default. The bundled e_sqlite3 native library is compiled with SQLITE_DEFAULT_FOREIGN_KEYS=1, so you don’t need to manually run PRAGMA foreign_keys = ON.

This means your tests will automatically catch referential integrity violations—no extra configuration needed.

Complete Implementation: Building a Proper Test Base

Here’s a production-ready implementation for xUnit:

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

public abstract class SqliteTestBase<TContext> : IDisposable 
    where TContext : DbContext
{
    private readonly SqliteConnection _connection;
    protected TContext Db { get; }

    protected SqliteTestBase()
    {
        // Create in-memory SQLite connection
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();

        // Build DbContext with SQLite provider
        // Note: Foreign keys are enabled by default in Microsoft.Data.Sqlite
        var options = new DbContextOptionsBuilder<TContext>()
            .UseSqlite(_connection)
            .EnableSensitiveDataLogging()  // Helpful for debugging
            .EnableDetailedErrors()
            .Options;

        Db = CreateContext(options);
        Db.Database.EnsureCreated();

        // Optional: Seed test data
        Seed(Db);
        Db.SaveChanges();
    }

    protected abstract TContext CreateContext(DbContextOptions<TContext> options);
    
    protected virtual void Seed(TContext context) { }

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

Using the Test Base

public class OrderTests : SqliteTestBase<AppDbContext>
{
    protected override AppDbContext CreateContext(
        DbContextOptions<AppDbContext> options) => new(options);

    protected override void Seed(AppDbContext context)
    {
        // Seed required test data
        context.Users.Add(new User { Id = 1, Name = "John Doe" });
    }

    [Fact]
    public void CreateOrder_WithValidUser_Succeeds()
    {
        // Arrange & Act
        Db.Orders.Add(new Order { UserId = 1, Total = 100.00m });
        Db.SaveChanges();

        // Assert
        Assert.Equal(1, Db.Orders.Count());
    }

    [Fact]
    public void CreateOrder_WithInvalidUser_ThrowsException()
    {
        // Arrange
        var order = new Order { UserId = 999, Total = 100.00m };  // User doesn't exist
        Db.Orders.Add(order);

        // Act & Assert - Now this correctly fails!
        Assert.Throws<DbUpdateException>(() => Db.SaveChanges());
    }

    [Fact]
    public void DeleteUser_WithOrders_CascadeDeletes()
    {
        // Arrange
        Db.Orders.Add(new Order { UserId = 1, Total = 50.00m });
        Db.SaveChanges();

        // Act
        var user = Db.Users.Find(1)!;
        Db.Users.Remove(user);
        Db.SaveChanges();

        // Assert - Order should be cascade deleted
        Assert.Empty(Db.Orders);
    }
}

Performance Considerations

You might wonder: “Does SQLite add significant overhead compared to InMemory?”

Here’s the reality based on typical benchmarks:

Yes, SQLite in-memory is roughly 8-10x slower than InMemory for simple operations. But consider:

1. It’s still blazing fast - milliseconds, not seconds
2. It catches real bugs - saving hours of production debugging
3. It’s 23x faster than disk-based SQLite - you get real behavior without real disk I/O

The small performance cost is a worthwhile trade-off for tests you can actually trust.

The Easy Way: EfCore.TestBed

If you want all of these benefits without building the infrastructure yourself, check out EfCore.TestBed—a NuGet package that wraps all of this functionality in a clean, easy-to-use API.

Installation

dotnet add package EfCore.TestBed

NuGet: https://www.nuget.org/packages/EfCore.TestBed/

Quick Start

Option 1: Inherit from EfTestBase (Recommended)

using EfCore.TestBed.Core;

public class OrderTests : EfTestBase<AppDbContext>
{
    protected override void Seed(AppDbContext context)
    {
        context.Users.Add(new User { Id = 1, Name = "John" });
    }

    [Fact]
    public void CreateOrder_WithValidUser_Succeeds()
    {
        // Db is automatically configured and seeded!
        Db.Orders.Add(new Order { UserId = 1 });
        Db.SaveChanges();

        Assert.Equal(1, Db.Orders.Count());
    }

    [Fact]
    public void CreateOrder_WithInvalidUser_Fails()
    {
        Db.Orders.Add(new Order { UserId = 999 });

        // Real FK validation!
        Assert.Throws<DbUpdateException>(() => Db.SaveChanges());
    }
}

Option 2: Factory Method for Quick Tests

using EfCore.TestBed.Factory;

[Fact]
public void QuickTest()
{
    using var db = TestDb.Create<AppDbContext>(ctx =>
    {
        ctx.Users.Add(new User { Name = "Test" });
    });

    Assert.Equal(1, db.Context.Users.Count());
}

Option 3: One-Liner for Super Quick Tests

[Fact]
public void SuperQuickTest()
{
    using var db = TestDb.Quick<AppDbContext>();
    db.Users.Add(new User { Name = "John" });
    db.SaveChanges();
    
    Assert.Single(db.Users);
}

Bonus Features

EfCore.TestBed includes additional utilities that make testing even easier:

Fluent Assertions:

using EfCore.TestBed.Extensions;

Db.ShouldHave<User>(u => u.Name == "John");
Db.ShouldNotHave<Order>(o => o.Status == "Cancelled");
Db.ShouldHaveCount<User>(5);
Db.ShouldSaveSuccessfully();
Db.ShouldFailOnSave<DbUpdateException>();

Easy Seeding:

using EfCore.TestBed.Seeding;

// Seed with factory pattern
var products = Db.SeedMany(10, i => new Product 
{ 
    Name = $"Product {i}",
    Price = 9.99m * i
});

// Fluent seeding
Db.Seed()
  .Add(new User { Name = "John" })
  .Add(5, i => new Order { Total = i * 10 })
  .Build();

Transaction Support:

using EfCore.TestBed.Transactions;

// Auto-rollback after test
Db.InRollbackTransaction(ctx =>
{
    ctx.Users.Add(new User { Name = "Temp" });
    ctx.SaveChanges();
    // Automatically rolled back!
});

When to Use What

Here’s a decision guide for choosing your testing approach:

Key Takeaways

1. EF Core’s InMemory provider doesn’t validate constraints - Tests can pass while hiding real bugs.

2. SQLite in-memory provides real database behavior - Foreign keys, unique constraints, transactions, and cascade deletes all work correctly.

3. The connection string is simple: "Data Source=:memory:"

4. Keep the connection open - The database only exists while the connection is alive.

5. Foreign keys are enabled by default - When using Microsoft.Data.Sqlite, no PRAGMA needed.

6. Consider EfCore.TestBed - It handles all the boilerplate and provides useful testing utilities.

Resources

• EfCore.TestBed GitHub: https://github.com/ShadyNagy/EfCore.TestBed
• EfCore.TestBed NuGet: https://www.nuget.org/packages/EfCore.TestBed/
• Microsoft SQLite Provider Docs: EF Core SQLite Provider
• SQLite PRAGMA Reference: SQLite Foreign Keys

Conclusion

The next time you’re setting up tests for an EF Core application, resist the temptation to reach for the InMemory provider. That extra millisecond of test execution time is a small price to pay for tests that actually catch the bugs you care about.

Your future self—the one who doesn’t get paged at 2 AM—will thank you.

Feedback and Questions

Your insights drive us! For any questions, feedback, or thoughts, feel free to connect:

1. Email: shady@shadynagy.com
2. Twitter: @ShadyNagy_
3. LinkedIn: Shady Nagy
4. GitHub: ShadyNagy

If you found this guide beneficial, don’t hesitate to share it with your network. Until the next guide, happy coding!