Why shouldn't I use EF Core's in-memory provider for testing?

The in-memory provider doesn’t enforce foreign key constraints, doesn’t match SQL Server behavior, and can give false confidence by passing tests that would fail against a real database.

What's better than the in-memory provider for EF Core testing?

Use SQLite in-memory databases for fast unit tests, or Docker containers with real databases for integration tests. Both provide more realistic database behavior.

How do I test EF Core repositories and services?

Focus on testing business logic separately from data access. Use repository patterns, mock DbContext when possible, and test against real database providers for integration scenarios.

Should I mock DbContext in unit tests?

Yes, for pure unit tests of business logic. Mock DbContext and DbSet to isolate the code under test. Use real databases for integration tests that verify data access logic.

How do I set up fast database tests with SQLite?

Use SQLite with in-memory mode, ensure your entities work with SQLite constraints, and reset the database between tests for isolation.

Unit Testing with EF Core: How to Avoid the In-Memory Provider Trap

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 customer
context.Orders.Add(order);
await context.SaveChangesAsync(); // No error in-memory!

Unique Constraints:

// Both orders with same OrderNumber - should fail
context.Orders.Add(new Order { OrderNumber = "ORD-001" });
context.Orders.Add(new Order { OrderNumber = "ORD-001" }); // Duplicate!
await context.SaveChangesAsync(); // No error in-memory!

SQL Server-Specific Behaviors:

// String comparison behavior differs
var customers = await context.Customers
    .Where(c => c.Name == "JOHN") // Case-insensitive in SQL Server
    .ToListAsync(); // Returns different results in-memory vs SQL Server

Transaction Behavior:

// Concurrency conflicts not properly simulated
var customer1 = await context.Customers.FindAsync(1);
var customer2 = await context.Customers.FindAsync(1);

customer1.Name = "Updated Name 1";
customer2.Name = "Updated Name 2";

await context.SaveChangesAsync(); // First update
await context.SaveChangesAsync(); // Should fail with concurrency conflict, but doesn't in-memory

Better Testing Strategies

1. SQLite In-Memory for Fast Unit Tests

SQLite provides real database behavior while running in memory:

public class DatabaseTestBase
{
    protected OrderContext CreateContext()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();
        
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseSqlite(connection)
            .Options;
            
        var context = new OrderContext(options);
        context.Database.EnsureCreated();
        
        return context;
    }
    
    protected async Task SeedTestDataAsync(OrderContext context)
    {
        var customer = new Customer { Id = 1, Name = "Test Customer", Email = "[email protected]" };
        context.Customers.Add(customer);
        await context.SaveChangesAsync();
    }
}

[Test]
public async Task CreateOrder_WithInvalidCustomer_ShouldThrowException()
{
    // Arrange
    using var context = CreateContext();
    await SeedTestDataAsync(context);
    
    var order = new Order { CustomerId = 999, Total = 100.00m }; // Invalid customer
    
    // Act & Assert
    context.Orders.Add(order);
    
    var exception = await Assert.ThrowsAsync<DbUpdateException>(() => 
        context.SaveChangesAsync());
        
    Assert.That(exception.InnerException?.Message, Contains.Substring("FOREIGN KEY constraint failed"));
}

Now the test correctly fails when trying to create an order with an invalid customer ID.

2. Repository Pattern for Testability

Separate business logic from data access concerns:

public interface IOrderRepository
{
    Task<Order> GetByIdAsync(int orderId);
    Task<Order> CreateAsync(Order order);
    Task<List<Order>> GetOrdersByCustomerAsync(int customerId);
    Task<bool> OrderNumberExistsAsync(string orderNumber);
}

public class OrderRepository : IOrderRepository
{
    private readonly OrderContext context;
    
    public OrderRepository(OrderContext context)
    {
        this.context = context;
    }
    
    public async Task<Order> GetByIdAsync(int orderId)
    {
        return await context.Orders
            .Include(o => o.Customer)
            .Include(o => o.OrderItems)
            .FirstOrDefaultAsync(o => o.Id == orderId);
    }
    
    public async Task<Order> CreateAsync(Order order)
    {
        context.Orders.Add(order);
        await context.SaveChangesAsync();
        return order;
    }
    
    public async Task<List<Order>> GetOrdersByCustomerAsync(int customerId)
    {
        return await context.Orders
            .Where(o => o.CustomerId == customerId)
            .Include(o => o.OrderItems)
            .ToListAsync();
    }
    
