1// Repository Implementation Template for .NET 8+
2// Demonstrates both Dapper (performance) and EF Core (convenience) patterns
3 
4using System.Data;
5using Dapper;
6using Microsoft.Data.SqlClient;
7using Microsoft.EntityFrameworkCore;
8using Microsoft.Extensions.Logging;
9 
10namespace YourNamespace.Infrastructure.Data;
11 
12#region Interfaces
13 
14public interface IProductRepository
15{
16    Task<Product?> GetByIdAsync(string id, CancellationToken ct = default);
17    Task<Product?> GetBySkuAsync(string sku, CancellationToken ct = default);
18    Task<(IReadOnlyList<Product> Items, int TotalCount)> SearchAsync(ProductSearchRequest request, CancellationToken ct = default);
19    Task<Product> CreateAsync(Product product, CancellationToken ct = default);
20    Task<Product> UpdateAsync(Product product, CancellationToken ct = default);
21    Task DeleteAsync(string id, CancellationToken ct = default);
22    Task<IReadOnlyList<Product>> GetByIdsAsync(IEnumerable<string> ids, CancellationToken ct = default);
23}
24 
25#endregion
26 
27#region Dapper Implementation (High Performance)
28 
29public class DapperProductRepository : IProductRepository
30{
31    private readonly IDbConnection _connection;
32    private readonly ILogger<DapperProductRepository> _logger;
33 
34    public DapperProductRepository(
35        IDbConnection connection,
36        ILogger<DapperProductRepository> logger)
37    {
38        _connection = connection;
39        _logger = logger;
40    }
41 
42    public async Task<Product?> GetByIdAsync(string id, CancellationToken ct = default)
43    {
44        const string sql = """
45            SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, UpdatedAt
46            FROM Products
47            WHERE Id = @Id AND IsDeleted = 0
48            """;
49 
50        return await _connection.QueryFirstOrDefaultAsync<Product>(
51            new CommandDefinition(sql, new { Id = id }, cancellationToken: ct));
52    }
53 
54    public async Task<Product?> GetBySkuAsync(string sku, CancellationToken ct = default)
55    {
56        const string sql = """
57            SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, UpdatedAt
58            FROM Products
59            WHERE Sku = @Sku AND IsDeleted = 0
60            """;
61 
62        return await _connection.QueryFirstOrDefaultAsync<Product>(
63            new CommandDefinition(sql, new { Sku = sku }, cancellationToken: ct));
64    }
65 
66    public async Task<(IReadOnlyList<Product> Items, int TotalCount)> SearchAsync(
67        ProductSearchRequest request, 
68        CancellationToken ct = default)
69    {
70        var whereClauses = new List<string> { "IsDeleted = 0" };
71        var parameters = new DynamicParameters();
72 
73        // Build dynamic WHERE clause
74        if (!string.IsNullOrWhiteSpace(request.SearchTerm))
75        {
76            whereClauses.Add("(Name LIKE @SearchTerm OR Sku LIKE @SearchTerm)");
77            parameters.Add("SearchTerm", $"%{request.SearchTerm}%");
78        }
79 
80        if (request.CategoryId.HasValue)
81        {
82            whereClauses.Add("CategoryId = @CategoryId");
83            parameters.Add("CategoryId", request.CategoryId.Value);
84        }
85 
86        if (request.MinPrice.HasValue)
87        {
88            whereClauses.Add("Price >= @MinPrice");
89            parameters.Add("MinPrice", request.MinPrice.Value);
90        }
91 
92        if (request.MaxPrice.HasValue)
93        {
94            whereClauses.Add("Price <= @MaxPrice");
95            parameters.Add("MaxPrice", request.MaxPrice.Value);
96        }
97 
98        var whereClause = string.Join(" AND ", whereClauses);
99        var page = request.Page ?? 1;
100        var pageSize = request.PageSize ?? 50;
101        var offset = (page - 1) * pageSize;
102 
103        parameters.Add("Offset", offset);
104        parameters.Add("PageSize", pageSize);
105 
106        // Use multi-query for count + data in single roundtrip
107        var sql = $"""
108            -- Count query
109            SELECT COUNT(*) FROM Products WHERE {whereClause};
110            
111            -- Data query with pagination
112            SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, UpdatedAt
113            FROM Products
114            WHERE {whereClause}
115            ORDER BY Name
116            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
117            """;
118 
119        using var multi = await _connection.QueryMultipleAsync(
120            new CommandDefinition(sql, parameters, cancellationToken: ct));
121 
122        var totalCount = await multi.ReadSingleAsync<int>();
123        var items = (await multi.ReadAsync<Product>()).ToList();
124 
125        return (items, totalCount);
126    }
127 
128    public async Task<Product> CreateAsync(Product product, CancellationToken ct = default)
129    {
130        const string sql = """
131            INSERT INTO Products (Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, IsDeleted)
132            VALUES (@Id, @Name, @Sku, @Price, @CategoryId, @Stock, @CreatedAt, 0);
133            
134            SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, UpdatedAt
135            FROM Products WHERE Id = @Id;
136            """;
137 
138        return await _connection.QuerySingleAsync<Product>(
139            new CommandDefinition(sql, product, cancellationToken: ct));
140    }
141 
142    public async Task<Product> UpdateAsync(Product product, CancellationToken ct = default)
143    {
144        const string sql = """
145            UPDATE Products
146            SET Name = @Name,
147                Sku = @Sku,
148                Price = @Price,
149                CategoryId = @CategoryId,
150                Stock = @Stock,
151                UpdatedAt = @UpdatedAt
152            WHERE Id = @Id AND IsDeleted = 0;
153            
154            SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, UpdatedAt
155            FROM Products WHERE Id = @Id;
156            """;
157 
158        return await _connection.QuerySingleAsync<Product>(
159            new CommandDefinition(sql, product, cancellationToken: ct));
160    }
161 
162    public async Task DeleteAsync(string id, CancellationToken ct = default)
163    {
164        const string sql = """
165            UPDATE Products
166            SET IsDeleted = 1, UpdatedAt = @UpdatedAt
167            WHERE Id = @Id
168            """;
169 
170        await _connection.ExecuteAsync(
171            new CommandDefinition(sql, new { Id = id, UpdatedAt = DateTime.UtcNow }, cancellationToken: ct));
172    }
173 
174    public async Task<IReadOnlyList<Product>> GetByIdsAsync(
175        IEnumerable<string> ids, 
176        CancellationToken ct = default)
177    {
178        var idList = ids.ToList();
179        if (idList.Count == 0)
180            return Array.Empty<Product>();
181 
182        const string sql = """
183            SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt, UpdatedAt
184            FROM Products
185            WHERE Id IN @Ids AND IsDeleted = 0
186            """;
187 
188        var results = await _connection.QueryAsync<Product>(
189            new CommandDefinition(sql, new { Ids = idList }, cancellationToken: ct));
190 
191        return results.ToList();
192    }
193}
194 
195#endregion
196 
197#region EF Core Implementation (Rich Domain Models)
198 
199public class EfCoreProductRepository : IProductRepository
200{
201    private readonly AppDbContext _context;
202    private readonly ILogger<EfCoreProductRepository> _logger;
203 
204    public EfCoreProductRepository(
205        AppDbContext context,
206        ILogger<EfCoreProductRepository> logger)
207    {
208        _context = context;
209        _logger = logger;
210    }
211 
212    public async Task<Product?> GetByIdAsync(string id, CancellationToken ct = default)
213    {
214        return await _context.Products
215            .AsNoTracking()
216            .FirstOrDefaultAsync(p => p.Id == id, ct);
217    }
218 
219    public async Task<Product?> GetBySkuAsync(string sku, CancellationToken ct = default)
220    {
221        return await _context.Products
222            .AsNoTracking()
223            .FirstOrDefaultAsync(p => p.Sku == sku, ct);
224    }
225 
226    public async Task<(IReadOnlyList<Product> Items, int TotalCount)> SearchAsync(
227        ProductSearchRequest request, 
228        CancellationToken ct = default)
229    {
230        var query = _context.Products.AsNoTracking();
231 
232        // Apply filters
233        if (!string.IsNullOrWhiteSpace(request.SearchTerm))
234        {
235            var term = request.SearchTerm.ToLower();
236            query = query.Where(p => 
237                p.Name.ToLower().Contains(term) || 
238                p.Sku.ToLower().Contains(term));
239        }
240 
241        if (request.CategoryId.HasValue)
242            query = query.Where(p => p.CategoryId == request.CategoryId.Value);
243 
244        if (request.MinPrice.HasValue)
245            query = query.Where(p => p.Price >= request.MinPrice.Value);
246 
247        if (request.MaxPrice.HasValue)
248            query = query.Where(p => p.Price <= request.MaxPrice.Value);
249 
250        // Get count before pagination
251        var totalCount = await query.CountAsync(ct);
252 
253        // Apply pagination
254        var page = request.Page ?? 1;
255        var pageSize = request.PageSize ?? 50;
256 
257        var items = await query
258            .OrderBy(p => p.Name)
259            .Skip((page - 1) * pageSize)
260            .Take(pageSize)
261            .ToListAsync(ct);
262 
263        return (items, totalCount);
264    }
265 
266    public async Task<Product> CreateAsync(Product product, CancellationToken ct = default)
267    {
268        _context.Products.Add(product);
269        await _context.SaveChangesAsync(ct);
270        return product;
271    }
272 
273    public async Task<Product> UpdateAsync(Product product, CancellationToken ct = default)
274    {
275        _context.Products.Update(product);
276        await _context.SaveChangesAsync(ct);
277        return product;
278    }
279 
280    public async Task DeleteAsync(string id, CancellationToken ct = default)
281    {
282        var product = await _context.Products.FindAsync(new object[] { id }, ct);
283        if (product != null)
284        {
285            product.IsDeleted = true;
286            product.UpdatedAt = DateTime.UtcNow;
287            await _context.SaveChangesAsync(ct);
288        }
289    }
290 
291    public async Task<IReadOnlyList<Product>> GetByIdsAsync(
292        IEnumerable<string> ids, 
293        CancellationToken ct = default)
294    {
295        var idList = ids.ToList();
296        if (idList.Count == 0)
297            return Array.Empty<Product>();
298 
299        return await _context.Products
300            .AsNoTracking()
301            .Where(p => idList.Contains(p.Id))
302            .ToListAsync(ct);
303    }
304}
305 
306#endregion
307 
308#region DbContext Configuration
309 
310public class AppDbContext : DbContext
311{
312    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
313 
314    public DbSet<Product> Products => Set<Product>();
315    public DbSet<Category> Categories => Set<Category>();
316    public DbSet<Order> Orders => Set<Order>();
317    public DbSet<OrderItem> OrderItems => Set<OrderItem>();
318 
319    protected override void OnModelCreating(ModelBuilder modelBuilder)
320    {
321        // Apply all configurations from assembly
322        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
323 
324        // Global query filter for soft delete
325        modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);
326    }
327}
328 
329public class ProductConfiguration : IEntityTypeConfiguration<Product>
330{
331    public void Configure(EntityTypeBuilder<Product> builder)
332    {
333        builder.ToTable("Products");
334 
335        builder.HasKey(p => p.Id);
336        builder.Property(p => p.Id).HasMaxLength(40);
337 
338        builder.Property(p => p.Name)
339            .HasMaxLength(200)
340            .IsRequired();
341 
342        builder.Property(p => p.Sku)
343            .HasMaxLength(50)
344            .IsRequired();
345 
346        builder.Property(p => p.Price)
347            .HasPrecision(18, 2);
348 
349        // Indexes
350        builder.HasIndex(p => p.Sku).IsUnique();
351        builder.HasIndex(p => p.CategoryId);
352        builder.HasIndex(p => new { p.CategoryId, p.Name });
353 
354        // Relationships
355        builder.HasOne(p => p.Category)
356            .WithMany(c => c.Products)
357            .HasForeignKey(p => p.CategoryId);
358    }
359}
360 
361#endregion
362 
363#region Advanced Patterns
364 
365/// <summary>
366/// Unit of Work pattern for coordinating multiple repositories
367/// </summary>
368public interface IUnitOfWork : IDisposable
369{
370    IProductRepository Products { get; }
371    IOrderRepository Orders { get; }
372    Task<int> SaveChangesAsync(CancellationToken ct = default);
373    Task BeginTransactionAsync(CancellationToken ct = default);
374    Task CommitAsync(CancellationToken ct = default);
375    Task RollbackAsync(CancellationToken ct = default);
376}
377 
378public class UnitOfWork : IUnitOfWork
379{
380    private readonly AppDbContext _context;
381    private IDbContextTransaction? _transaction;
382 
383    public IProductRepository Products { get; }
384    public IOrderRepository Orders { get; }
385 
386    public UnitOfWork(
387        AppDbContext context,
388        IProductRepository products,
389        IOrderRepository orders)
390    {
391        _context = context;
392        Products = products;
393        Orders = orders;
394    }
395 
396    public async Task<int> SaveChangesAsync(CancellationToken ct = default)
397        => await _context.SaveChangesAsync(ct);
398 
399    public async Task BeginTransactionAsync(CancellationToken ct = default)
400    {
401        _transaction = await _context.Database.BeginTransactionAsync(ct);
402    }
403 
404    public async Task CommitAsync(CancellationToken ct = default)
405    {
406        if (_transaction != null)
407        {
408            await _transaction.CommitAsync(ct);
409            await _transaction.DisposeAsync();
410            _transaction = null;
411        }
412    }
413 
414    public async Task RollbackAsync(CancellationToken ct = default)
415    {
416        if (_transaction != null)
417        {
418            await _transaction.RollbackAsync(ct);
419            await _transaction.DisposeAsync();
420            _transaction = null;
421        }
422    }
423 
424    public void Dispose()
425    {
426        _transaction?.Dispose();
427        _context.Dispose();
428    }
429}
430 
431/// <summary>
432/// Specification pattern for complex queries
433/// </summary>
434public interface ISpecification<T>
435{
436    Expression<Func<T, bool>> Criteria { get; }
437    List<Expression<Func<T, object>>> Includes { get; }
438    List<string> IncludeStrings { get; }
439    Expression<Func<T, object>>? OrderBy { get; }
440    Expression<Func<T, object>>? OrderByDescending { get; }
441    int? Take { get; }
442    int? Skip { get; }
443}
444 
445public abstract class BaseSpecification<T> : ISpecification<T>
446{
447    public Expression<Func<T, bool>> Criteria { get; private set; } = _ => true;
448    public List<Expression<Func<T, object>>> Includes { get; } = new();
449    public List<string> IncludeStrings { get; } = new();
450    public Expression<Func<T, object>>? OrderBy { get; private set; }
451    public Expression<Func<T, object>>? OrderByDescending { get; private set; }
452    public int? Take { get; private set; }
453    public int? Skip { get; private set; }
454 
455    protected void AddCriteria(Expression<Func<T, bool>> criteria) => Criteria = criteria;
456    protected void AddInclude(Expression<Func<T, object>> include) => Includes.Add(include);
457    protected void AddInclude(string include) => IncludeStrings.Add(include);
458    protected void ApplyOrderBy(Expression<Func<T, object>> orderBy) => OrderBy = orderBy;
459    protected void ApplyOrderByDescending(Expression<Func<T, object>> orderBy) => OrderByDescending = orderBy;
460    protected void ApplyPaging(int skip, int take) { Skip = skip; Take = take; }
461}
462 
463// Example specification
464public class ProductsByCategorySpec : BaseSpecification<Product>
465{
466    public ProductsByCategorySpec(int categoryId, int page, int pageSize)
467    {
468        AddCriteria(p => p.CategoryId == categoryId);
469        AddInclude(p => p.Category);
470        ApplyOrderBy(p => p.Name);
471        ApplyPaging((page - 1) * pageSize, pageSize);
472    }
473}
474 
475#endregion
476 
477#region Entity Definitions
478 
479public class Product
480{
481    public string Id { get; set; } = string.Empty;
482    public string Name { get; set; } = string.Empty;
483    public string Sku { get; set; } = string.Empty;
484    public decimal Price { get; set; }
485    public int CategoryId { get; set; }
486    public int Stock { get; set; }
487    public bool IsDeleted { get; set; }
488    public DateTime CreatedAt { get; set; }
489    public DateTime? UpdatedAt { get; set; }
490 
491    // Navigation
492    public Category? Category { get; set; }
493}
494 
495public class Category
496{
497    public int Id { get; set; }
498    public string Name { get; set; } = string.Empty;
499    public ICollection<Product> Products { get; set; } = new List<Product>();
500}
501 
502public class Order
503{
504    public int Id { get; set; }
505    public string CustomerOrderCode { get; set; } = string.Empty;
506    public decimal Total { get; set; }
507    public DateTime CreatedAt { get; set; }
508    public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
509}
510 
511public class OrderItem
512{
513    public int Id { get; set; }
514    public int OrderId { get; set; }
515    public string ProductId { get; set; } = string.Empty;
516    public int Quantity { get; set; }
517    public decimal UnitPrice { get; set; }
518 
519    public Order? Order { get; set; }
520    public Product? Product { get; set; }
521}
522 
523#endregion
524