dotnet-efcore-patterns

Tactical patterns for Entity Framework Core in .NET applications. Covers DbContext lifetime management, read-only query optimization, query splitting, migration workflows, interceptors, compiled queries, and connection resiliency. These patterns apply to EF Core 8+ and are compatible with SQL Server, PostgreSQL, and SQLite providers.

Scope

DbContext lifecycle and scoped registration
AsNoTracking and read-only query optimization
Query splitting and compiled queries
Migration workflows and migration bundles for production
SaveChanges and connection interceptors
Connection resiliency configuration
DbContextFactory for background services and Blazor Server

Out of scope

Strategic data architecture (read/write split, aggregate boundaries) -- see [skill:dotnet-efcore-architecture]
Data access technology selection (EF Core vs Dapper vs ADO.NET) -- see [skill:dotnet-data-access-strategy]
DI container mechanics -- see [skill:dotnet-csharp-dependency-injection]
Testing EF Core with fixtures -- see [skill:dotnet-integration-testing]
Domain modeling with DDD patterns -- see [skill:dotnet-domain-modeling]

Cross-references: [skill:dotnet-csharp-dependency-injection] for service registration and DbContext lifetime, [skill:dotnet-csharp-async-patterns] for cancellation token propagation in queries, [skill:dotnet-efcore-architecture] for strategic data patterns, [skill:dotnet-data-access-strategy] for data access technology selection.

DbContext Lifecycle

DbContext is a unit of work and should be short-lived. In ASP.NET Core, register it as scoped (one per request):

builder.Services.AddDbContext<AppDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

Lifetime Rules

Scenario Lifetime Registration

Web API / MVC request Scoped (default) AddDbContext<T>()

Background service Scoped via factory AddDbContextFactory<T>()

Blazor Server Scoped via factory AddDbContextFactory<T>()

Console app Transient or manual new AppDbContext(options)

DbContextFactory for Long-Lived Services

Background services and Blazor Server circuits outlive a single scope. Use IDbContextFactory<T> to create short-lived contexts on demand:

public sealed class OrderProcessor( IDbContextFactory<AppDbContext> contextFactory) { public async Task ProcessBatchAsync(CancellationToken ct) { // Each iteration gets its own short-lived DbContext await using var db = await contextFactory.CreateDbContextAsync(ct);

    var pending = await db.Orders
        .Where(o => o.Status == OrderStatus.Pending)
        .ToListAsync(ct);

    foreach (var order in pending)
    {
        order.Status = OrderStatus.Processing;
    }

    await db.SaveChangesAsync(ct);
}

}

builder.Services.AddDbContextFactory<AppDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

Important: AddDbContextFactory<T>() also registers AppDbContext itself as scoped, so controllers and request-scoped services can still inject AppDbContext directly.

Pooling

AddDbContextPool<T>() and AddPooledDbContextFactory<T>() reuse DbContext instances to reduce allocation overhead. Use pooling when throughput matters and your context has no injected scoped services:

builder.Services.AddDbContextPool<AppDbContext>(options => options.UseNpgsql(connectionString), poolSize: 128); // default is 1024

Pooling constraints: Pooled contexts are reset and reused. Do not store per-request state on the DbContext subclass. Do not inject scoped services into the constructor -- use IDbContextFactory<T> with pooling (AddPooledDbContextFactory<T>() ) if you need factory semantics.

AsNoTracking for Read-Only Queries

By default, EF Core tracks all entities returned by queries, enabling change detection on SaveChangesAsync() . For read-only queries, disable tracking to reduce memory and CPU overhead:

// Per-query opt-out var orders = await db.Orders .AsNoTracking() .Where(o => o.CustomerId == customerId) .ToListAsync(ct);

// Per-query with identity resolution (deduplicates entities in the result set) var ordersWithItems = await db.Orders .AsNoTrackingWithIdentityResolution() .Include(o => o.Items) .Where(o => o.Status == OrderStatus.Active) .ToListAsync(ct);