    public async Task<bool> OrderNumberExistsAsync(string orderNumber)
    {
        return await context.Orders
            .AnyAsync(o => o.OrderNumber == orderNumber);
    }
}

Business logic service that depends on the repository:

public class OrderService
{
    private readonly IOrderRepository orderRepository;
    private readonly ICustomerRepository customerRepository;
    
    public OrderService(IOrderRepository orderRepository, ICustomerRepository customerRepository)
    {
        this.orderRepository = orderRepository;
        this.customerRepository = customerRepository;
    }
    
    public async Task<OrderResult> CreateOrderAsync(CreateOrderRequest request)
    {
        // Validate customer exists
        var customer = await customerRepository.GetByIdAsync(request.CustomerId);
        if (customer == null)
        {
            return OrderResult.Failure("Customer not found");
        }
        
        // Check for duplicate order number
        if (await orderRepository.OrderNumberExistsAsync(request.OrderNumber))
        {
            return OrderResult.Failure("Order number already exists");
        }
        
        // Calculate total
        var total = request.Items.Sum(item => item.Price * item.Quantity);
        
        var order = new Order
        {
            CustomerId = request.CustomerId,
            OrderNumber = request.OrderNumber,
            Total = total,
            OrderDate = DateTime.UtcNow
        };
        
        var createdOrder = await orderRepository.CreateAsync(order);
        return OrderResult.Success(createdOrder);
    }
}

3. Unit Testing Business Logic with Mocks

Test business logic separately from data access:

[Test]
public async Task CreateOrder_WithInvalidCustomer_ShouldReturnFailure()
{
    // Arrange
    var mockOrderRepository = new Mock<IOrderRepository>();
    var mockCustomerRepository = new Mock<ICustomerRepository>();
    
    mockCustomerRepository
        .Setup(r => r.GetByIdAsync(999))
        .ReturnsAsync((Customer)null); // Customer doesn't exist
    
    var orderService = new OrderService(mockOrderRepository.Object, mockCustomerRepository.Object);
    
    var request = new CreateOrderRequest
    {
        CustomerId = 999,
        OrderNumber = "ORD-001",
        Items = new[] { new OrderItemRequest { Price = 100.00m, Quantity = 1 } }
    };
    
    // Act
    var result = await orderService.CreateOrderAsync(request);
    
    // Assert
    Assert.IsFalse(result.IsSuccess);
    Assert.AreEqual("Customer not found", result.ErrorMessage);
    
    // Verify repository interactions
    mockCustomerRepository.Verify(r => r.GetByIdAsync(999), Times.Once);
    mockOrderRepository.Verify(r => r.CreateAsync(It.IsAny<Order>()), Times.Never);
}

[Test]
public async Task CreateOrder_WithDuplicateOrderNumber_ShouldReturnFailure()
{
    // Arrange
    var mockOrderRepository = new Mock<IOrderRepository>();
    var mockCustomerRepository = new Mock<ICustomerRepository>();
    
    mockCustomerRepository
        .Setup(r => r.GetByIdAsync(1))
        .ReturnsAsync(new Customer { Id = 1, Name = "Test Customer" });
        
    mockOrderRepository
        .Setup(r => r.OrderNumberExistsAsync("ORD-001"))
        .ReturnsAsync(true); // Order number already exists
    
    var orderService = new OrderService(mockOrderRepository.Object, mockCustomerRepository.Object);
    
    var request = new CreateOrderRequest
    {
        CustomerId = 1,
        OrderNumber = "ORD-001",
        Items = new[] { new OrderItemRequest { Price = 100.00m, Quantity = 1 } }
    };
    
    // Act
    var result = await orderService.CreateOrderAsync(request);
    
    // Assert
    Assert.IsFalse(result.IsSuccess);
    Assert.AreEqual("Order number already exists", result.ErrorMessage);
}

4. Integration Tests with Real Database Behavior

Test the full stack with SQLite or test containers:

[Test]
public async Task OrderRepository_CreateOrder_ShouldEnforceConstraints()
{
    // Arrange
    using var context = CreateContext();
    await SeedTestDataAsync(context);
    
    var repository = new OrderRepository(context);
    var order = new Order 
    { 
        CustomerId = 1, // Valid customer
        OrderNumber = "ORD-001",
        Total = 100.00m 
    };
    
    // Act
    var createdOrder = await repository.CreateAsync(order);
    
    // Assert
    Assert.IsNotNull(createdOrder);
    Assert.Greater(createdOrder.Id, 0);
    
    // Verify order was actually saved
    var retrievedOrder = await repository.GetByIdAsync(createdOrder.Id);
    Assert.IsNotNull(retrievedOrder);
    Assert.AreEqual("ORD-001", retrievedOrder.OrderNumber);
}

[Test]
public async Task OrderRepository_CreateOrder_WithInvalidCustomer_ShouldThrow()
{
    // Arrange
    using var context = CreateContext();
    // Don't seed customer data
    
    var repository = new OrderRepository(context);
    var order = new Order 
    { 
        CustomerId = 999, // Invalid customer
        OrderNumber = "ORD-001",
        Total = 100.00m 
    };
    
    // Act & Assert
    var exception = await Assert.ThrowsAsync<DbUpdateException>(() => 
        repository.CreateAsync(order));
        
    Assert.That(exception.InnerException?.Message, Contains.Substring("FOREIGN KEY constraint failed"));
}

Docker Test Containers for SQL Server

For even more realistic testing, use Docker containers with actual SQL Server:

public class SqlServerIntegrationTestBase : IAsyncDisposable
{
    private readonly MsSqlContainer sqlContainer;
    private OrderContext context;
    
    public SqlServerIntegrationTestBase()
    {
        sqlContainer = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
            .WithPassword("YourStrong!Passw0rd")
            .Build();
    }
    
    public async Task InitializeAsync()
    {
        await sqlContainer.StartAsync();
        
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseSqlServer(sqlContainer.GetConnectionString())
            .Options;
            
        context = new OrderContext(options);
        await context.Database.MigrateAsync();
    }
    
    protected OrderContext GetContext() => context;
    
    public async ValueTask DisposeAsync()
    {
        await context.DisposeAsync();
        await sqlContainer.DisposeAsync();
    }
}

[Test]
public async Task FullIntegrationTest_WithRealSqlServer()
{
    // Arrange
    using var testBase = new SqlServerIntegrationTestBase();
    await testBase.InitializeAsync();
    
    var context = testBase.GetContext();
    var repository = new OrderRepository(context);
    var customerRepository = new CustomerRepository(context);
    var orderService = new OrderService(repository, customerRepository);
    
    // Create test data
    var customer = new Customer { Name = "Integration Test Customer", Email = "[email protected]" };
    await customerRepository.CreateAsync(customer);
    
    // Act
    var request = new CreateOrderRequest
    {
        CustomerId = customer.Id,
        OrderNumber = "INT-001",
        Items = new[] { new OrderItemRequest { Price = 99.99m, Quantity = 2 } }
    };
    
    var result = await orderService.CreateOrderAsync(request);
    
    // Assert
    Assert.IsTrue(result.IsSuccess);
    Assert.AreEqual(199.98m, result.Order.Total);
    
    // Verify in database
    var savedOrder = await repository.GetByIdAsync(result.Order.Id);
    Assert.IsNotNull(savedOrder);
    Assert.AreEqual("INT-001", savedOrder.OrderNumber);
}

Testing Strategy Guidelines

Use This Testing Pyramid

Unit Tests (Mocked Dependencies):

Test business logic in isolation
Fast execution
Mock repository interfaces
Focus on edge cases and validation logic

Integration Tests (SQLite In-Memory):

Test repository implementations
Verify query logic
Test EF Core configurations
Validate database constraints

End-to-End Tests (Real Database):

Test complete user scenarios
Use Docker containers or test databases
Verify production-like behavior
Test complex queries and transactions

Configuration for Multiple Providers

Support different databases for different test scenarios:

public static class TestContextFactory
{
    public static OrderContext CreateSqliteContext()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();
        
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseSqlite(connection)
            .EnableSensitiveDataLogging()
            .Options;
            
        var context = new OrderContext(options);
        context.Database.EnsureCreated();
        return context;
    }
    
    public static OrderContext CreateSqlServerContext(string connectionString)
    {
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseSqlServer(connectionString)
            .EnableSensitiveDataLogging()
            .Options;
            
        return new OrderContext(options);
    }
    
    public static OrderContext CreateInMemoryContext(string databaseName)
    {
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseInMemoryDatabase(databaseName)
            .EnableSensitiveDataLogging()
            .Options;
            
        return new OrderContext(options);
    }
}

