Repository Implementation

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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

using Microsoft.EntityFrameworkCore;
 
/// <summary>
/// Complete Entity Framework Core implementation of IOrderRepository.
/// This is a production-ready example demonstrating key implementation patterns.
/// </summary>
public class EfOrderRepository : IOrderRepository
{
    private readonly OrderingDbContext _context;
    private readonly ILogger<EfOrderRepository> _logger;
    
    public EfOrderRepository(
        OrderingDbContext context, 
        ILogger<EfOrderRepository> logger)
    {
        _context = context ?? throw new ArgumentNullException(nameof(context));
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
    }
    
    /// <summary>
    /// Retrieves an order by its ID, including all aggregate children.
    /// </summary>
    public Order? GetById(OrderId id)
    {
        // Load the complete aggregate - Order with its LineItems and Addresses
        // This ensures the aggregate is always in a consistent state
        return _context.Orders
            .Include(o => o.LineItems)
                .ThenInclude(li => li.ProductSnapshot)
            .Include(o => o.ShippingAddress)
            .Include(o => o.BillingAddress)
            .FirstOrDefault(o => o.Id == id);
    }
    
    /// <summary>
    /// Retrieves an order or throws if not found.
    /// Use when the order is expected to exist (business logic guarantee).
    /// </summary>
    public Order GetByIdOrThrow(OrderId id)
    {
        var order = GetById(id);
        if (order == null)
        {
            _logger.LogWarning("Order {OrderId} not found", id);
            throw new OrderNotFoundException(id);
        }
        return order;
    }
    
    /// <summary>
    /// Async version of GetById - preferred for web applications.
    /// </summary>
    public async Task<Order?> GetByIdAsync(
        OrderId id, 
        CancellationToken ct = default)
    {
        return await _context.Orders
            .Include(o => o.LineItems)
                .ThenInclude(li => li.ProductSnapshot)
            .Include(o => o.ShippingAddress)
            .Include(o => o.BillingAddress)
            .FirstOrDefaultAsync(o => o.Id == id, ct);
    }
    
    /// <summary>
    /// Adds a new order to the repository.
    /// The order is added to the change tracker and will be persisted
    /// when SaveChanges is called on the DbContext (Unit of Work).
    /// </summary>
    public void Add(Order order)
    {
        ArgumentNullException.ThrowIfNull(order);
        
        _context.Orders.Add(order);
        _logger.LogDebug("Order {OrderId} added to change tracker", order.Id);
    }
    
    /// <summary>
    /// Marks an order for removal.
    /// The delete will occur when SaveChanges is called.
    /// </summary>
    public void Remove(Order order)
    {
        ArgumentNullException.ThrowIfNull(order);
        
        // EF Core handles cascade deletes based on configuration
        _context.Orders.Remove(order);
        _logger.LogDebug("Order {OrderId} marked for removal", order.Id);
    }
    
    /// <summary>
    /// Finds orders that are ready for shipment processing.
    /// Encapsulates the business rules for "ready for shipment".
    /// </summary>
    public IEnumerable<Order> FindOrdersReadyForShipment()
    {
        return _context.Orders
            .Include(o => o.LineItems)
            .Include(o => o.ShippingAddress)
            .Where(o => 
                o.Status == OrderStatus.Paid &&
                o.StockVerified &&
                !o.ShippedAt.HasValue)
            .OrderBy(o => o.OrderDate) // FIFO processing
            .ToList();
    }
    
    /// <summary>
    /// Finds orders awaiting payment.
    /// </summary>
    public IEnumerable<Order> FindOrdersPendingPayment()
    {
        return _context.Orders
            .Where(o => o.Status == OrderStatus.PendingPayment)
            .OrderBy(o => o.OrderDate)
            .ToList();
    }
    
    /// <summary>
    /// Finds all orders for a specific customer.
    /// </summary>
    public IEnumerable<Order> FindForCustomer(CustomerId customerId)
    {
        return _context.Orders
            .Include(o => o.LineItems)
            .Where(o => o.CustomerId == customerId)
            .OrderByDescending(o => o.OrderDate)
            .ToList();
    }
    
