SQLite

Your EF Core unit tests are passing beautifully. You’re using the in-memory provider, tests run fast, and everything looks green. Then you deploy to production and suddenly encounter foreign key violations, constraint errors, and mysterious data consistency issues that your tests never caught.

The EF Core in-memory provider is a testing trap. It provides false confidence by behaving differently from real databases in critical ways.

The In-Memory Provider Problem

The in-memory provider seems perfect for testing:

[Test]
public async Task CreateOrder_ShouldSaveSuccessfully()
{
    // Arrange
    var options = new DbContextOptionsBuilder<OrderContext>()
        .UseInMemoryDatabase(databaseName: "TestDatabase")
        .Options;
        
    using var context = new OrderContext(options);
    var order = new Order { CustomerId = 999, Total = 100.00m }; // Invalid customer ID
    
    // Act
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    
    // Assert
    var savedOrder = await context.Orders.FindAsync(order.Id);
    Assert.IsNotNull(savedOrder);
    Assert.AreEqual(100.00m, savedOrder.Total);
}

This test passes even though CustomerId = 999 doesn’t exist. In a real database with foreign key constraints, this would fail with a constraint violation.

What the In-Memory Provider Doesn’t Enforce

Foreign Key Constraints:

// This passes in-memory but fails in SQL Server
var order = new Order { CustomerId = 999 }; // Non-existent …