Ef Core

// Persistence/AppDbContext.cspublic class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options){    public DbSet<Order> Orders => Set<Order>();    public DbSet<Product> Products => Set<Product>();
    protected override void OnModelCreating(ModelBuilder modelBuilder)    {        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);    }}
// Persistence/Configurations/OrderConfiguration.cspublic class OrderConfiguration : IEntityTypeConfiguration<Order>{    public void Configure(EntityTypeBuilder<Order> builder)    {        builder.HasKey(o => o.Id);
        builder.Property(o => o.Total)            .HasPrecision(18, 2);
        builder.HasMany(o => o.Items)            .WithOne()            .HasForeignKey(i => i.OrderId)            .OnDelete(DeleteBehavior.Cascade);
        builder.HasIndex(o => o.CustomerId);        builder.HasIndex(o => o.CreatedAt);    }}

// Program.csbuilder.Services.AddDbContext<AppDbContext>(options =>    options.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

// GOOD — project to DTO, only loads needed columnspublic async Task<OrderResponse?> GetOrderAsync(Guid id, CancellationToken ct){    return await db.Orders        .Where(o => o.Id == id)        .Select(o => new OrderResponse(            o.Id,            o.Total,            o.CreatedAt,            o.Items.Select(i => new OrderItemResponse(i.ProductName, i.Quantity, i.Price)).ToList()))        .FirstOrDefaultAsync(ct);}

public async Task<PagedList<OrderSummary>> ListOrdersAsync(int page, int pageSize, CancellationToken ct){    var query = db.Orders        .OrderByDescending(o => o.CreatedAt)        .Select(o => new OrderSummary(o.Id, o.CustomerName, o.Total, o.Status));
    var totalCount = await query.CountAsync(ct);    var items = await query        .Skip((page - 1) * pageSize)        .Take(pageSize)        .ToListAsync(ct);
    return new PagedList<OrderSummary>(items, totalCount, page, pageSize);}

// Update without loading entitiesawait db.Orders    .Where(o => o.Status == OrderStatus.Pending && o.CreatedAt < cutoff)    .ExecuteUpdateAsync(s => s        .SetProperty(o => o.Status, OrderStatus.Expired)        .SetProperty(o => o.UpdatedAt, clock.GetUtcNow()),        ct);
// Delete without loading entitiesawait db.Orders    .Where(o => o.Status == OrderStatus.Cancelled && o.CreatedAt < archiveCutoff)    .ExecuteDeleteAsync(ct);

public class AuditInterceptor(TimeProvider clock) : SaveChangesInterceptor{    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(        DbContextEventData eventData,        InterceptionResult<int> result,        CancellationToken ct = default)    {        var context = eventData.Context;        if (context is null) return ValueTask.FromResult(result);
        var now = clock.GetUtcNow();
        foreach (var entry in context.ChangeTracker.Entries<IAuditable>())        {            switch (entry.State)            {                case EntityState.Added:                    entry.Entity.CreatedAt = now;                    entry.Entity.UpdatedAt = now;                    break;                case EntityState.Modified:                    entry.Entity.UpdatedAt = now;                    break;            }        }
        return ValueTask.FromResult(result);    }}
// Registrationbuilder.Services.AddDbContext<AppDbContext>((sp, options) =>    options        .UseNpgsql(connectionString)        .AddInterceptors(sp.GetRequiredService<AuditInterceptor>()));

public class OrderQueries{    public static readonly Func<AppDbContext, Guid, CancellationToken, Task<Order?>> GetById =        EF.CompileAsyncQuery((AppDbContext db, Guid id, CancellationToken ct) =>            db.Orders                .Include(o => o.Items)                .FirstOrDefault(o => o.Id == id));}
// Usagevar order = await OrderQueries.GetById(db, orderId, ct);

// Store enum as stringbuilder.Property(o => o.Status)    .HasConversion<string>()    .HasMaxLength(50);
// Strongly-typed IDspublic readonly record struct OrderId(Guid Value);
builder.Property(o => o.Id)    .HasConversion(id => id.Value, value => new OrderId(value));

// Soft delete filterbuilder.HasQueryFilter(o => !o.IsDeleted);
// Multi-tenant filterbuilder.HasQueryFilter(o => o.TenantId == _tenantProvider.TenantId);
// Bypass when neededvar allOrders = await db.Orders.IgnoreQueryFilters().ToListAsync(ct);

// BAD — unnecessary abstraction that limits EF Core's powerpublic interface IOrderRepository{    Task<Order?> GetByIdAsync(Guid id);    Task AddAsync(Order order);    Task SaveChangesAsync();}
// GOOD — use DbContext directly in handlerspublic class Handler(AppDbContext db){    public async Task<Order?> Handle(GetOrder.Query query, CancellationToken ct)    {        return await db.Orders.FindAsync([query.Id], ct);    }}

// BAD — lazy loading causes N+1 queries and hides data accessbuilder.Services.AddDbContext<AppDbContext>(options =>    options.UseLazyLoadingProxies()); // DON'T
// GOOD — explicit loading with Include or projectionvar orders = await db.Orders    .Include(o => o.Items)    .Where(o => o.CustomerId == customerId)    .ToListAsync(ct);

// BAD — loads ALL orders, filters in C#var orders = await db.Orders.ToListAsync(ct);var pending = orders.Where(o => o.Status == OrderStatus.Pending);
// GOOD — filter in the databasevar pending = await db.Orders    .Where(o => o.Status == OrderStatus.Pending)    .ToListAsync(ct);

// BAD — missing await, returns before save completespublic void Handle(CreateOrder.Command command){    db.Orders.Add(order);    db.SaveChangesAsync(); // Fire-and-forget BUG}
// GOODpublic async Task Handle(CreateOrder.Command command, CancellationToken ct){    db.Orders.Add(order);    await db.SaveChangesAsync(ct);}