Unit Testing Repositories — Repository Pattern Deep… | OneNoughtOne

0/86

System Design (LLD)Persistence Layer Design

Repository Pattern Deep Dive

LevelIntermediate

Duration75 mins

TopicPersistence Layer Design

4 / 4

Unit Testing Repositories

The Testing Challenge

Repositories interact with external databases—by definition, they have side effects and dependencies on infrastructure. This makes testing them more nuanced than testing pure domain logic. How do you write tests that are fast, reliable, and actually verify that your persistence layer works? The answer involves understanding the testing spectrum: from ultra-fast unit tests with mocks, through in-memory database tests, to full integration tests against real databases. Each approach has its place, and knowing when to use which is a hallmark of experienced engineers.

What You Will Learn

By the end of this page, you will understand the complete testing strategy for repositories: when to use mocks vs. in-memory databases vs. real databases, how to set up effective test fixtures, what to test and what not to test, and how to balance test speed with test confidence.

The Repository Testing Spectrum

Repository tests fall on a spectrum from isolated unit tests to full integration tests. Understanding this spectrum helps you choose the right approach for what you're trying to verify.

Repository Testing Approaches
Approach	Speed	Confidence	What It Tests	Best For
Mock/Fake Repository	Fastest (ms)	Low for repo	Domain logic using repository	Testing services that USE repositories
In-Memory Database	Fast (10-100ms)	Medium	ORM mappings, query logic	Testing repository implementations
Real Database (Containerized)	Slower (100ms-1s)	High	Full stack including DB	Verifying production behavior
Real Database (Shared)	Slowest	Highest	Production-like environment	CI/CD integration tests

The Testing Pyramid

Most repository tests should use in-memory databases—they're fast enough for TDD and reliable enough to catch real bugs. Mock repositories are for testing code that USES repositories. Real database tests are for critical scenarios and CI verification. Don't put all tests at one level.

A Balanced Testing Strategy: /\ / \ Few: Real database integration tests / \ (CI/CD, critical paths) /------\ / \ Many: In-memory database tests / \ (Repository implementations) /──────────── / \ Most: Mocks for services that use repositories /__________________\ (Fast unit tests for domain/service logic) Let's explore each level in detail.

Testing with Mock and Fake Repositories

When testing code that uses a repository (services, handlers, domain logic), you should mock or fake the repository. This isolates the code under test and runs blazingly fast. Mock vs Fake: Mock : A test double that records calls and returns pre-programmed responses. Created with mocking libraries (Moq, Mockito, Jest). Fake : A simplified, working implementation that uses in-memory data structures. More reusable across tests.

MockRepositoryTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
using Moq;
using Xunit;
 
public class PlaceOrderServiceTests
{
    private readonly Mock<IOrderRepository> _orderRepoMock;
    private readonly Mock<ICustomerRepository> _customerRepoMock;
    private readonly PlaceOrderService _service;
    
    public PlaceOrderServiceTests()
    {
        _orderRepoMock = new Mock<IOrderRepository>();
        _customerRepoMock = new Mock<ICustomerRepository>();
        _service = new PlaceOrderService(
            _orderRepoMock.Object,
            _customerRepoMock.Object);
    }
    
    [Fact]
    public async Task PlaceOrder_ValidCustomer_CreatesOrderAndAddsToRepository()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        var customer = new Customer(customerId, "John Doe", Email.From("john@example.com"));
        
        _customerRepoMock
            .Setup(r => r.GetByIdAsync(customerId, It.IsAny<CancellationToken>()))
            .ReturnsAsync(customer);
        
        var command = new PlaceOrderCommand(
            CustomerId: customerId,
            Items: new[] { new OrderItemDto("SKU-001", 2, 29.99m) });
        
        // Act
        var orderId = await _service.PlaceOrder(command);
        
        // Assert
        Assert.NotNull(orderId);
        
        // Verify Add was called with an order containing correct data
        _orderRepoMock.Verify(
            r => r.Add(It.Is<Order>(o => 
                o.CustomerId == customerId &&
                o.LineItems.Count == 1)),
            Times.Once);
    }
    
    [Fact]
    public async Task PlaceOrder_CustomerNotFound_ThrowsException()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        _customerRepoMock
            .Setup(r => r.GetByIdAsync(customerId, It.IsAny<CancellationToken>()))
            .ReturnsAsync((Customer?)null);
        
        var command = new PlaceOrderCommand(customerId, Array.Empty<OrderItemDto>());
        
        // Act & Assert
        await Assert.ThrowsAsync<CustomerNotFoundException>(
            () => _service.PlaceOrder(command));
        
        // Verify Add was never called
        _orderRepoMock.Verify(r => r.Add(It.IsAny<Order>()), Times.Never);
    }
    
    [Fact]
    public async Task PlaceOrder_DuplicateIdempotencyKey_ThrowsException()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        var customer = new Customer(customerId, "John Doe", Email.From("john@example.com"));
        
        _customerRepoMock
            .Setup(r => r.GetByIdAsync(customerId, It.IsAny<CancellationToken>()))
            .ReturnsAsync(customer);
        
        _orderRepoMock
            .Setup(r => r.ExistsWithIdempotencyKey("unique-key-123"))
            .Returns(true);  // Already exists
        
        var command = new PlaceOrderCommand(
            CustomerId: customerId,
            Items: new[] { new OrderItemDto("SKU-001", 1, 19.99m) },
            IdempotencyKey: "unique-key-123");
        
        // Act & Assert
        await Assert.ThrowsAsync<DuplicateOrderException>(
            () => _service.PlaceOrder(command));
    }
}

What Mocks Don't Test

Mocks verify that your service calls repository methods correctly. They do NOT verify that your repository actually works—that it maps entities correctly, that queries return expected results, or that transactions commit. For that, you need in-memory or integration tests on the repository itself.

In-Memory Fake Repositories

A fake repository is a fully functional in-memory implementation. It's more reusable than mocks and can be shared across tests. Fakes are especially useful when multiple tests need a realistic repository that maintains state across operations.

