Data access with Entity Framework Core

DbContext lifetime, migrations, loading strategies and the N+1 queries that turn a fast endpoint into a slow one.

Context lifetime and configuration

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("App"),
        sql => sql.EnableRetryOnFailure(3)));

// For a read-heavy service that creates a context per operation
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("App")));

public sealed class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Order>(e =>
        {
            e.ToTable("orders");
            e.HasKey(o => o.Id);
            e.Property(o => o.Total)
             .HasConversion<decimal>(m => m, v => v)
             .HasColumnType("decimal(18,2)");
            e.Property(o => o.RowVersion).IsRowVersion();   // optimistic concurrency
            e.HasIndex(o => new { o.CustomerId, o.PlacedAt });
            e.HasOne(o => o.Customer)
             .WithMany(c => c.Orders)
             .HasForeignKey(o => o.CustomerId)
             .OnDelete(DeleteBehavior.Restrict);
        });

        // Apply every IEntityTypeConfiguration<T> in this assembly
        b.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

• Scoped lifetime matches the request, which is the right default. A context is not thread safe and must never be shared across concurrent operations.
• Pooling reuses context instances, which helps a service that creates many short-lived contexts, but the context must not hold per-request state in its constructor.
• Queries are not thread safe on one context either: await Task.WhenAll(db.A.ToListAsync(), db.B.ToListAsync()) on the same context is a bug.
• Configure the model explicitly. Convention is fine for a prototype and a liability for a schema you will own for years.
• An index on the columns your WHERE and ORDER BY clauses use is worth more than any query rewrite.

Migrations, loading and N+1

# Migrations are code: review them like code
dotnet ef migrations add AddOrderIndex --output-dir Data/Migrations
dotnet ef migrations script --idempotent -o migrate.sql
dotnet ef database update

# See the SQL a query produces, without running the app
dotnet ef dbcontext optimize --output-dir Compiled --namespace App.Compiled

// N+1: one query for the orders, then one per customer
var orders = await db.Orders.Where(o => o.PlacedAt > since).ToListAsync();
foreach (var o in orders)
{
    var name = (await db.Customers.FindAsync(o.CustomerId)).Name;   // N queries
}

// Fixed with projection: one query, only the columns you need
var rows = await db.Orders
    .Where(o => o.PlacedAt > since)
    .Select(o => new OrderRow(o.Id, o.Total, o.Customer.Name))
    .AsNoTracking()
    .ToListAsync(ct);

// Eager loading when you need the tracked entities
var withItems = await db.Orders
    .Include(o => o.Items)
    .Where(o => o.Id == id)
    .AsSplitQuery()                 // avoids the cartesian explosion
    .FirstOrDefaultAsync(ct);

// Explicit loading for a small, known set of parents
await db.Entry(order).Collection(o => o.Items).LoadAsync(ct);

// AsNoTracking for read-only work: no change tracker, less memory
var report = await db.Orders.AsNoTracking()
    .Where(o => o.Status == OrderStatus.Fulfilled)
    .GroupBy(o => o.Currency)
    .Select(g => new { g.Key, Total = g.Sum(o => o.Total) })
    .ToListAsync(ct);

FAQ

Related

Controllers, dependency injection and middleware Error handling, health checks and resilience

Last refreshed 2026-09-18.