Setting the file. One moment. Turborepo Caching
Python
(opens in a new tab)
assets/repository-template.cs
C#·523 lines·16 KB
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