FakeOrderRepository.cs
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
/// <summary>
/// In-memory implementation of IOrderRepository for testing.
/// Maintains state like a real repository but uses Dictionary storage.
/// </summary>
public class FakeOrderRepository : IOrderRepository
{
    private readonly Dictionary<OrderId, Order> _orders = new();
    private long _nextOrderNumber = 1000;
    
    public Order? GetById(OrderId id)
    {
        return _orders.TryGetValue(id, out var order) ? order : null;
    }
    
    public Order GetByIdOrThrow(OrderId id)
    {
        return GetById(id) ?? throw new OrderNotFoundException(id);
    }
    
    public Task<Order?> GetByIdAsync(OrderId id, CancellationToken ct = default)
    {
        return Task.FromResult(GetById(id));
    }
    
    public void Add(Order order)
    {
        if (_orders.ContainsKey(order.Id))
            throw new DuplicateOrderException(order.Id, new Exception("Fake duplicate"));
        
        _orders[order.Id] = order;
    }
    
    public void Remove(Order order)
    {
        _orders.Remove(order.Id);
    }
    
    public IEnumerable<Order> FindOrdersReadyForShipment()
    {
        return _orders.Values
            .Where(o => 
                o.Status == OrderStatus.Paid &&
                o.StockVerified &&
                !o.ShippedAt.HasValue)
            .OrderBy(o => o.OrderDate)
            .ToList();
    }
    
    public IEnumerable<Order> FindOrdersPendingPayment()
    {
        return _orders.Values
            .Where(o => o.Status == OrderStatus.PendingPayment)
            .OrderBy(o => o.OrderDate)
            .ToList();
    }
    
    public IEnumerable<Order> FindForCustomer(CustomerId customerId)
    {
        return _orders.Values
            .Where(o => o.CustomerId == customerId)
            .OrderByDescending(o => o.OrderDate)
            .ToList();
    }
    
    public bool ExistsWithIdempotencyKey(string key)
    {
        return _orders.Values.Any(o => o.IdempotencyKey == key);
    }
    
    public OrderNumber GenerateNextOrderNumber()
    {
        return new OrderNumber(Interlocked.Increment(ref _nextOrderNumber));
    }
    
    // Test helper methods
    public void Clear() => _orders.Clear();
    public int Count => _orders.Count;
    public void Seed(IEnumerable<Order> orders)
    {
        foreach (var order in orders)
            _orders[order.Id] = order;
    }
}
 
// Usage in tests
public class OrderProcessingTests
{
    private readonly FakeOrderRepository _orderRepo;
    private readonly ShipmentService _service;
    
    public OrderProcessingTests()
    {
        _orderRepo = new FakeOrderRepository();
        _service = new ShipmentService(_orderRepo);
    }
    
    [Fact]
    public void ProcessReadyOrders_ShipsAllQualifyingOrders()
    {
        // Arrange - Seed with test data
        var readyOrder1 = CreatePaidVerifiedOrder();
        var readyOrder2 = CreatePaidVerifiedOrder();
        var pendingOrder = CreatePendingOrder();  // Should not be shipped
        
        _orderRepo.Seed(new[] { readyOrder1, readyOrder2, pendingOrder });
        
        // Act
        var shippedCount = _service.ProcessReadyOrders();
        
        // Assert
        Assert.Equal(2, shippedCount);
        Assert.True(_orderRepo.GetById(readyOrder1.Id)!.ShippedAt.HasValue);
        Assert.True(_orderRepo.GetById(readyOrder2.Id)!.ShippedAt.HasValue);
        Assert.False(_orderRepo.GetById(pendingOrder.Id)!.ShippedAt.HasValue);
    }
}

Fake Repository Best Practices

Keep fake logic simple—use LINQ on collections to mirror query behavior. Add test helper methods (Seed, Clear, Count) to make test setup easier. Throw the same exceptions as real repositories (DuplicateOrderException) to test error handling paths. Consider using the same fake in both unit and acceptance tests for consistency.

Testing with In-Memory Databases

To test the actual repository implementation —the one that uses Entity Framework, Hibernate, or another ORM—you need a database. In-memory databases provide fast, isolated testing without external dependencies. Options: EF Core InMemory Provider : Fastest, but doesn't enforce constraints or relationships SQLite In-Memory : Fast, enforces SQL semantics, good middle ground H2 (Java) : In-memory relational database, excellent Hibernate compatibility Testcontainers : Real database in Docker, slower but highest fidelity

InMemoryDatabaseTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
using Microsoft.EntityFrameworkCore;
using Xunit;
 
public class EfOrderRepositoryTests : IDisposable
{
    private readonly OrderingDbContext _context;
    private readonly EfOrderRepository _repository;
    
    public EfOrderRepositoryTests()
    {
        // Create in-memory database with unique name for test isolation
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;
        
        _context = new OrderingDbContext(options);
        _repository = new EfOrderRepository(_context, new NullLogger<EfOrderRepository>());
    }
    
    [Fact]
    public void GetById_ExistingOrder_ReturnsOrderWithLineItems()
    {
        // Arrange
        var order = CreateTestOrder();
        order.AddLineItem(new LineItem("SKU-001", 2, Money.FromDecimal(29.99m, "USD")));
        order.AddLineItem(new LineItem("SKU-002", 1, Money.FromDecimal(49.99m, "USD")));
        
        _context.Orders.Add(order);
        _context.SaveChanges();
        _context.ChangeTracker.Clear(); // Clear tracking to simulate fresh load
        
        // Act
        var retrieved = _repository.GetById(order.Id);
        
        // Assert
        Assert.NotNull(retrieved);
        Assert.Equal(order.Id, retrieved.Id);
        Assert.Equal(2, retrieved.LineItems.Count); // Verifies eager loading works
    }
    
    [Fact]
    public void GetById_NonExistingOrder_ReturnsNull()
    {
        // Arrange
        var nonExistentId = new OrderId(Guid.NewGuid());
        
        // Act
        var retrieved = _repository.GetById(nonExistentId);
        
        // Assert
        Assert.Null(retrieved);
    }
    