    /// <summary>
    /// Checks if an order exists with the given idempotency key.
    /// Used to prevent duplicate order creation.
    /// </summary>
    public bool ExistsWithIdempotencyKey(string key)
    {
        return _context.Orders.Any(o => o.IdempotencyKey == key);
    }
    
    /// <summary>
    /// Generates the next order number.
    /// Uses a database sequence for atomicity.
    /// </summary>
    public OrderNumber GenerateNextOrderNumber()
    {
        // Option 1: Use a database sequence (PostgreSQL, Oracle, SQL Server 2012+)
        var nextVal = _context.Database
            .SqlQuery<long>($"SELECT NEXT VALUE FOR OrderNumberSequence")
            .Single();
        return new OrderNumber(nextVal);
        
        // Option 2: For databases without sequences, use a dedicated table
        // This requires careful transaction handling to avoid gaps/duplicates
    }
    
    /// <summary>
    /// Paged query with total count for UI listing.
    /// </summary>
    public async Task<PagedResult<Order>> GetPagedAsync(
        int pageNumber, 
        int pageSize,
        CancellationToken ct = default)
    {
        var query = _context.Orders.AsQueryable();
        
        var totalCount = await query.CountAsync(ct);
        
        var items = await query
            .OrderByDescending(o => o.OrderDate)
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync(ct);
        
        return new PagedResult<Order>(items, totalCount, pageNumber, pageSize);
    }
}

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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

import javax.persistence.*;
import java.util.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
/**
 * Complete JPA/Hibernate implementation of OrderRepository.
 * Production-ready example demonstrating key implementation patterns.
 */
@Repository
public class JpaOrderRepository implements OrderRepository {
    
    private static final Logger log = LoggerFactory.getLogger(JpaOrderRepository.class);
    
    @PersistenceContext
    private EntityManager entityManager;
    
    /**
     * Retrieves an order by its ID, including all aggregate children.
     * Uses JPQL with JOIN FETCH to load the complete aggregate.
     */
    @Override
    public Optional<Order> findById(OrderId id) {
        try {
            Order order = entityManager
                .createQuery("""
                    SELECT DISTINCT o FROM Order o
                    LEFT JOIN FETCH o.lineItems li
                    LEFT JOIN FETCH li.productSnapshot
                    LEFT JOIN FETCH o.shippingAddress
                    LEFT JOIN FETCH o.billingAddress
                    WHERE o.id = :id
                    """, Order.class)
                .setParameter("id", id)
                .getSingleResult();
            return Optional.of(order);
        } catch (NoResultException e) {
            return Optional.empty();
        }
    }
    
    /**
     * Retrieves an order or throws if not found.
     */
    @Override
    public Order findByIdOrThrow(OrderId id) {
        return findById(id)
            .orElseThrow(() -> {
                log.warn("Order {} not found", id);
                return new OrderNotFoundException(id);
            });
    }
    
    /**
     * Saves (creates or updates) an order.
     * JPA merge handles both insert and update.
     */
    @Override
    @Transactional
    public Order save(Order order) {
        Objects.requireNonNull(order, "Order cannot be null");
        
        if (order.getId() == null) {
            entityManager.persist(order);
            log.debug("Order persisted (new)");
            return order;
        } else {
            Order merged = entityManager.merge(order);
            log.debug("Order {} merged", order.getId());
            return merged;
        }
    }
    
    /**
     * Removes an order from the persistence context.
     */
    @Override
    @Transactional
    public void delete(Order order) {
        Objects.requireNonNull(order, "Order cannot be null");
        
        // Ensure entity is managed before removing
        Order managed = entityManager.contains(order) 
            ? order 
            : entityManager.merge(order);
        entityManager.remove(managed);
        log.debug("Order {} marked for deletion", order.getId());
    }
    
