Back to Details

dotnet-efcore-patterns

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

dotnet-efcore-patterns

.NET EF Core 实战模式

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.
.NET应用中Entity Framework Core的实战模式。涵盖DbContext生命周期管理、只读查询优化、查询拆分、迁移工作流、拦截器、编译查询和连接弹性。这些模式适用于EF Core 8+，并兼容SQL Server、PostgreSQL和SQLite提供程序。

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
  • DbContext生命周期与作用域注册
  • AsNoTracking与只读查询优化
  • 查询拆分与编译查询
  • 迁移工作流与生产环境迁移包
  • SaveChanges与连接拦截器
  • 连接弹性配置
  • 用于后台服务和Blazor Server的DbContextFactory

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.
  • 战略数据架构（读写分离、聚合边界）—— 参见[skill:dotnet-efcore-architecture]
  • 数据访问技术选型（EF Core vs Dapper vs ADO.NET）—— 参见[skill:dotnet-data-access-strategy]
  • DI容器机制—— 参见[skill:dotnet-csharp-dependency-injection]
  • 使用测试夹具测试EF Core—— 参见[skill:dotnet-integration-testing]
  • 采用DDD模式的领域建模—— 参见[skill:dotnet-domain-modeling]
交叉引用：[skill:dotnet-csharp-dependency-injection] 用于服务注册和DbContext生命周期，[skill:dotnet-csharp-async-patterns] 用于查询中的取消令牌传播，[skill:dotnet-efcore-architecture] 用于战略数据模式，[skill:dotnet-data-access-strategy] 用于数据访问技术选型。

DbContext Lifecycle

DbContext 生命周期

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")));
DbContext
是工作单元，应保持短生命周期。在ASP.NET Core中，将其注册为作用域服务（每个请求一个实例）：
csharp
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

Lifetime Rules

生命周期规则

ScenarioLifetimeRegistration
Web API / MVC requestScoped (default)
AddDbContext<T>()
Background serviceScoped via factory
AddDbContextFactory<T>()
Blazor ServerScoped via factory
AddDbContextFactory<T>()
Console appTransient or manual
new AppDbContext(options)
场景生命周期注册方式
Web API / MVC 请求作用域（默认）
AddDbContext<T>()
后台服务通过工厂创建作用域实例
AddDbContextFactory<T>()
Blazor Server通过工厂创建作用域实例
AddDbContextFactory<T>()
控制台应用瞬时或手动创建
new AppDbContext(options)

DbContextFactory for Long-Lived Services

长生命周期服务中的DbContextFactory

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);
    }
}
Register the factory:
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.
后台服务和Blazor Server电路的生命周期会超过单个作用域。使用
IDbContextFactory<T>
按需创建短生命周期的上下文：
csharp
public sealed class OrderProcessor(
    IDbContextFactory<AppDbContext> contextFactory)
{
    public async Task ProcessBatchAsync(CancellationToken ct)
    {
        // 每次迭代获取自己的短生命周期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")));
重要提示： 
AddDbContextFactory<T>()
也会将
AppDbContext
本身注册为作用域服务，因此控制器和请求作用域服务仍然可以直接注入
AppDbContext
。

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.
AddDbContextPool<T>()
和 
AddPooledDbContextFactory<T>()
重用
DbContext
实例以减少内存分配开销。当吞吐量很重要且上下文没有注入作用域服务时，使用池化：
csharp
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseNpgsql(connectionString),
    poolSize: 128);  // 默认值为1024
池化约束： 池化上下文会被重置并重用。不要在
DbContext
子类上存储每个请求的状态。不要在构造函数中注入作用域服务——如果需要工厂语义，请结合池化使用
IDbContextFactory<T>
（
AddPooledDbContextFactory<T>()
）。

AsNoTracking for Read-Only Queries

只读查询使用AsNoTracking

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);
默认情况下，EF Core会跟踪查询返回的所有实体，以便在
SaveChangesAsync()
时进行变更检测。对于只读查询，禁用跟踪以减少内存和CPU开销：
csharp
// 单查询禁用跟踪
var orders = await db.Orders
    .AsNoTracking()
    .Where(o => o.CustomerId == customerId)
    .ToListAsync(ct);