    [Fact]
    public void Add_NewOrder_PersistsSuccessfully()
    {
        // Arrange
        var order = CreateTestOrder();
        
        // Act
        _repository.Add(order);
        _context.SaveChanges();
        
        // Assert
        _context.ChangeTracker.Clear();
        var persisted = _context.Orders.Find(order.Id);
        Assert.NotNull(persisted);
    }
    
    [Fact]
    public void FindOrdersReadyForShipment_OnlyReturnsQualifyingOrders()
    {
        // Arrange
        var readyOrder = CreateTestOrder();
        readyOrder.MarkAsPaid();
        readyOrder.VerifyStock();
        
        var notPaidOrder = CreateTestOrder();
        // Status is still Pending
        
        var paidButNotVerifiedOrder = CreateTestOrder();
        paidButNotVerifiedOrder.MarkAsPaid();
        // Stock not verified
        
        var alreadyShippedOrder = CreateTestOrder();
        alreadyShippedOrder.MarkAsPaid();
        alreadyShippedOrder.VerifyStock();
        alreadyShippedOrder.MarkAsShipped();
        
        _context.Orders.AddRange(
            readyOrder, notPaidOrder, paidButNotVerifiedOrder, alreadyShippedOrder);
        _context.SaveChanges();
        
        // Act
        var readyOrders = _repository.FindOrdersReadyForShipment().ToList();
        
        // Assert
        Assert.Single(readyOrders);
        Assert.Equal(readyOrder.Id, readyOrders[0].Id);
    }
    
    [Fact]
    public void ExistsWithIdempotencyKey_DuplicateKey_ReturnsTrue()
    {
        // Arrange
        var order = CreateTestOrder(idempotencyKey: "unique-key-abc");
        _context.Orders.Add(order);
        _context.SaveChanges();
        
        // Act
        var exists = _repository.ExistsWithIdempotencyKey("unique-key-abc");
        
        // Assert
        Assert.True(exists);
    }
    
    public void Dispose()
    {
        _context.Dispose();
    }
    
    private static Order CreateTestOrder(string? idempotencyKey = null)
    {
        return new Order(
            new OrderId(Guid.NewGuid()),
            new CustomerId(Guid.NewGuid()),
            DateTime.UtcNow,
            idempotencyKey);
    }
}

In-Memory Database Limitations

EF Core's InMemory provider doesn't enforce foreign keys, unique constraints, or computed columns. For tests that depend on these features, use SQLite in-memory mode or Testcontainers with a real database. Always verify critical constraints in integration tests against a production-like database.

SQLite In-Memory for Higher Fidelity

SQLite in-memory mode provides a real SQL database that enforces constraints, making it superior to pure in-memory providers for catching issues early.

SqliteRepositoryTests.cs
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Xunit;
 
public class SqliteOrderRepositoryTests : IDisposable
{
    private readonly SqliteConnection _connection;
    private readonly OrderingDbContext _context;
    private readonly EfOrderRepository _repository;
    
    public SqliteOrderRepositoryTests()
    {
        // Create an in-memory SQLite database
        // Connection string uses DataSource=:memory: for in-memory database
        // Mode=ReadWriteCreate to allow creating tables
        _connection = new SqliteConnection("DataSource=:memory:");
        _connection.Open();  // Keep connection open for lifetime of test
        
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseSqlite(_connection)
            .Options;
        
        _context = new OrderingDbContext(options);
        _context.Database.EnsureCreated();  // Create tables from model
        
        _repository = new EfOrderRepository(_context, new NullLogger<EfOrderRepository>());
    }
    
    [Fact]
    public void Add_DuplicateOrderId_ThrowsException()
    {
        // Arrange
        var orderId = new OrderId(Guid.NewGuid());
        var order1 = CreateTestOrderWithId(orderId);
        var order2 = CreateTestOrderWithId(orderId);  // Same ID
        
        _repository.Add(order1);
        _context.SaveChanges();
        
        // Act & Assert
        _repository.Add(order2);
        
        // SQLite enforces primary key constraint
        Assert.Throws<DbUpdateException>(() => _context.SaveChanges());
    }
    
    [Fact]
    public void Update_ConcurrentModification_ThrowsConcurrencyException()
    {
        // Arrange - requires RowVersion/Concurrency token on Order entity
        var order = CreateTestOrder();
        _context.Orders.Add(order);
        _context.SaveChanges();
        
        // Simulate concurrent modification
        using (var context2 = CreateContext())
        {
            var orderCopy = context2.Orders.Find(order.Id)!;
            orderCopy.AddLineItem(new LineItem("SKU-X", 1, Money.USD(10)));
            context2.SaveChanges();
        }
        
        // Act - try to save from original context
        order.AddLineItem(new LineItem("SKU-Y", 1, Money.USD(20)));
        
        // Assert
        Assert.Throws<DbUpdateConcurrencyException>(() => _context.SaveChanges());
    }
    
    [Fact]
    public void FindForCustomer_OrderByDescendingDate_ReturnsCorrectOrder()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        
        var oldOrder = CreateTestOrder(customerId, DateTime.UtcNow.AddDays(-10));
        var middleOrder = CreateTestOrder(customerId, DateTime.UtcNow.AddDays(-5));
        var newOrder = CreateTestOrder(customerId, DateTime.UtcNow);
        
        // Add in random order
        _context.Orders.AddRange(middleOrder, newOrder, oldOrder);
        _context.SaveChanges();
        _context.ChangeTracker.Clear();
        
        // Act
        var orders = _repository.FindForCustomer(customerId).ToList();
        
        // Assert - should be newest first
        Assert.Equal(3, orders.Count);
        Assert.Equal(newOrder.Id, orders[0].Id);
        Assert.Equal(middleOrder.Id, orders[1].Id);
        Assert.Equal(oldOrder.Id, orders[2].Id);
    }
    
    private OrderingDbContext CreateContext()
    {
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseSqlite(_connection)
            .Options;
        return new OrderingDbContext(options);
    }
    
    public void Dispose()
    {
        _context.Dispose();
        _connection.Dispose();
    }
}

SQLite vs InMemory Provider