    /**
     * Finds orders ready for shipment.
     */
    @Override
    public List<Order> findOrdersReadyForShipment() {
        return entityManager
            .createQuery("""
                SELECT DISTINCT o FROM Order o
                LEFT JOIN FETCH o.lineItems
                LEFT JOIN FETCH o.shippingAddress
                WHERE o.status = :status
                  AND o.stockVerified = true
                  AND o.shippedAt IS NULL
                ORDER BY o.orderDate
                """, Order.class)
            .setParameter("status", OrderStatus.PAID)
            .getResultList();
    }
    
    /**
     * Finds orders for a specific customer with pagination.
     */
    @Override
    public Page<Order> findByCustomerId(CustomerId customerId, Pageable pageable) {
        // Count query for total
        Long total = entityManager
            .createQuery(
                "SELECT COUNT(o) FROM Order o WHERE o.customerId = :customerId",
                Long.class)
            .setParameter("customerId", customerId)
            .getSingleResult();
        
        // Data query with pagination
        List<Order> orders = entityManager
            .createQuery("""
                SELECT o FROM Order o
                WHERE o.customerId = :customerId
                ORDER BY o.orderDate DESC
                """, Order.class)
            .setParameter("customerId", customerId)
            .setFirstResult((int) pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();
        
        return new PageImpl<>(orders, pageable, total);
    }
    
    /**
     * Checks for duplicate order by idempotency key.
     */
    @Override
    public boolean existsByIdempotencyKey(String key) {
        Long count = entityManager
            .createQuery(
                "SELECT COUNT(o) FROM Order o WHERE o.idempotencyKey = :key",
                Long.class)
            .setParameter("key", key)
            .getSingleResult();
        return count > 0;
    }
    
    /**
     * Generates next order number using database sequence.
     */
    @Override
    public OrderNumber generateNextOrderNumber() {
        // Native query for sequence (PostgreSQL example)
        BigInteger nextVal = (BigInteger) entityManager
            .createNativeQuery("SELECT nextval('order_number_seq')")
            .getSingleResult();
        return new OrderNumber(nextVal.longValue());
    }
}

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

public class EfOrderRepository : IOrderRepository
{
    // STRATEGY 1: Eager Loading with Include
    // Best for: Consistent aggregate access where children are always needed
    public Order? GetById_EagerLoading(OrderId id)
    {
        return _context.Orders
            .Include(o => o.LineItems)
                .ThenInclude(li => li.ProductSnapshot)
            .Include(o => o.ShippingAddress)
            .Include(o => o.BillingAddress)
            .FirstOrDefault(o => o.Id == id);
        
        // Generates a single query with JOINs
        // Good for small aggregates, problematic with many includes
    }
    
    // STRATEGY 2: Split Queries
    // Best for: Aggregates with multiple collections to avoid Cartesian product
    public Order? GetById_SplitQuery(OrderId id)
    {
        return _context.Orders
            .Include(o => o.LineItems)
            .Include(o => o.ShippingAddress)
            .Include(o => o.BillingAddress)
            .AsSplitQuery() // EF Core 5+ feature
            .FirstOrDefault(o => o.Id == id);
        
        // Generates multiple queries (one per include)
        // Avoids Cartesian explosion: O(n*m) rows → O(n+m) rows
    }
    
    // STRATEGY 3: Explicit Loading
    // Best for: Conditional loading based on business rules
    public Order? GetById_ExplicitLoading(OrderId id, bool includeLineItems)
    {
        var order = _context.Orders.Find(id);
        
        if (order != null)
        {
            // Always load addresses (small, always needed)
            _context.Entry(order)
                .Reference(o => o.ShippingAddress)
                .Load();
            
            // Conditionally load line items
            if (includeLineItems)
            {
                _context.Entry(order)
                    .Collection(o => o.LineItems)
                    .Query()
                    .Include(li => li.ProductSnapshot)
                    .Load();
            }
        }
        
        return order;
    }
    
    // STRATEGY 4: Projection for Read-Only Scenarios
    // Best for: Queries, reporting, APIs where full aggregate isn't needed
    public OrderSummaryDto? GetOrderSummary(OrderId id)
    {
        return _context.Orders
            .Where(o => o.Id == id)
            .Select(o => new OrderSummaryDto
            {
                OrderId = o.Id.Value,
                OrderNumber = o.OrderNumber.Value,
                CustomerName = o.CustomerName,
                TotalAmount = o.TotalAmount.Amount,
                Currency = o.TotalAmount.Currency,
                Status = o.Status.ToString(),
                ItemCount = o.LineItems.Count,
                OrderDate = o.OrderDate
            })
            .FirstOrDefault();
        
        // Generates a SELECT with only needed columns
        // No entity tracking overhead, minimal data transfer
    }
    
