# Dotnet Efcore Patterns > EF Core, DbContext, AsNoTracking, query splitting. - Skill: `rudironsoni/dotnet-efcore-patterns` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add rudironsoni/dotnet-efcore-patterns` - Raw SKILL.md: https://api.skillmd.com/api/skills/rudironsoni/dotnet-efcore-patterns/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools, AI & ML - License: MIT - Author: rudironsoni (https://skillmd.com/u/rudironsoni) - Updated: 2026-09-10 - Page: https://skillmd.com/skills/rudironsoni/dotnet-efcore-patterns --- # 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(options => options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection"))); ```csharp ### Lifetime Rules | Scenario | Lifetime | Registration | | --------------------- | ------------------- | --------------------------- | | Web API / MVC request | Scoped (default) | `AddDbContext()` | | Background service | Scoped via factory | `AddDbContextFactory()` | | Blazor Server | Scoped via factory | `AddDbContextFactory()` | | 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` to create short-lived contexts on demand: ```csharp public sealed class OrderProcessor( IDbContextFactory 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(options => options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection"))); ```csharp **Important:** `AddDbContextFactory()` also registers `AppDbContext` itself as scoped, so controllers and request-scoped services can still inject `AppDbContext` directly. ### Pooling `AddDbContextPool()` and `AddPooledDbContextFactory()` reuse `DbContext` instances to reduce allocation overhead. Use pooling when throughput matters and your context has no injected scoped services: ```csharp builder.Services.AddDbContextPool(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` with pooling (`AddPooledDbContextFactory()`) 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(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=" # 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().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> SavingChangesAsync( DbContextEventData eventData, InterceptionResult 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()) { 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> SavingChangesAsync( DbContextEventData eventData, InterceptionResult result, CancellationToken ct = default) { if (eventData.Context is null) return ValueTask.FromResult(result); foreach (var entry in eventData.Context.ChangeTracker.Entries()) { ## Detailed Examples See [references/detailed-examples.md](references/detailed-examples.md) for complete code samples and advanced patterns.