Use SQLite in-memory when you need: primary key constraints, foreign key enforcement, unique constraints, ordering/sorting verification, concurrency token testing. Use EF Core InMemory when you need maximum speed and only care about basic CRUD behavior.

Integration Testing with Real Databases

For maximum confidence, test against the same database engine you use in production. Testcontainers makes this manageable by spinning up real databases in Docker containers during test runs.

TestcontainersRepositoryTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
using Testcontainers.PostgreSql;
using Xunit;
 
public class PostgresOrderRepositoryTests : IAsyncLifetime
{
    private PostgreSqlContainer _postgres = null!;
    private OrderingDbContext _context = null!;
    private EfOrderRepository _repository = null!;
    
    public async Task InitializeAsync()
    {
        // Start a PostgreSQL container
        _postgres = new PostgreSqlBuilder()
            .WithImage("postgres:15-alpine")
            .WithDatabase("ordering_tests")
            .WithUsername("test")
            .WithPassword("test")
            .Build();
        
        await _postgres.StartAsync();
        
        // Configure EF Core to use the container
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseNpgsql(_postgres.GetConnectionString())
            .Options;
        
        _context = new OrderingDbContext(options);
        await _context.Database.MigrateAsync();  // Apply migrations
        
        _repository = new EfOrderRepository(_context, new NullLogger<EfOrderRepository>());
    }
    
    public async Task DisposeAsync()
    {
        await _context.DisposeAsync();
        await _postgres.DisposeAsync();
    }
    
    [Fact]
    public async Task GenerateNextOrderNumber_UsesDatabaseSequence()
    {
        // Arrange - sequence is created by migration
        
        // Act
        var number1 = _repository.GenerateNextOrderNumber();
        var number2 = _repository.GenerateNextOrderNumber();
        var number3 = _repository.GenerateNextOrderNumber();
        
        // Assert - numbers are sequential
        Assert.True(number2.Value > number1.Value);
        Assert.True(number3.Value > number2.Value);
    }
    
    [Fact]
    public async Task Add_OrderWithLineItems_CreatesRecordsInAllTables()
    {
        // Arrange
        var order = CreateTestOrder();
        order.AddLineItem(new LineItem("SKU-001", 2, Money.FromDecimal(29.99m, "USD")));
        order.AddLineItem(new LineItem("SKU-002", 1, Money.FromDecimal(49.99m, "USD")));
        
        // Act
        _repository.Add(order);
        await _context.SaveChangesAsync();
        
        // Assert - verify using raw SQL that records exist
        await using var connection = new NpgsqlConnection(_postgres.GetConnectionString());
        await connection.OpenAsync();
        
        var orderCount = await connection.ExecuteScalarAsync<int>(
            "SELECT COUNT(*) FROM orders WHERE id = @id",
            new { id = order.Id.Value });
        Assert.Equal(1, orderCount);
        
        var lineItemCount = await connection.ExecuteScalarAsync<int>(
            "SELECT COUNT(*) FROM order_line_items WHERE order_id = @id",
            new { id = order.Id.Value });
        Assert.Equal(2, lineItemCount);
    }
    
    [Fact]
    public async Task FindOrdersReadyForShipment_UsesIndex()
    {
        // This test verifies that the query plan uses expected indexes
        // Important for performance-critical queries
        
        // Seed enough data to make indexes relevant
        for (int i = 0; i < 100; i++)
        {
            var order = CreateTestOrder();
            if (i % 10 == 0)
            {
                order.MarkAsPaid();
                order.VerifyStock();
            }
            _context.Orders.Add(order);
        }
        await _context.SaveChangesAsync();
        _context.ChangeTracker.Clear();
        
        // Use EXPLAIN ANALYZE to verify index usage
        await using var connection = new NpgsqlConnection(_postgres.GetConnectionString());
        await connection.OpenAsync();
        
        var explainResult = await connection.QueryFirstAsync<string>(
            @"EXPLAIN (ANALYZE, FORMAT TEXT) 
              SELECT * FROM orders 
              WHERE status = 'Paid' AND stock_verified = true AND shipped_at IS NULL");
        
        // Assert index is used (look for Index Scan vs Seq Scan)
        Assert.Contains("Index", explainResult);
    }
}

When to Use Real Database Tests

Use real database tests for: database sequences, stored procedures, database-specific features (JSON queries, full-text search), index usage verification, migration testing, and any behavior that differs between databases. These tests are slower but catch issues that in-memory tests miss.

What to Test (and What Not To)

Not every repository method needs exhaustive testing. Focus your testing effort where it provides the most value.

Worth Testing Thoroughly

•Custom Query Methods — FindOrdersReadyForShipment, SearchByName, etc. These contain business logic.
•Complex Loading — Verify eager loading works correctly for aggregates with multiple collections.
•Edge Cases — Empty results, null handling, large datasets, special characters.
•Ordering and Pagination — Verify sort order, offset, and limit behavior.
•Constraint Enforcement — Unique constraints, foreign keys (with SQL-based tests).
•Concurrency Handling — Optimistic locking, version conflicts.

Usually Not Worth Testing

•Simple CRUD — If GetById just calls Find(), you're testing the ORM, not your code.
•ORM Behavior — Entity Framework and Hibernate are well-tested. Don't re-test them.
•Framework Features — Change tracking, lazy loading mechanisms, etc.
•Generated Queries — Unless you suspect bugs, trust the query generation.
•Every Possible Input — Focus on boundaries and equivalence classes, not exhaustive inputs.

The Value Test

Before writing a repository test, ask: 'If this test passed but the code was wrong, would we have a production bug?' If yes, the test is valuable. If the test would only fail due to ORM misconfiguration (which would break everything), it might not be worth isolated testing.

System Design (LLD)Persistence Layer Design

Repository Pattern Deep Dive

LevelIntermediate

Duration75 mins

TopicPersistence Layer Design

4 / 4

Unit Testing Repositories

The Testing Challenge

Repositories interact with external databases—by definition, they have side effects and dependencies on infrastructure. This makes testing them more nuanced than testing pure domain logic. How do you write tests that are fast, reliable, and actually verify that your persistence layer works? The answer involves understanding the testing spectrum: from ultra-fast unit tests with mocks, through in-memory database tests, to full integration tests against real databases. Each approach has its place, and knowing when to use which is a hallmark of experienced engineers.

What You Will Learn

By the end of this page, you will understand the complete testing strategy for repositories: when to use mocks vs. in-memory databases vs. real databases, how to set up effective test fixtures, what to test and what not to test, and how to balance test speed with test confidence.

The Repository Testing Spectrum

Repository tests fall on a spectrum from isolated unit tests to full integration tests. Understanding this spectrum helps you choose the right approach for what you're trying to verify.

Repository Testing Approaches
Approach	Speed	Confidence	What It Tests	Best For
Mock/Fake Repository	Fastest (ms)	Low for repo	Domain logic using repository	Testing services that USE repositories
In-Memory Database	Fast (10-100ms)	Medium	ORM mappings, query logic	Testing repository implementations
Real Database (Containerized)	Slower (100ms-1s)	High	Full stack including DB	Verifying production behavior
Real Database (Shared)	Slowest	Highest	Production-like environment	CI/CD integration tests

The Testing Pyramid

Most repository tests should use in-memory databases—they're fast enough for TDD and reliable enough to catch real bugs. Mock repositories are for testing code that USES repositories. Real database tests are for critical scenarios and CI verification. Don't put all tests at one level.

A Balanced Testing Strategy: /\ / \ Few: Real database integration tests / \ (CI/CD, critical paths) /------\ / \ Many: In-memory database tests / \ (Repository implementations) /──────────── / \ Most: Mocks for services that use repositories /__________________\ (Fast unit tests for domain/service logic) Let's explore each level in detail.

Testing with Mock and Fake Repositories

When testing code that uses a repository (services, handlers, domain logic), you should mock or fake the repository. This isolates the code under test and runs blazingly fast. Mock vs Fake: Mock : A test double that records calls and returns pre-programmed responses. Created with mocking libraries (Moq, Mockito, Jest). Fake : A simplified, working implementation that uses in-memory data structures. More reusable across tests.

MockRepositoryTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
using Moq;
using Xunit;
 
public class PlaceOrderServiceTests
{
    private readonly Mock<IOrderRepository> _orderRepoMock;
    private readonly Mock<ICustomerRepository> _customerRepoMock;
    private readonly PlaceOrderService _service;
    