    // STRATEGY 5: Batch Loading for Collections
    // Best for: Loading orders for multiple customers efficiently
    public Dictionary<CustomerId, List<Order>> GetOrdersByCustomers(
        IEnumerable<CustomerId> customerIds)
    {
        var orders = _context.Orders
            .Include(o => o.LineItems)
            .Where(o => customerIds.Contains(o.CustomerId))
            .ToList();
        
        return orders
            .GroupBy(o => o.CustomerId)
            .ToDictionary(g => g.Key, g => g.ToList());
        
        // Single query fetches all orders for all customers
        // Avoids N queries for N customers
    }
}

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

// PATTERN 1: DbContext-scoped repositories (recommended)
// The DbContext IS the Unit of Work in EF Core
 
public class OrderingUnitOfWork : IUnitOfWork
{
    private readonly OrderingDbContext _context;
    
    // Lazy-loaded repositories share the same DbContext
    private IOrderRepository? _orders;
    private ICustomerRepository? _customers;
    
    public OrderingUnitOfWork(OrderingDbContext context)
    {
        _context = context;
    }
    
    public IOrderRepository Orders => 
        _orders ??= new EfOrderRepository(_context, _logger);
    
    public ICustomerRepository Customers => 
        _customers ??= new EfCustomerRepository(_context, _logger);
    
    public async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        return await _context.SaveChangesAsync(ct);
    }
    
    public async Task BeginTransactionAsync()
    {
        await _context.Database.BeginTransactionAsync();
    }
    
    public async Task CommitAsync()
    {
        await _context.Database.CommitTransactionAsync();
    }
    
    public async Task RollbackAsync()
    {
        await _context.Database.RollbackTransactionAsync();
    }
    
    public void Dispose()
    {
        _context.Dispose();
    }
}
 
// PATTERN 2: Usage in Application Service
public class PlaceOrderService
{
    private readonly IUnitOfWork _uow;
    
    public PlaceOrderService(IUnitOfWork uow)
    {
        _uow = uow;
    }
    
    public async Task<OrderId> PlaceOrder(PlaceOrderCommand command)
    {
        try
        {
            await _uow.BeginTransactionAsync();
            
            // Multiple repository operations in same transaction
            var customer = await _uow.Customers.GetByIdAsync(command.CustomerId);
            if (customer == null)
                throw new CustomerNotFoundException(command.CustomerId);
            
            var order = customer.PlaceOrder(command.Items);
            _uow.Orders.Add(order);
            
            // All changes saved atomically
            await _uow.SaveChangesAsync();
            await _uow.CommitAsync();
            
            return order.Id;
        }
        catch
        {
            await _uow.RollbackAsync();
            throw;
        }
    }
}
 
// PATTERN 3: Scoped DbContext with DI (ASP.NET Core)
// In Startup.cs / Program.cs:
services.AddDbContext<OrderingDbContext>(options =>
    options.UseSqlServer(connectionString));
 
services.AddScoped<IOrderRepository, EfOrderRepository>();
services.AddScoped<IUnitOfWork, OrderingUnitOfWork>();
 
// DbContext is scoped per request, so all repository operations
// within a request share the same context and transaction.

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

public class EfOrderRepository : IOrderRepository
{
    public void Add(Order order)
    {
        try
        {
            _context.Orders.Add(order);
        }
        catch (DbUpdateException ex) when (IsUniqueConstraintViolation(ex))
        {
            // Translate database constraint violation to domain exception
            _logger.LogWarning(ex, "Duplicate order detected: {OrderId}", order.Id);
            throw new DuplicateOrderException(order.Id, ex);
        }
        catch (DbUpdateConcurrencyException ex)
        {
            // Translate optimistic concurrency failure
            _logger.LogWarning(ex, "Concurrency conflict for order: {OrderId}", order.Id);
            throw new OrderConcurrencyException(order.Id, ex);
        }
    }
    