// 单查询带标识解析（对结果集中的实体进行去重）
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);
对于读密集型服务，将禁用跟踪设置为默认行为：
csharp
builder.Services.AddDbContext<ReadOnlyDbContext>(options =>
    options.UseNpgsql(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
然后仅在需要时启用跟踪：
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.
当通过
Include()
加载集合时，EF Core默认生成带JOIN的单个SQL查询。当包含多个集合时，这会导致笛卡尔积爆炸。

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
csharp
// 单查询：生成OrderItems x Payments的笛卡尔积
var orders = await db.Orders
    .Include(o => o.Items)      // N个条目
    .Include(o => o.Payments)   // M个支付记录
    .ToListAsync(ct);
// 结果集：每个订单对应N x M行

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
csharp
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync(ct);
// 执行3个独立查询：Orders、Items、Payments

Tradeoffs

权衡

ApproachProsCons
Single query (default)Atomic snapshot, one round-tripCartesian explosion with multiple Includes
Split queryNo Cartesian explosion, less data transferMultiple 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.
方式优点缺点
单查询（默认）原子快照，一次往返包含多个集合时出现笛卡尔积爆炸
拆分查询无笛卡尔积爆炸，数据传输量更少多次往返，无原子性保证
经验法则： 当包含两个或更多集合导航属性时使用
AsSplitQuery()
。对于单个集合包含或需要原子性的场景，使用默认的单查询。

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);
在提供程序级别将拆分查询设置为默认：
csharp
options.UseNpgsql(connectionString, npgsql =>
    npgsql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
然后在需要原子性的场景中启用单查询：
csharp
var result = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSingleQuery()
    .ToListAsync(ct);

Migrations

迁移

Migration Workflow

迁移工作流

bash
undefined
bash
undefined

Create a migration after model changes

模型变更后创建迁移

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

Review the generated SQL before applying

应用前查看生成的SQL

dotnet ef migrations script
--project src/MyApp.Infrastructure
--startup-project src/MyApp.Api
--idempotent
--output migrations.sql
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
undefined
dotnet ef database update \ --project src/MyApp.Infrastructure \ --startup-project src/MyApp.Api
undefined

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
undefined
迁移包会生成一个自包含的可执行文件，用于CI/CD流水线——部署服务器上不需要
dotnet ef
工具：
bash
undefined

Build the bundle

构建迁移包

dotnet ef migrations bundle
--project src/MyApp.Infrastructure
--startup-project src/MyApp.Api
--output efbundle
--self-contained
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

在生产环境运行——通过--connection显式传递连接字符串

./efbundle --connection "Host=prod-db;Database=myapp;Username=deploy;Password=..."
./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

在DbContext的OnConfiguring或appsettings.json中设置连接字符串键，然后在运行时传递环境变量：

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

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

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

—
undefined
undefined

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.
  1. 始终为生产部署生成幂等脚本（使用
    --idempotent
    参数）。
  2. 生产环境中绝不要在应用启动时调用
    Database.Migrate()
    ——它会与水平扩展产生竞争，且不支持回滚。使用迁移包或从CI/CD应用的幂等脚本。
  3. 保持迁移为增量式——添加带默认值的列、添加表、添加索引。避免在同一版本的代码变更中包含破坏性变更（删除列、重命名表）。
  4. 查看生成的代码——EF Core迁移脚手架可能会生成意外的SQL。始终检查
    Up()
    和
    Down()
    方法。
  5. 使用独立的迁移项目——将迁移放在基础设施项目中，而不是API项目中。显式指定
    --project
    和
    --startup-project
    参数。

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.
使用
HasData()
添加应作为迁移一部分的参考数据：
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" });
}
重要提示： 
HasData()
使用主键值进行标识。在后续迁移中更改种子数据的主键会删除旧行并插入新行——不会原地更新。

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.
EF Core拦截器允许在不修改实体逻辑的情况下，将横切关注点注入到数据库管道中。拦截器会对其类型的所有操作生效。

SaveChanges Interceptor: Automatic Audit Timestamps

SaveChanges拦截器：自动审计时间戳

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; }
}
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);
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);
    }
}
结合全局查询过滤器，默认排除软删除的实体：
csharp
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);
}

