DbContext - Configuration & Connection Pooling

// Infrastructure/Persistence/AppDbContext.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply configuration classes from the same assembly
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);

        // Global query filter - soft delete pattern for all entities implementing ISoftDelete
        modelBuilder.Model.GetEntityTypes()
            .Where(e => typeof(ISoftDelete).IsAssignableFrom(e.ClrType))
            .ToList()
            .ForEach(entityType =>
                modelBuilder.Entity(entityType.ClrType)
                    .HasQueryFilter(e => !((ISoftDelete)e).IsDeleted));
    }

    // ━━ Automatic audit fields (CreatedAt, UpdatedAt) ━━
    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        var now = DateTime.UtcNow;

        foreach (var entry in ChangeTracker.Entries<BaseEntity>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.Entity.CreatedAt = now;
                    entry.Entity.UpdatedAt = now;
                    break;
                case EntityState.Modified:
                    entry.Entity.UpdatedAt = now;
                    entry.Property(e => e.CreatedAt).IsModified = false; // protect CreatedAt
                    break;
            }
        }

        return await base.SaveChangesAsync(ct);
    }
}

// ━━ Registration - AddDbContextPool for high throughput ━━
// Pool reuses DbContext instances - avoids per-request allocation
builder.Services.AddDbContextPool<AppDbContext>(options =>
{
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection"),
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 3,
                maxRetryDelay: TimeSpan.FromSeconds(5),
                errorNumbersToAdd: null);

            sqlOptions.CommandTimeout(30);                    // 30s per query
            sqlOptions.MigrationsAssembly("MyApp.Infrastructure");
        });

    if (builder.Environment.IsDevelopment())
    {
        options.EnableSensitiveDataLogging();   // show SQL params in logs (never in prod!)
        options.EnableDetailedErrors();
    }
}, poolSize: 128); // pool of 128 instances (tune based on concurrency)

// Base types
public abstract class BaseEntity { public Guid Id { get; set; } public DateTime CreatedAt { get; set; } public DateTime UpdatedAt { get; set; } }
public interface ISoftDelete { bool IsDeleted { get; } }
public class Product : BaseEntity { public string Name { get; set; } = ""; public decimal Price { get; set; } public bool IsDeleted { get; set; } public Guid CategoryId { get; set; } public Category Category { get; set; } = null!; public string Description { get; set; } = ""; }
public class Category : BaseEntity { public string Name { get; set; } = ""; public List<Product> Products { get; set; } = []; public bool IsDeleted { get; set; } }
public class Order : BaseEntity { public Guid CustomerId { get; set; } public Customer Customer { get; set; } = null!; public List<OrderItem> Items { get; set; } = []; public string Status { get; set; } = ""; public decimal Total { get; set; } }
public class OrderItem : BaseEntity { public Guid OrderId { get; set; } public Guid ProductId { get; set; } public int Quantity { get; set; } public decimal UnitPrice { get; set; } }
public class Customer : BaseEntity { public string Email { get; set; } = ""; public string Name { get; set; } = ""; }