    public Order GetByIdOrThrow(OrderId id)
    {
        try
        {
            var order = _context.Orders
                .Include(o => o.LineItems)
                .FirstOrDefault(o => o.Id == id);
            
            if (order == null)
            {
                throw new OrderNotFoundException(id);
            }
            
            return order;
        }
        catch (SqlException ex) when (IsConnectionError(ex))
        {
            _logger.LogError(ex, "Database connection failed");
            throw new PersistenceUnavailableException(
                "Unable to connect to order database", ex);
        }
        catch (TimeoutException ex)
        {
            _logger.LogError(ex, "Database query timed out for order {OrderId}", id);
            throw new PersistenceTimeoutException(
                $"Query for order {id} timed out", ex);
        }
    }
    
    private static bool IsUniqueConstraintViolation(DbUpdateException ex)
    {
        return ex.InnerException is SqlException sqlEx && 
               (sqlEx.Number == 2601 || sqlEx.Number == 2627);
        // SQL Server unique constraint violation error codes
    }
    
    private static bool IsConnectionError(SqlException ex)
    {
        // SQL Server connection-related error codes
        return new[] { 53, 10054, 10061, 40143, 40197, 40501, 40613 }
            .Contains(ex.Number);
    }
}
 
// Domain exceptions that can be caught by application services
public class OrderNotFoundException : DomainException
{
    public OrderId OrderId { get; }
    
    public OrderNotFoundException(OrderId orderId)
        : base($"Order with ID {orderId} was not found")
    {
        OrderId = orderId;
    }
}
 
public class DuplicateOrderException : DomainException
{
    public OrderId OrderId { get; }
    
    public DuplicateOrderException(OrderId orderId, Exception inner)
        : base($"Order with ID {orderId} already exists", inner)
    {
        OrderId = orderId;
    }
}
 
public class PersistenceUnavailableException : InfrastructureException
{
    public PersistenceUnavailableException(string message, Exception inner)
        : base(message, inner) { }
}

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

public class EfOrderRepository : IOrderRepository
{
    // Compiled query - defined once, reused for every call
    private static readonly Func<OrderingDbContext, OrderId, Order?> 
        GetOrderByIdCompiled = 
            EF.CompileQuery((OrderingDbContext ctx, OrderId id) =>
                ctx.Orders
                    .Include(o => o.LineItems)
                    .FirstOrDefault(o => o.Id == id));
    
    public Order? GetById(OrderId id)
    {
        // Use compiled query - avoids expression tree compilation
        return GetOrderByIdCompiled(_context, id);
    }
    
    // No-tracking for read-only list operations
    public IEnumerable<OrderSummaryDto> GetRecentOrderSummaries(int count)
    {
        return _context.Orders
            .AsNoTracking()  // Skip change tracking overhead
            .OrderByDescending(o => o.OrderDate)
            .Take(count)
            .Select(o => new OrderSummaryDto { /* ... */ })
            .ToList();
    }
    
    // Bulk update - single query instead of loading all entities
    public int MarkOrdersAsShipped(IEnumerable<OrderId> orderIds)
    {
        // EF Core 7+ ExecuteUpdate
        return _context.Orders
            .Where(o => orderIds.Contains(o.Id))
            .ExecuteUpdate(setters => setters
                .SetProperty(o => o.Status, OrderStatus.Shipped)
                .SetProperty(o => o.ShippedAt, DateTime.UtcNow));
        
        // Generates single UPDATE statement, no entities loaded
    }
    
    // Streamed results for large datasets
    public async IAsyncEnumerable<Order> StreamAllOrders(
        [EnumeratorCancellation] CancellationToken ct)
    {
        // AsAsyncEnumerable streams results instead of loading all to memory
        await foreach (var order in _context.Orders
            .AsNoTracking()
            .AsAsyncEnumerable()
            .WithCancellation(ct))
        {
            yield return order;
        }
    }
}