// 需要时绕过过滤器（如管理员查询）
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);
    }
}
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>();
csharp
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
    options.UseNpgsql(connectionString)
           .AddInterceptors(
               sp.GetRequiredService<AuditTimestampInterceptor>(),
               sp.GetRequiredService<SoftDeleteInterceptor>()));

// 在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.
对于执行频率极高且结构相同的查询，编译查询可以消除每次调用时表达式树翻译的开销：
csharp
public static class CompiledQueries
{
    // 单结果编译查询——委托不接受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));

    // 多结果编译查询返回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));
}

// 使用示例
var order = await CompiledQueries.GetOrderById(db, orderId);

// IAsyncEnumerable结果通过WithCancellation支持取消：
await foreach (var o in CompiledQueries.GetOrdersByCustomer(db, customerId)
    .WithCancellation(ct))
{
    // 处理每个订单
}
使用场景： 对于每秒执行数千次的查询，编译查询能带来显著的性能提升。对于典型的CRUD端点，标准LINQ已足够——不要过早优化。
取消限制： 单结果编译查询委托（
Task<T?>
）不接受
CancellationToken
。如果需要逐调用取消，请使用标准异步LINQ（
FirstOrDefaultAsync(ct)
）而非编译查询。多结果编译查询（
IAsyncEnumerable<T>
）通过异步枚举的
.WithCancellation(ct)
支持取消。

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));
临时数据库故障（网络中断、故障转移）应通过自动重试处理。每个提供程序都有内置的执行策略：
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.
当需要将多个
SaveChangesAsync
调用包装在单个逻辑事务中并支持重试时：
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);
});
重要提示： 重试时会重新执行整个委托，包括事务。确保逻辑是幂等的，或使用数据库级别的唯一性约束防止重复。

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
  • 保持DbContext短生命周期——Web应用中每个请求一个实例，后台服务中通过
    IDbContextFactory<T>
    每个工作单元一个实例
  • 只读查询默认使用AsNoTracking——仅在需要变更检测时启用跟踪
  • 包含多个集合时使用拆分查询——避免笛卡尔积爆炸
  • 生产环境中绝不要在启动时调用Database.Migrate()——使用迁移包或幂等脚本
  • 通过DI注册拦截器——避免手动创建拦截器实例
  • 启用连接弹性——临时故障是云数据库中的常见情况

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.
  1. 不要将DbContext注入单例服务——
    DbContext
    是作用域服务。将其注入单例会捕获过期实例。请改用
    IDbContextFactory<T>
    。
  2. 不要忘记传播CancellationToken——将
    ct
    传递给所有
    ToListAsync()
    、
    FirstOrDefaultAsync()
    、
    SaveChangesAsync()
    及其他EF Core异步方法。省略它会阻止请求优雅取消。
  3. 不要在迁移同时使用Database.EnsureCreated()——
    EnsureCreated()
    会在没有迁移历史的情况下创建架构，导致后续迁移失败。仅在无迁移的测试场景中使用它。
  4. 不要假设SaveChangesAsync在多次调用间隐式支持事务——每个
    SaveChangesAsync()
    都是独立的事务。当需要原子性时，将多个保存操作包装在显式的
    BeginTransactionAsync()
    / 
    CommitAsync()
    块中。
  5. 不要硬编码连接字符串——从配置中读取（
    builder.Configuration.GetConnectionString("...")
    ），生产环境中通过环境变量注入。
  6. 不要忘记列出所需的NuGet包——必须显式引用EF Core提供程序包（
    Microsoft.EntityFrameworkCore.SqlServer
    、
    Npgsql.EntityFrameworkCore.PostgreSQL
    ）和设计时包（
    Microsoft.EntityFrameworkCore.Design
    ）。

References

参考资料