Default No-Tracking at the Context Level

For read-heavy services, set no-tracking as the default:

builder.Services.AddDbContext<ReadOnlyDbContext>(options => options.UseNpgsql(connectionString) .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

Then opt-in to tracking only when needed:

var order = await readOnlyDb.Orders .AsTracking() .FirstAsync(o => o.Id == orderId, ct);

Query Splitting

When loading collections via Include() , EF Core generates a single SQL query with JOINs by default. This produces a Cartesian explosion when multiple collections are included.

The Problem: Cartesian Explosion

// Single query: produces Cartesian product of OrderItems x Payments var orders = await db.Orders .Include(o => o.Items) // N items .Include(o => o.Payments) // M payments .ToListAsync(ct); // Result set: N x M rows per order

The Solution: Split Queries

var orders = await db.Orders .Include(o => o.Items) .Include(o => o.Payments) .AsSplitQuery() .ToListAsync(ct); // Executes 3 separate queries: Orders, Items, Payments

Tradeoffs

Approach Pros Cons

Single query (default) Atomic snapshot, one round-trip Cartesian explosion with multiple Includes

Split query No Cartesian explosion, less data transfer Multiple round-trips, no atomicity guarantee

Rule of thumb: Use AsSplitQuery() when including two or more collection navigations. Use the default single query for single-collection includes or when atomicity matters.

Global Default

Set split queries as the default at the provider level:

options.UseNpgsql(connectionString, npgsql => npgsql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Then opt-in to single queries where atomicity is needed:

var result = await db.Orders .Include(o => o.Items) .Include(o => o.Payments) .AsSingleQuery() .ToListAsync(ct);

Migrations

Migration Workflow

Create a migration after model changes

dotnet ef migrations add AddOrderStatus
--project src/MyApp.Infrastructure
--startup-project src/MyApp.Api

Review the generated SQL before applying

dotnet ef migrations script
--project src/MyApp.Infrastructure
--startup-project src/MyApp.Api
--idempotent
--output migrations.sql

Apply in development

dotnet ef database update
--project src/MyApp.Infrastructure
--startup-project src/MyApp.Api

Migration Bundles for Production

Migration bundles produce a self-contained executable for CI/CD pipelines -- no dotnet ef tooling needed on the deployment server:

Build the bundle

dotnet ef migrations bundle
--project src/MyApp.Infrastructure
--startup-project src/MyApp.Api
--output efbundle
--self-contained

Run in production -- pass connection string explicitly via --connection

./efbundle --connection "Host=prod-db;Database=myapp;Username=deploy;Password=..."

Alternatively, configure the bundle to read from an environment variable

by setting the connection string key in your DbContext's OnConfiguring or

appsettings.json, then pass the env var at runtime:

ConnectionStrings__DefaultConnection="Host=..." ./efbundle

Migration Best Practices

Always generate idempotent scripts for production deployments (--idempotent flag).
Never call Database.Migrate() at application startup in production -- it races with horizontal scaling and lacks rollback. Use migration bundles or idempotent scripts applied from CI/CD.
Keep migrations additive -- add columns with defaults, add tables, add indexes. Avoid destructive changes (drop column, rename table) in the same release as code changes.
Review generated code -- EF Core migration scaffolding can produce unexpected SQL. Always review the Up() and Down() methods.
Use separate migration projects -- keep migrations in an infrastructure project, not the API project. Specify --project and --startup-project explicitly.

Data Seeding

Use HasData() for reference data that should be part of migrations:

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<OrderStatus>().HasData( new OrderStatus { Id = 1, Name = "Pending" }, new OrderStatus { Id = 2, Name = "Processing" }, new OrderStatus { Id = 3, Name = "Completed" }, new OrderStatus { Id = 4, Name = "Cancelled" }); }

Important: HasData() uses primary key values for identity. Changing a seed value's PK in a later migration deletes the old row and inserts a new one -- it does not update in place.

Interceptors

EF Core interceptors allow cross-cutting concerns to be injected into the database pipeline without modifying entity logic. Interceptors run for every operation of their type.

SaveChanges Interceptor: Automatic Audit Timestamps

public sealed class AuditTimestampInterceptor : SaveChangesInterceptor { public override ValueTask<InterceptionResult<int>> SavingChangesAsync( DbContextEventData eventData, InterceptionResult<int> result, CancellationToken ct = default) { if (eventData.Context is null) return ValueTask.FromResult(result);

    var now = DateTimeOffset.UtcNow;

    foreach (var entry in eventData.Context.ChangeTracker.Entries&#x3C;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);
}

}

public interface IAuditable { DateTimeOffset CreatedAt { get; set; } DateTimeOffset UpdatedAt { get; set; } }

Soft Delete Interceptor

public sealed class SoftDeleteInterceptor : SaveChangesInterceptor { public override ValueTask<InterceptionResult<int>> SavingChangesAsync( DbContextEventData eventData, InterceptionResult<int> result, CancellationToken ct = default) { if (eventData.Context is null) return ValueTask.FromResult(result);

    foreach (var entry in eventData.Context.ChangeTracker.Entries&#x3C;ISoftDeletable>())
    {
        if (entry.State == EntityState.Deleted)
        {
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTimeOffset.UtcNow;
        }
    }

    return ValueTask.FromResult(result);
}

}

Combine with a global query filter so soft-deleted entities are excluded by default:

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Product>() .HasQueryFilter(p => !p.IsDeleted); }

// Bypass the filter when needed (e.g., admin queries) var allProducts = await db.Products .IgnoreQueryFilters() .ToListAsync(ct);

Connection Interceptor: Dynamic Connection Strings

public sealed class TenantConnectionInterceptor( ITenantProvider tenantProvider) : DbConnectionInterceptor { public override ValueTask<InterceptionResult> ConnectionOpeningAsync( DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken ct = default) { var tenant = tenantProvider.GetCurrentTenant(); connection.ConnectionString = tenant.ConnectionString; return ValueTask.FromResult(result); } }

Registering Interceptors

builder.Services.AddDbContext<AppDbContext>((sp, options) => options.UseNpgsql(connectionString) .AddInterceptors( sp.GetRequiredService<AuditTimestampInterceptor>(), sp.GetRequiredService<SoftDeleteInterceptor>()));

// Register interceptors in DI builder.Services.AddSingleton<AuditTimestampInterceptor>(); builder.Services.AddSingleton<SoftDeleteInterceptor>();

Compiled Queries

For queries executed very frequently with the same shape, compiled queries eliminate the overhead of expression tree translation on every call:

public static class CompiledQueries { // Single-result compiled query -- delegate does NOT accept CancellationToken public static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById = EF.CompileAsyncQuery( (AppDbContext db, int orderId) => db.Orders .AsNoTracking() .Include(o => o.Items) .FirstOrDefault(o => o.Id == orderId));

// Multi-result compiled query returns IAsyncEnumerable
public static readonly Func&#x3C;AppDbContext, string, IAsyncEnumerable&#x3C;Order>>
    GetOrdersByCustomer = EF.CompileAsyncQuery(
        (AppDbContext db, string customerId) =>
            db.Orders
                .AsNoTracking()
                .Where(o => o.CustomerId == customerId)
                .OrderByDescending(o => o.CreatedAt));

}

// Usage var order = await CompiledQueries.GetOrderById(db, orderId);

// IAsyncEnumerable results support cancellation via WithCancellation: await foreach (var o in CompiledQueries.GetOrdersByCustomer(db, customerId) .WithCancellation(ct)) { // Process each order }

When to use: Compiled queries provide measurable benefit for queries that execute thousands of times per second. For typical CRUD endpoints, standard LINQ is sufficient -- do not prematurely optimize.

Cancellation limitation: Single-result compiled query delegates (Task<T?> ) do not accept CancellationToken . If per-call cancellation is required, use standard async LINQ (FirstOrDefaultAsync(ct) ) instead of a compiled query. Multi-result compiled queries (IAsyncEnumerable<T> ) support cancellation via .WithCancellation(ct) on the async enumerable.

Connection Resiliency

Transient database failures (network blips, failovers) should be handled with automatic retry. Each provider has a built-in execution strategy:

// PostgreSQL options.UseNpgsql(connectionString, npgsql => npgsql.EnableRetryOnFailure( maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(30), errorCodesToAdd: null));

// SQL Server options.UseSqlServer(connectionString, sqlServer => sqlServer.EnableRetryOnFailure( maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null));

Manual Execution Strategies

When you need to wrap multiple SaveChangesAsync calls in a single logical transaction with retries:

var strategy = db.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () => { await using var transaction = await db.Database.BeginTransactionAsync(ct);

var order = await db.Orders.FindAsync([orderId], ct);
order!.Status = OrderStatus.Completed;
await db.SaveChangesAsync(ct);

var payment = new Payment { OrderId = orderId, Amount = order.Total };
db.Payments.Add(payment);
await db.SaveChangesAsync(ct);

await transaction.CommitAsync(ct);

});

Important: The entire delegate is re-executed on retry, including the transaction. Ensure the logic is idempotent or uses database-level uniqueness constraints to prevent duplicates.

Key Principles

Keep DbContext short-lived -- one per request in web apps, one per unit of work in background services via IDbContextFactory<T>
Default to AsNoTracking for reads -- opt in to tracking only when you need change detection
Use split queries for multiple collection Includes -- avoid Cartesian explosion
Never call Database.Migrate() at startup in production -- use migration bundles or idempotent scripts
Register interceptors via DI -- avoid creating interceptor instances manually
Enable connection resiliency -- transient failures are a fact of life in cloud databases

Agent Gotchas

Do not inject DbContext into singleton services -- DbContext is scoped. Injecting it into a singleton captures a stale instance. Use IDbContextFactory<T> instead.
Do not forget CancellationToken propagation -- pass ct to all ToListAsync() , FirstOrDefaultAsync() , SaveChangesAsync() , and other async EF Core methods. Omitting it prevents graceful request cancellation.
Do not use Database.EnsureCreated() alongside migrations -- EnsureCreated() creates the schema without migration history, making subsequent migrations fail. Use it only in test scenarios without migrations.
Do not assume SaveChangesAsync is implicitly transactional across multiple calls -- each SaveChangesAsync() is its own transaction. Wrap multiple saves in an explicit BeginTransactionAsync() / CommitAsync() block when atomicity is required.
Do not hardcode connection strings -- read from configuration (builder.Configuration.GetConnectionString("...") ) and inject via environment variables in production.
Do not forget to list required NuGet packages -- EF Core provider packages (Microsoft.EntityFrameworkCore.SqlServer , Npgsql.EntityFrameworkCore.PostgreSQL ) and the design-time package (Microsoft.EntityFrameworkCore.Design ) must be referenced explicitly.

References

EF Core performance best practices
DbContext lifetime, configuration, and initialization
EF Core interceptors
EF Core migrations overview
EF Core compiled queries
EF Core connection resiliency

dotnet-efcore-patterns

Safety Notice

Copy this and send it to your AI assistant to learn

Create a migration after model changes

Review the generated SQL before applying

Apply in development

Build the bundle

Run in production -- pass connection string explicitly via --connection

Alternatively, configure the bundle to read from an environment variable

by setting the connection string key in your DbContext's OnConfiguring or

appsettings.json, then pass the env var at runtime:

ConnectionStrings__DefaultConnection="Host=..." ./efbundle

Source Transparency

Related Skills

dotnet-ui

dotnet-csharp

dotnet-api

dotnet-testing