    public PlaceOrderServiceTests()
    {
        _orderRepoMock = new Mock<IOrderRepository>();
        _customerRepoMock = new Mock<ICustomerRepository>();
        _service = new PlaceOrderService(
            _orderRepoMock.Object,
            _customerRepoMock.Object);
    }
    
    [Fact]
    public async Task PlaceOrder_ValidCustomer_CreatesOrderAndAddsToRepository()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        var customer = new Customer(customerId, "John Doe", Email.From("john@example.com"));
        
        _customerRepoMock
            .Setup(r => r.GetByIdAsync(customerId, It.IsAny<CancellationToken>()))
            .ReturnsAsync(customer);
        
        var command = new PlaceOrderCommand(
            CustomerId: customerId,
            Items: new[] { new OrderItemDto("SKU-001", 2, 29.99m) });
        
        // Act
        var orderId = await _service.PlaceOrder(command);
        
        // Assert
        Assert.NotNull(orderId);
        
        // Verify Add was called with an order containing correct data
        _orderRepoMock.Verify(
            r => r.Add(It.Is<Order>(o => 
                o.CustomerId == customerId &&
                o.LineItems.Count == 1)),
            Times.Once);
    }
    
    [Fact]
    public async Task PlaceOrder_CustomerNotFound_ThrowsException()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        _customerRepoMock
            .Setup(r => r.GetByIdAsync(customerId, It.IsAny<CancellationToken>()))
            .ReturnsAsync((Customer?)null);
        
        var command = new PlaceOrderCommand(customerId, Array.Empty<OrderItemDto>());
        
        // Act & Assert
        await Assert.ThrowsAsync<CustomerNotFoundException>(
            () => _service.PlaceOrder(command));
        
        // Verify Add was never called
        _orderRepoMock.Verify(r => r.Add(It.IsAny<Order>()), Times.Never);
    }
    
    [Fact]
    public async Task PlaceOrder_DuplicateIdempotencyKey_ThrowsException()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        var customer = new Customer(customerId, "John Doe", Email.From("john@example.com"));
        
        _customerRepoMock
            .Setup(r => r.GetByIdAsync(customerId, It.IsAny<CancellationToken>()))
            .ReturnsAsync(customer);
        
        _orderRepoMock
            .Setup(r => r.ExistsWithIdempotencyKey("unique-key-123"))
            .Returns(true);  // Already exists
        
        var command = new PlaceOrderCommand(
            CustomerId: customerId,
            Items: new[] { new OrderItemDto("SKU-001", 1, 19.99m) },
            IdempotencyKey: "unique-key-123");
        
        // Act & Assert
        await Assert.ThrowsAsync<DuplicateOrderException>(
            () => _service.PlaceOrder(command));
    }
}

What Mocks Don't Test

Mocks verify that your service calls repository methods correctly. They do NOT verify that your repository actually works—that it maps entities correctly, that queries return expected results, or that transactions commit. For that, you need in-memory or integration tests on the repository itself.

In-Memory Fake Repositories

A fake repository is a fully functional in-memory implementation. It's more reusable than mocks and can be shared across tests. Fakes are especially useful when multiple tests need a realistic repository that maintains state across operations.

FakeOrderRepository.cs
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
/// <summary>
/// In-memory implementation of IOrderRepository for testing.
/// Maintains state like a real repository but uses Dictionary storage.
/// </summary>
public class FakeOrderRepository : IOrderRepository
{
    private readonly Dictionary<OrderId, Order> _orders = new();
    private long _nextOrderNumber = 1000;
    
    public Order? GetById(OrderId id)
    {
        return _orders.TryGetValue(id, out var order) ? order : null;
    }
    
