dotnet-efcore-patterns

name	dotnet-efcore-patterns
description	EF Core, DbContext, AsNoTracking, query splitting.
metadata	{"short-description":".NET skill guidance for data tasks"}

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

```csharp

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

```text

Register the factory:

```csharp

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

```csharp

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

```csharp

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

```text

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

```csharp

Then opt-in to tracking only when needed:

```csharp

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

```csharp

---

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

```text

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

```text

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

```csharp

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

```text

---

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

```text

### 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=<DB_PASSWORD_PLACEHOLDER>"

# 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

```json

### Migration Best Practices

1. **Always generate idempotent scripts** for production deployments (`--idempotent` flag).
2. **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.
3. **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.
4. **Review generated code** -- EF Core migration scaffolding can produce unexpected SQL. Always review the `Up()` and
   `Down()` methods.
5. **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" });
}

```text

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

```text

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

```text

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

```text

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

```text

### 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>();

```text

---

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

```text

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

```text

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

```text

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

1. **Do not inject `DbContext` into singleton services** -- `DbContext` is scoped. Injecting it into a singleton
   captures a stale instance. Use `IDbContextFactory<T>` instead.
2. **Do not forget `CancellationToken` propagation** -- pass `ct` to all `ToListAsync()`, `FirstOrDefaultAsync()`,
   `SaveChangesAsync()`, and other async EF Core methods. Omitting it prevents graceful request cancellation.
3. **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.
4. **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.
5. **Do not hardcode connection strings** -- read from configuration
   (`builder.Configuration.GetConnectionString("...")`) and inject via environment variables in production.
6. **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](https://learn.microsoft.com/en-us/ef/core/performance/)
- [DbContext lifetime, configuration, and initialization](https://learn.microsoft.com/en-us/ef/core/dbcontext-configuration/)
- [EF Core interceptors](https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors)
- [EF Core migrations overview](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/)
- [EF Core compiled queries](https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics#compiled-queries)
- [EF Core connection resiliency](https://learn.microsoft.com/en-us/ef/core/miscellaneous/connection-resiliency)

Code Navigation (Serena MCP)

Primary approach: Use Serena symbol operations for efficient code navigation:

1. Find definitions: serena_find_symbol instead of text search
2. Understand structure: serena_get_symbols_overview for file organization
3. Track references: serena_find_referencing_symbols for impact analysis
4. Precise edits: serena_replace_symbol_body for clean modifications

When to use Serena vs traditional tools:

• Use Serena: Navigation, refactoring, dependency analysis, precise edits
• Use Read/Grep: Reading full files, pattern matching, simple text operations
• Fallback: If Serena unavailable, traditional tools work fine

Example workflow:

# Instead of:
Read: src/Services/OrderService.cs
Grep: "public void ProcessOrder"

# Use:
serena_find_symbol: "OrderService/ProcessOrder"
serena_get_symbols_overview: "src/Services/OrderService.cs"