Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.
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
publicsealedclassOrderProcessor(
IDbContextFactory<AppDbContext> contextFactory)
{
publicasync Task ProcessBatchAsync(CancellationToken ct)
{
// Each iteration gets its own short-lived DbContextawaitusingvar 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 , so controllers
request- services can still inject `AppDbContext` directly.
`AddDbContextPool<T>()` `AddPooledDbContextFactory<T>()` reuse `DbContext` instances to reduce allocation overhead.
Use pooling throughput matters your context has no injected services:
```csharp
builder.Services.AddDbContextPool<AppDbContext>(options =>
options.UseNpgsql(connectionString),
poolSize: );
```csharp
**Pooling constraints:** Pooled contexts are reset reused. Do store per-request state the `DbContext`
subclass. Do inject services the constructor -- use `IDbContextFactory<T>` = db.Orders
.AsNoTracking()
.Where(o => o.CustomerId == customerId)
.ToListAsync(ct);
ordersWithItems = db.Orders
.AsNoTrackingWithIdentityResolution()
.Include(o => o.Items)
.Where(o => o.Status == OrderStatus.Active)
.ToListAsync(ct);
```text
For read-heavy services, no-tracking the :
```csharp
builder.Services.AddDbContext<ReadOnlyDbContext>(options =>
options.UseNpgsql(connectionString)
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
```csharp
Then opt- to tracking only needed:
```csharp
order = readOnlyDb.Orders
.AsTracking()
.FirstAsync(o => o.Id == orderId, ct);
```csharp
---
When loading collections via `Include()`, EF Core generates a single SQL query JOINs . This produces a
Cartesian explosion multiple collections are included.
```csharp
orders = db.Orders
.Include(o => o.Items)
.Include(o => o.Payments)
.ToListAsync(ct);
```text
```csharp
orders = db.Orders
.Include(o => o.Items)
.Include(o => o.Payments)
.AsSplitQuery()
.ToListAsync(ct);
```text
| Approach | Pros | Cons |
| ---------------------- | ------------------------------------------ | -------------------------------------------- |
| ;
```csharp
Then opt- to single queries atomicity needed:
```csharp
result = db.Orders
.Include(o => o.Items)
.Include(o => o.Payments)
.AsSingleQuery()
.ToListAsync(ct);
```text
---
```bash
dotnet ef migrations AddOrderStatus \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef migrations script \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api \
--idempotent \
--output migrations.sql
dotnet ef database update \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
```text
Migration bundles produce a self-contained executable CI/CD pipelines -- no `dotnet ef` tooling needed the
deployment server:
```bash
dotnet ef migrations bundle \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api \
--output efbundle \
--self-contained
./efbundle --connection
```json
**Always generate idempotent scripts**
{
modelBuilder.Entity<OrderStatus>().HasData(
OrderStatus { Id = , Name = },
OrderStatus { Id = , Name = },
OrderStatus { Id = , Name = },
OrderStatus { Id = , Name = });
}
```text
**Important:** `HasData()` uses primary key values identity. Changing a seed
Code Navigation (Serena MCP)
Primary approach: Use Serena symbol operations for efficient code navigation:
Find definitions: serena_find_symbol instead of text search
Understand structure: serena_get_symbols_overview for file organization
Track references: serena_find_referencing_symbols for impact analysis
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
withpooling
(`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-outvar orders
await
// Per-query with identity resolution (deduplicates entities in the result set)
var
await
### Default No-Tracking at the Context Level
set
as
default
in
when
var
await
## Query Splitting
with
by
default
when
### The Problem: Cartesian Explosion
// Single query: produces Cartesian product of OrderItems x Payments
var
await
// N items
// M payments
// Result set: N x M rows per order
### The Solution: Split Queries
var
await
// Executes 3 separate queries: Orders, Items, Payments
### Tradeoffs
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 orwhen atomicity matters.
### Global Default
Set split queries as the default at the provider level:
```csharp
options.UseNpgsql(connectionString, npgsql =>
npgsql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery))
in
where
is
var
await
## Migrations
### Migration Workflow
# Create a migration after model changes
add
# Review the generated SQL before applying
# Apply in development
### Migration Bundles for Production
for
on
# Build the bundle
# Run in production -- pass connection string explicitly via --connection
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
protectedoverridevoidOnModelCreating(ModelBuilder modelBuilder)
new
1
"Pending"
new
2
"Processing"
new
3
"Completed"
new
4
"Cancelled"
for
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)