    public Order GetByIdOrThrow(OrderId id)
    {
        return GetById(id) ?? throw new OrderNotFoundException(id);
    }
    
    public Task<Order?> GetByIdAsync(OrderId id, CancellationToken ct = default)
    {
        return Task.FromResult(GetById(id));
    }
    
    public void Add(Order order)
    {
        if (_orders.ContainsKey(order.Id))
            throw new DuplicateOrderException(order.Id, new Exception("Fake duplicate"));
        
        _orders[order.Id] = order;
    }
    
    public void Remove(Order order)
    {
        _orders.Remove(order.Id);
    }
    
    public IEnumerable<Order> FindOrdersReadyForShipment()
    {
        return _orders.Values
            .Where(o => 
                o.Status == OrderStatus.Paid &&
                o.StockVerified &&
                !o.ShippedAt.HasValue)
            .OrderBy(o => o.OrderDate)
            .ToList();
    }
    
    public IEnumerable<Order> FindOrdersPendingPayment()
    {
        return _orders.Values
            .Where(o => o.Status == OrderStatus.PendingPayment)
            .OrderBy(o => o.OrderDate)
            .ToList();
    }
    
    public IEnumerable<Order> FindForCustomer(CustomerId customerId)
    {
        return _orders.Values
            .Where(o => o.CustomerId == customerId)
            .OrderByDescending(o => o.OrderDate)
            .ToList();
    }
    
    public bool ExistsWithIdempotencyKey(string key)
    {
        return _orders.Values.Any(o => o.IdempotencyKey == key);
    }
    
    public OrderNumber GenerateNextOrderNumber()
    {
        return new OrderNumber(Interlocked.Increment(ref _nextOrderNumber));
    }
    
    // Test helper methods
    public void Clear() => _orders.Clear();
    public int Count => _orders.Count;
    public void Seed(IEnumerable<Order> orders)
    {
        foreach (var order in orders)
            _orders[order.Id] = order;
    }
}
 
// Usage in tests
public class OrderProcessingTests
{
    private readonly FakeOrderRepository _orderRepo;
    private readonly ShipmentService _service;
    
    public OrderProcessingTests()
    {
        _orderRepo = new FakeOrderRepository();
        _service = new ShipmentService(_orderRepo);
    }
    
    [Fact]
    public void ProcessReadyOrders_ShipsAllQualifyingOrders()
    {
        // Arrange - Seed with test data
        var readyOrder1 = CreatePaidVerifiedOrder();
        var readyOrder2 = CreatePaidVerifiedOrder();
        var pendingOrder = CreatePendingOrder();  // Should not be shipped
        
        _orderRepo.Seed(new[] { readyOrder1, readyOrder2, pendingOrder });
        
        // Act
        var shippedCount = _service.ProcessReadyOrders();
        
        // Assert
        Assert.Equal(2, shippedCount);
        Assert.True(_orderRepo.GetById(readyOrder1.Id)!.ShippedAt.HasValue);
        Assert.True(_orderRepo.GetById(readyOrder2.Id)!.ShippedAt.HasValue);
        Assert.False(_orderRepo.GetById(pendingOrder.Id)!.ShippedAt.HasValue);
    }
}

Fake Repository Best Practices

Keep fake logic simple—use LINQ on collections to mirror query behavior. Add test helper methods (Seed, Clear, Count) to make test setup easier. Throw the same exceptions as real repositories (DuplicateOrderException) to test error handling paths. Consider using the same fake in both unit and acceptance tests for consistency.

Testing with In-Memory Databases

To test the actual repository implementation —the one that uses Entity Framework, Hibernate, or another ORM—you need a database. In-memory databases provide fast, isolated testing without external dependencies. Options: EF Core InMemory Provider : Fastest, but doesn't enforce constraints or relationships SQLite In-Memory : Fast, enforces SQL semantics, good middle ground H2 (Java) : In-memory relational database, excellent Hibernate compatibility Testcontainers : Real database in Docker, slower but highest fidelity

InMemoryDatabaseTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
using Microsoft.EntityFrameworkCore;
using Xunit;
 
public class EfOrderRepositoryTests : IDisposable
{
    private readonly OrderingDbContext _context;
    private readonly EfOrderRepository _repository;
    
    public EfOrderRepositoryTests()
    {
        // Create in-memory database with unique name for test isolation
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;
        
        _context = new OrderingDbContext(options);
        _repository = new EfOrderRepository(_context, new NullLogger<EfOrderRepository>());
    }
    
    [Fact]
    public void GetById_ExistingOrder_ReturnsOrderWithLineItems()
    {
        // Arrange
        var order = CreateTestOrder();
        order.AddLineItem(new LineItem("SKU-001", 2, Money.FromDecimal(29.99m, "USD")));
        order.AddLineItem(new LineItem("SKU-002", 1, Money.FromDecimal(49.99m, "USD")));
        
        _context.Orders.Add(order);
        _context.SaveChanges();
        _context.ChangeTracker.Clear(); // Clear tracking to simulate fresh load
        
        // Act
        var retrieved = _repository.GetById(order.Id);
        
        // Assert
        Assert.NotNull(retrieved);
        Assert.Equal(order.Id, retrieved.Id);
        Assert.Equal(2, retrieved.LineItems.Count); // Verifies eager loading works
    }
    
    [Fact]
    public void GetById_NonExistingOrder_ReturnsNull()
    {
        // Arrange
        var nonExistentId = new OrderId(Guid.NewGuid());
        
        // Act
        var retrieved = _repository.GetById(nonExistentId);
        
        // Assert
        Assert.Null(retrieved);
    }
    
    [Fact]
    public void Add_NewOrder_PersistsSuccessfully()
    {
        // Arrange
        var order = CreateTestOrder();
        
        // Act
        _repository.Add(order);
        _context.SaveChanges();
        
        // Assert
        _context.ChangeTracker.Clear();
        var persisted = _context.Orders.Find(order.Id);
        Assert.NotNull(persisted);
    }
    
