EF Core Patterns
DbContext design
public sealed class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
public DbSet<User> Users => Set<User>();
public DbSet<Order> Orders => Set<Order>();
public DbSet<OrderLine> OrderLines => Set<OrderLine>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Discover all IEntityTypeConfiguration<T> in the assembly automatically
modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
}
}
Use ApplyConfigurationsFromAssembly — it discovers all IEntityTypeConfiguration<T> implementations automatically, avoiding the need to register each entity manually in OnModelCreating.
Use DbSet<T> expression-bodied properties (=> Set<T>()) to avoid null warnings and simplify the class.
Entity configuration — Fluent API
public sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
public void Configure(EntityTypeBuilder<Order> builder)
{
builder.ToTable("orders");
builder.HasKey(o => o.Id);
builder.Property(o => o.Status)
.HasConversion<string>() // store enum as string for readability
.HasMaxLength(50)
.IsRequired();
builder.Property(o => o.Total)
.HasPrecision(18, 2)
.IsRequired();
builder.Property(o => o.CreatedAt)
.IsRequired();
builder.Property(o => o.Notes)
.HasMaxLength(1000)
.IsRequired(false);
builder.HasIndex(o => o.CustomerId);
builder.HasIndex(o => new { o.Status, o.CreatedAt });
builder.HasOne(o => o.Customer)
.WithMany(c => c.Orders)
.HasForeignKey(o => o.CustomerId)
.OnDelete(DeleteBehavior.Restrict); // don't cascade-delete orders with customer
builder.HasMany(o => o.Lines)
.WithOne(l => l.Order)
.HasForeignKey(l => l.OrderId)
.OnDelete(DeleteBehavior.Cascade); // deleting an order removes its lines
}
}
OnDelete guidance
| Scenario | Behaviour |
|---|
| Child data is meaningless without parent (e.g., order lines) | DeleteBehavior.Cascade |
| Child data must remain (e.g., orders after customer deletion) | DeleteBehavior.Restrict |
| FK becomes null when parent is deleted (e.g., optional reference) | DeleteBehavior.SetNull |
Default is Cascade for required relations. Override explicitly to avoid surprises.
Querying — AsNoTracking and projection
// Read-only query — AsNoTracking skips the change tracker (faster, less memory)
public async Task<IReadOnlyList<OrderSummaryDto>> GetOrderSummariesAsync(
int customerId, CancellationToken ct)
{
return await _db.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.CreatedAt)
.Select(o => new OrderSummaryDto(o.Id, o.Status, o.Total, o.CreatedAt))
.AsNoTracking()
.ToListAsync(ct);
}
// Tracked query — when you need to update the entity afterwards
public async Task<Order?> FindForUpdateAsync(int id, CancellationToken ct)
{
return await _db.Orders.FindAsync(new object[] { id }, ct);
// FindAsync always uses the change tracker
}
Use AsNoTracking() for all read-only queries — it reduces memory allocation and execution time by skipping the identity map.
Project to DTOs with Select() instead of loading full entities and then mapping — avoids fetching columns you don't need.
Avoiding N+1 — Include and Split queries
// Eager loading with Include — single JOIN query
var orders = await _db.Orders
.Include(o => o.Lines)
.ThenInclude(l => l.Product)
.Where(o => o.CustomerId == customerId)
.AsNoTracking()
.ToListAsync(ct);
// Split query — avoids cartesian product explosion for multiple collections
var orders = await _db.Orders
.Include(o => o.Lines)
.Include(o => o.Tags)
.AsSplitQuery() // issues separate SQL queries per collection
.AsNoTracking()
.ToListAsync(ct);
Prefer projection (Select) over Include when you only need a subset of related data — it generates more efficient SQL.
Use AsSplitQuery() when loading multiple collection navigations to avoid a Cartesian explosion (rows = A × B × C).
Never use lazy loading in web applications — it is a hidden N+1 footgun. Disable it explicitly:
// DbContextOptions — disable lazy loading proxies
options.UseSqlServer(connectionString)
.UseLazyLoadingProxies(false); // false is the default; explicit for clarity
Repositories — thin wrapper pattern
public interface IOrderRepository
{
Task<Order?> FindAsync(int id, CancellationToken ct = default);
Task<IReadOnlyList<OrderSummaryDto>> GetSummariesAsync(int customerId, CancellationToken ct = default);
Task SaveAsync(Order order, CancellationToken ct = default);
Task DeleteAsync(int id, CancellationToken ct = default);
}
public sealed class OrderRepository(AppDbContext db) : IOrderRepository
{
public async Task<Order?> FindAsync(int id, CancellationToken ct)
=> await db.Orders
.Include(o => o.Lines)
.FirstOrDefaultAsync(o => o.Id == id, ct);
public async Task<IReadOnlyList<OrderSummaryDto>> GetSummariesAsync(
int customerId, CancellationToken ct)
=> await db.Orders
.Where(o => o.CustomerId == customerId)
.Select(o => new OrderSummaryDto(o.Id, o.Status, o.Total, o.CreatedAt))
.AsNoTracking()
.ToListAsync(ct);
public async Task SaveAsync(Order order, CancellationToken ct)
{
db.Orders.Add(order);
await db.SaveChangesAsync(ct);
}
public async Task DeleteAsync(int id, CancellationToken ct)
{
await db.Orders.Where(o => o.Id == id).ExecuteDeleteAsync(ct); // EF Core 7+
}
}
Transactions
// Explicit transaction for multi-aggregate writes
await using var tx = await _db.Database.BeginTransactionAsync(ct);
try
{
var order = Order.Create(customerId, lines);
_db.Orders.Add(order);
await _db.SaveChangesAsync(ct);
inventory.Deduct(lines);
await _db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);
}
catch
{
await tx.RollbackAsync(ct);
throw;
}
Prefer designing aggregates to avoid cross-aggregate transactions. When a transaction is necessary, keep it as short as possible (no external HTTP calls inside a transaction).
Parameterized raw SQL — when LINQ is not enough
// Safe: FromSql with FormattableString — EF Core parameterizes automatically
var userId = 42;
var users = await _db.Users
.FromSql($"SELECT * FROM users WHERE id = {userId} AND is_active = 1")
.AsNoTracking()
.ToListAsync(ct);
// Also safe: FromSqlRaw with explicit SqlParameter objects
var param = new SqlParameter("@userId", userId);
var users = await _db.Users
.FromSqlRaw("SELECT * FROM users WHERE id = @userId", param)
.AsNoTracking()
.ToListAsync(ct);
// UNSAFE — string concatenation / interpolation into FromSqlRaw
// NEVER DO THIS:
var users = await _db.Users
.FromSqlRaw($"SELECT * FROM users WHERE id = {userId}") // SQL injection risk
.ToListAsync(ct);
Always prefer FromSql(FormattableString) (C# interpolated string) over FromSqlRaw with manual parameters — it is safer and more readable. FromSqlRaw is only needed when the SQL is truly dynamic (e.g., built from an allowlisted column name for ORDER BY).
Connection string from IConfiguration
// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql( // or UseSqlServer
builder.Configuration.GetConnectionString("DefaultConnection")
?? throw new InvalidOperationException(
"Connection string 'DefaultConnection' not found in configuration.")));
// appsettings.json — non-sensitive defaults only
{
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Database=myapp_dev;Username=dev;Password="
}
}
Override Password and the full connection string via environment variables in CI/prod:
ConnectionStrings__DefaultConnection="Host=prod-db;Database=myapp;Username=app;Password=<secret>"
Never commit the production connection string — use environment variables or a secrets manager.