dotnet-efcore-patterns

Original：🇺🇸 English

Translated

EF Core, DbContext, AsNoTracking, query splitting.

7installs

Sourcenovotnyllc/dotnet-artisan

Added on2026-02-24

NPX Install

npx skill4agent add novotnyllc/dotnet-artisan dotnet-efcore-patterns

SKILL.md Content

View Translation Comparison →

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):

csharp

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:

csharp

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);
    }
}

csharp

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:

csharp

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:

csharp

// 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:

csharp

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

Then opt-in to tracking only when needed:

csharp

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

csharp

// 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

csharp

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:

csharp

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

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

csharp

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

Migrations

Migration Workflow

bash

# 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:

bash

# 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:

csharp

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

csharp

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<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

csharp

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<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:

csharp

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

csharp

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

csharp

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:

csharp

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<AppDbContext, string, IAsyncEnumerable<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:

csharp

// 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:

csharp

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.

dotnet-efcore-patterns

NPX Install

Tags

SKILL.md Content

dotnet-efcore-patterns

Scope

Out of scope

DbContext Lifecycle

Lifetime Rules

DbContextFactory for Long-Lived Services

Pooling

AsNoTracking for Read-Only Queries

Default No-Tracking at the Context Level

Query Splitting

The Problem: Cartesian Explosion

The Solution: Split Queries

Tradeoffs

Global Default

Migrations

Migration Workflow

Migration Bundles for Production

Migration Best Practices

Data Seeding

Interceptors

SaveChanges Interceptor: Automatic Audit Timestamps

Soft Delete Interceptor

Connection Interceptor: Dynamic Connection Strings

Registering Interceptors

Compiled Queries

Connection Resiliency

Manual Execution Strategies

Key Principles

Agent Gotchas

References