    [Fact]
    public void FindOrdersReadyForShipment_OnlyReturnsQualifyingOrders()
    {
        // Arrange
        var readyOrder = CreateTestOrder();
        readyOrder.MarkAsPaid();
        readyOrder.VerifyStock();
        
        var notPaidOrder = CreateTestOrder();
        // Status is still Pending
        
        var paidButNotVerifiedOrder = CreateTestOrder();
        paidButNotVerifiedOrder.MarkAsPaid();
        // Stock not verified
        
        var alreadyShippedOrder = CreateTestOrder();
        alreadyShippedOrder.MarkAsPaid();
        alreadyShippedOrder.VerifyStock();
        alreadyShippedOrder.MarkAsShipped();
        
        _context.Orders.AddRange(
            readyOrder, notPaidOrder, paidButNotVerifiedOrder, alreadyShippedOrder);
        _context.SaveChanges();
        
        // Act
        var readyOrders = _repository.FindOrdersReadyForShipment().ToList();
        
        // Assert
        Assert.Single(readyOrders);
        Assert.Equal(readyOrder.Id, readyOrders[0].Id);
    }
    
    [Fact]
    public void ExistsWithIdempotencyKey_DuplicateKey_ReturnsTrue()
    {
        // Arrange
        var order = CreateTestOrder(idempotencyKey: "unique-key-abc");
        _context.Orders.Add(order);
        _context.SaveChanges();
        
        // Act
        var exists = _repository.ExistsWithIdempotencyKey("unique-key-abc");
        
        // Assert
        Assert.True(exists);
    }
    
    public void Dispose()
    {
        _context.Dispose();
    }
    
    private static Order CreateTestOrder(string? idempotencyKey = null)
    {
        return new Order(
            new OrderId(Guid.NewGuid()),
            new CustomerId(Guid.NewGuid()),
            DateTime.UtcNow,
            idempotencyKey);
    }
}

In-Memory Database Limitations

EF Core's InMemory provider doesn't enforce foreign keys, unique constraints, or computed columns. For tests that depend on these features, use SQLite in-memory mode or Testcontainers with a real database. Always verify critical constraints in integration tests against a production-like database.

SQLite In-Memory for Higher Fidelity

SQLite in-memory mode provides a real SQL database that enforces constraints, making it superior to pure in-memory providers for catching issues early.

SqliteRepositoryTests.cs
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Xunit;
 
public class SqliteOrderRepositoryTests : IDisposable
{
    private readonly SqliteConnection _connection;
    private readonly OrderingDbContext _context;
    private readonly EfOrderRepository _repository;
    
    public SqliteOrderRepositoryTests()
    {
        // Create an in-memory SQLite database
        // Connection string uses DataSource=:memory: for in-memory database
        // Mode=ReadWriteCreate to allow creating tables
        _connection = new SqliteConnection("DataSource=:memory:");
        _connection.Open();  // Keep connection open for lifetime of test
        
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseSqlite(_connection)
            .Options;
        
        _context = new OrderingDbContext(options);
        _context.Database.EnsureCreated();  // Create tables from model
        
        _repository = new EfOrderRepository(_context, new NullLogger<EfOrderRepository>());
    }
    
    [Fact]
    public void Add_DuplicateOrderId_ThrowsException()
    {
        // Arrange
        var orderId = new OrderId(Guid.NewGuid());
        var order1 = CreateTestOrderWithId(orderId);
        var order2 = CreateTestOrderWithId(orderId);  // Same ID
        
        _repository.Add(order1);
        _context.SaveChanges();
        
        // Act & Assert
        _repository.Add(order2);
        
        // SQLite enforces primary key constraint
        Assert.Throws<DbUpdateException>(() => _context.SaveChanges());
    }
    
    [Fact]
    public void Update_ConcurrentModification_ThrowsConcurrencyException()
    {
        // Arrange - requires RowVersion/Concurrency token on Order entity
        var order = CreateTestOrder();
        _context.Orders.Add(order);
        _context.SaveChanges();
        
        // Simulate concurrent modification
        using (var context2 = CreateContext())
        {
            var orderCopy = context2.Orders.Find(order.Id)!;
            orderCopy.AddLineItem(new LineItem("SKU-X", 1, Money.USD(10)));
            context2.SaveChanges();
        }
        
        // Act - try to save from original context
        order.AddLineItem(new LineItem("SKU-Y", 1, Money.USD(20)));
        
        // Assert
        Assert.Throws<DbUpdateConcurrencyException>(() => _context.SaveChanges());
    }
    
    [Fact]
    public void FindForCustomer_OrderByDescendingDate_ReturnsCorrectOrder()
    {
        // Arrange
        var customerId = new CustomerId(Guid.NewGuid());
        
        var oldOrder = CreateTestOrder(customerId, DateTime.UtcNow.AddDays(-10));
        var middleOrder = CreateTestOrder(customerId, DateTime.UtcNow.AddDays(-5));
        var newOrder = CreateTestOrder(customerId, DateTime.UtcNow);
        
        // Add in random order
        _context.Orders.AddRange(middleOrder, newOrder, oldOrder);
        _context.SaveChanges();
        _context.ChangeTracker.Clear();
        
        // Act
        var orders = _repository.FindForCustomer(customerId).ToList();
        
        // Assert - should be newest first
        Assert.Equal(3, orders.Count);
        Assert.Equal(newOrder.Id, orders[0].Id);
        Assert.Equal(middleOrder.Id, orders[1].Id);
        Assert.Equal(oldOrder.Id, orders[2].Id);
    }
    
    private OrderingDbContext CreateContext()
    {
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseSqlite(_connection)
            .Options;
        return new OrderingDbContext(options);
    }
    
    public void Dispose()
    {
        _context.Dispose();
        _connection.Dispose();
    }
}

SQLite vs InMemory Provider

Use SQLite in-memory when you need: primary key constraints, foreign key enforcement, unique constraints, ordering/sorting verification, concurrency token testing. Use EF Core InMemory when you need maximum speed and only care about basic CRUD behavior.

Integration Testing with Real Databases

For maximum confidence, test against the same database engine you use in production. Testcontainers makes this manageable by spinning up real databases in Docker containers during test runs.

TestcontainersRepositoryTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
using Testcontainers.PostgreSql;
using Xunit;
 
public class PostgresOrderRepositoryTests : IAsyncLifetime
{
    private PostgreSqlContainer _postgres = null!;
    private OrderingDbContext _context = null!;
    private EfOrderRepository _repository = null!;
    
    public async Task InitializeAsync()
    {
        // Start a PostgreSQL container
        _postgres = new PostgreSqlBuilder()
            .WithImage("postgres:15-alpine")
            .WithDatabase("ordering_tests")
            .WithUsername("test")
            .WithPassword("test")
            .Build();
        
        await _postgres.StartAsync();
        
        // Configure EF Core to use the container
        var options = new DbContextOptionsBuilder<OrderingDbContext>()
            .UseNpgsql(_postgres.GetConnectionString())
            .Options;
        
        _context = new OrderingDbContext(options);
        await _context.Database.MigrateAsync();  // Apply migrations
        
        _repository = new EfOrderRepository(_context, new NullLogger<EfOrderRepository>());
    }
    
    public async Task DisposeAsync()
    {
        await _context.DisposeAsync();
        await _postgres.DisposeAsync();
    }
    
    [Fact]
    public async Task GenerateNextOrderNumber_UsesDatabaseSequence()
    {
        // Arrange - sequence is created by migration
        
        // Act
        var number1 = _repository.GenerateNextOrderNumber();
        var number2 = _repository.GenerateNextOrderNumber();
        var number3 = _repository.GenerateNextOrderNumber();
        
        // Assert - numbers are sequential
        Assert.True(number2.Value > number1.Value);
        Assert.True(number3.Value > number2.Value);
    }
    
    [Fact]
    public async Task Add_OrderWithLineItems_CreatesRecordsInAllTables()
    {
        // Arrange
        var order = CreateTestOrder();
        order.AddLineItem(new LineItem("SKU-001", 2, Money.FromDecimal(29.99m, "USD")));
        order.AddLineItem(new LineItem("SKU-002", 1, Money.FromDecimal(49.99m, "USD")));
        
        // Act
        _repository.Add(order);
        await _context.SaveChangesAsync();
        
        // Assert - verify using raw SQL that records exist
        await using var connection = new NpgsqlConnection(_postgres.GetConnectionString());
        await connection.OpenAsync();
        
        var orderCount = await connection.ExecuteScalarAsync<int>(
            "SELECT COUNT(*) FROM orders WHERE id = @id",
            new { id = order.Id.Value });
        Assert.Equal(1, orderCount);
        
        var lineItemCount = await connection.ExecuteScalarAsync<int>(
            "SELECT COUNT(*) FROM order_line_items WHERE order_id = @id",
            new { id = order.Id.Value });
        Assert.Equal(2, lineItemCount);
    }
    
    [Fact]
    public async Task FindOrdersReadyForShipment_UsesIndex()
    {
        // This test verifies that the query plan uses expected indexes
        // Important for performance-critical queries
        
        // Seed enough data to make indexes relevant
        for (int i = 0; i < 100; i++)
        {
            var order = CreateTestOrder();
            if (i % 10 == 0)
            {
                order.MarkAsPaid();
                order.VerifyStock();
            }
            _context.Orders.Add(order);
        }
        await _context.SaveChangesAsync();
        _context.ChangeTracker.Clear();
        
        // Use EXPLAIN ANALYZE to verify index usage
        await using var connection = new NpgsqlConnection(_postgres.GetConnectionString());
        await connection.OpenAsync();
        
        var explainResult = await connection.QueryFirstAsync<string>(
            @"EXPLAIN (ANALYZE, FORMAT TEXT) 
              SELECT * FROM orders 
              WHERE status = 'Paid' AND stock_verified = true AND shipped_at IS NULL");
        
        // Assert index is used (look for Index Scan vs Seq Scan)
        Assert.Contains("Index", explainResult);
    }
}

When to Use Real Database Tests

Use real database tests for: database sequences, stored procedures, database-specific features (JSON queries, full-text search), index usage verification, migration testing, and any behavior that differs between databases. These tests are slower but catch issues that in-memory tests miss.

What to Test (and What Not To)

Not every repository method needs exhaustive testing. Focus your testing effort where it provides the most value.

Worth Testing Thoroughly

•Custom Query Methods — FindOrdersReadyForShipment, SearchByName, etc. These contain business logic.
•Complex Loading — Verify eager loading works correctly for aggregates with multiple collections.
•Edge Cases — Empty results, null handling, large datasets, special characters.
•Ordering and Pagination — Verify sort order, offset, and limit behavior.
•Constraint Enforcement — Unique constraints, foreign keys (with SQL-based tests).
•Concurrency Handling — Optimistic locking, version conflicts.

Usually Not Worth Testing

•Simple CRUD — If GetById just calls Find(), you're testing the ORM, not your code.
•ORM Behavior — Entity Framework and Hibernate are well-tested. Don't re-test them.
•Framework Features — Change tracking, lazy loading mechanisms, etc.
•Generated Queries — Unless you suspect bugs, trust the query generation.
•Every Possible Input — Focus on boundaries and equivalence classes, not exhaustive inputs.

The Value Test

Before writing a repository test, ask: 'If this test passed but the code was wrong, would we have a production bug?' If yes, the test is valuable. If the test would only fail due to ORM misconfiguration (which would break everything), it might not be worth isolated testing.

Unit Testing Repositories

The Testing Challenge

The Repository Testing Spectrum

Testing with Mock and Fake Repositories

In-Memory Fake Repositories

Testing with In-Memory Databases

SQLite In-Memory for Higher Fidelity

Integration Testing with Real Databases

What to Test (and What Not To)

There’s more to understand.

Unit Testing Repositories

The Testing Challenge

The Repository Testing Spectrum

Testing with Mock and Fake Repositories

In-Memory Fake Repositories

Testing with In-Memory Databases

SQLite In-Memory for Higher Fidelity

Integration Testing with Real Databases

What to Test (and What Not To)

There’s more to understand.