Common Testing Pitfalls

Not Testing Actual Database Constraints

// Bad - doesn't test real constraints
[Test]
public async Task CreateDuplicateEmail_ShouldThrow()
{
    var options = new DbContextOptionsBuilder<CustomerContext>()
        .UseInMemoryDatabase("test")
        .Options;
        
    using var context = new CustomerContext(options);
    
    context.Customers.Add(new Customer { Email = "[email protected]" });
    context.Customers.Add(new Customer { Email = "[email protected]" });
    
    // This doesn't throw with in-memory provider!
    await context.SaveChangesAsync();
}

// Good - tests real constraints with SQLite
[Test]
public async Task CreateDuplicateEmail_ShouldThrow()
{
    using var context = CreateSqliteContext();
    
    context.Customers.Add(new Customer { Email = "[email protected]" });
    await context.SaveChangesAsync();
    
    context.Customers.Add(new Customer { Email = "[email protected]" });
    
    var exception = await Assert.ThrowsAsync<DbUpdateException>(() => 
        context.SaveChangesAsync());
        
    Assert.That(exception.InnerException?.Message, Contains.Substring("UNIQUE constraint failed"));
}

Testing Implementation Details Instead of Behavior

// Bad - testing EF Core internals
[Test]
public void DbContext_ShouldHaveCorrectConfiguration()
{
    var context = new OrderContext();
    var model = context.Model;
    var entityType = model.FindEntityType(typeof(Order));
    
    Assert.IsNotNull(entityType);
    // Testing EF Core configuration details
}

// Good - testing actual behavior
[Test]
public async Task GetOrdersByCustomer_ShouldReturnCorrectOrders()
{
    using var context = CreateSqliteContext();
    await SeedTestDataAsync(context);
    
    var repository = new OrderRepository(context);
    var orders = await repository.GetOrdersByCustomerAsync(customerId: 1);
    
    Assert.AreEqual(2, orders.Count);
    Assert.IsTrue(orders.All(o => o.CustomerId == 1));
}

Pro Tip: I learned this lesson the hard way after a production incident where foreign key constraints failed, but all our tests were green. Since switching to SQLite for integration tests and proper mocking for unit tests, we catch real database issues before deployment.

Performance Considerations

SQLite in-memory is still very fast for testing:

[Benchmark]
public class DatabaseTestPerformance
{
    [Benchmark(Baseline = true)]
    public async Task InMemoryProvider_100Operations()
    {
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseInMemoryDatabase("benchmark")
            .Options;
            
        using var context = new OrderContext(options);
        
        for (int i = 0; i < 100; i++)
        {
            context.Orders.Add(new Order { Total = i });
            await context.SaveChangesAsync();
        }
    }
    
    [Benchmark]
    public async Task SqliteInMemory_100Operations()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();
        
        var options = new DbContextOptionsBuilder<OrderContext>()
            .UseSqlite(connection)
            .Options;
            
        using var context = new OrderContext(options);
        context.Database.EnsureCreated();
        
        for (int i = 0; i < 100; i++)
        {
            context.Orders.Add(new Order { Total = i });
            await context.SaveChangesAsync();
        }
    }
}

Typical results:

In-Memory Provider: 45ms
SQLite In-Memory: 78ms

The 73% performance difference is worth the reliability gain.

Key Takeaway

The EF Core in-memory provider creates a false sense of security by not enforcing database constraints and behaviors. Use SQLite in-memory for fast, realistic database testing, mock repositories for pure unit tests, and leverage Docker containers for full integration testing.

Your tests should give you confidence that your code works against real databases, not just in-memory collections. The small performance cost of SQLite is worth avoiding production issues that in-memory tests miss.

The In-Memory Provider Problem#

What the In-Memory Provider Doesn’t Enforce#

Better Testing Strategies#

1. SQLite In-Memory for Fast Unit Tests#

2. Repository Pattern for Testability#

3. Unit Testing Business Logic with Mocks#

4. Integration Tests with Real Database Behavior#

Docker Test Containers for SQL Server#

Testing Strategy Guidelines#

Use This Testing Pyramid#

Configuration for Multiple Providers#

Common Testing Pitfalls#

Not Testing Actual Database Constraints#

Testing Implementation Details Instead of Behavior#

Performance Considerations#

Key Takeaway#

Posts in the "30 Days of EF Core That Actually Scales" series