dotnet-entity-framework-core

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Entity Framework Core

Trigger On

触发场景

working on
```
DbContext
```
, migrations, model configuration, or EF queries
reviewing tracking, loading, performance, or transaction behavior
porting data access from EF6 or custom repositories to EF Core
optimizing slow database queries

处理
```
DbContext
```
、迁移、模型配置或EF查询相关工作时
评审跟踪机制、加载策略、性能或事务行为时
将数据访问方案从EF6或自定义仓储移植到EF Core时
优化慢速数据库查询时

Documentation

参考文档

References

参考资料

patterns.md - Query patterns, tracking strategies, loading strategies, projections, compiled queries, pagination, and temporal tables
anti-patterns.md - Common EF Core mistakes including N+1 queries, large contexts, generic repositories, and missing indexes

patterns.md - 查询模式、跟踪策略、加载策略、投影、编译查询、分页和临时表
anti-patterns.md - EF Core常见错误，包括N+1查询、上下文过大、通用仓储、索引缺失

Workflow

工作流程

Prefer EF Core for new development unless a documented gap requires Dapper or raw SQL
Keep
DbContext
lifetime scoped — align with unit of work
Review query translation — check generated SQL, avoid N+1
Treat migrations as first-class — reviewable, not throwaway
Be deliberate about provider behavior — cross-provider but not identical
Validate with query inspection — not just in-memory mental model

新开发优先选择EF Core，除非存在明确的功能缺口需要使用Dapper或原生SQL
保持
DbContext
生命周期为作用域级别 —— 与工作单元对齐
评审查询转换结果 —— 检查生成的SQL，避免N+1问题
将迁移视为一等公民 —— 可评审，不可随意丢弃
谨慎处理不同provider的行为差异 —— 支持跨provider但行为并非完全一致
通过查询检查做验证 —— 不要仅依赖内存中的思维模型

DbContext Patterns

DbContext 最佳实践模式

Basic Configuration

基础配置

csharp

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

// Entity Configuration (Fluent API)
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Name).HasMaxLength(200).IsRequired();
        builder.HasIndex(p => p.Sku).IsUnique();
        builder.HasMany(p => p.OrderItems).WithOne(oi => oi.Product);
    }
}

csharp

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

// Entity Configuration (Fluent API)
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Name).HasMaxLength(200).IsRequired();
        builder.HasIndex(p => p.Sku).IsUnique();
        builder.HasMany(p => p.OrderItems).WithOne(oi => oi.Product);
    }
}

Registration with DI

依赖注入注册

csharp

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .EnableSensitiveDataLogging()  // Dev only
           .EnableDetailedErrors());      // Dev only

// Or with pooling (better performance)
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

csharp

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .EnableSensitiveDataLogging()  // Dev only
           .EnableDetailedErrors());      // Dev only

// Or with pooling (better performance)
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

Query Patterns

查询模式

Use AsNoTracking for Read-Only

只读场景使用AsNoTracking

csharp

// Bad - tracks entities unnecessarily
var products = await db.Products.ToListAsync();

// Good - no tracking overhead
var products = await db.Products
    .AsNoTracking()
    .ToListAsync();

csharp

// Bad - tracks entities unnecessarily
var products = await db.Products.ToListAsync();

// Good - no tracking overhead
var products = await db.Products
    .AsNoTracking()
    .ToListAsync();

Project to DTOs

投影到DTO

csharp

// Bad - loads entire entity graph
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToListAsync();

// Good - loads only needed data
var orders = await db.Orders
    .Select(o => new OrderDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Price)
    })
    .ToListAsync();

csharp

// Bad - loads entire entity graph
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToListAsync();

// Good - loads only needed data
var orders = await db.Orders
    .Select(o => new OrderDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Price)
    })
    .ToListAsync();

Avoid N+1 Queries

避免N+1查询

csharp

// Bad - N+1 problem
foreach (var order in orders)
{
    var items = await db.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync();
}

// Good - eager loading
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Good - split query for large graphs
var orders = await db.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

csharp

// Bad - N+1 problem
foreach (var order in orders)
{
    var items = await db.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync();
}

// Good - eager loading
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Good - split query for large graphs
var orders = await db.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

Compiled Queries (EF Core 9)

编译查询（EF Core 9）

csharp

// Pre-compiled for frequently used queries
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Products.FirstOrDefault(p => p.Id == id));

// Usage
var product = await GetProductById(db, productId);

csharp

// Pre-compiled for frequently used queries
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Products.FirstOrDefault(p => p.Id == id));

// Usage
var product = await GetProductById(db, productId);

Migration Patterns

迁移模式

Creating Migrations

创建迁移

bash

undefined

bash

undefined

Add migration

dotnet ef migrations add AddProductIndex

Apply to database

dotnet ef database update

Generate SQL script

dotnet ef migrations script --idempotent -o migrate.sql

undefined

dotnet ef migrations script --idempotent -o migrate.sql

undefined

Data Migrations

数据迁移

csharp

public partial class AddProductIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "IX_Products_Sku",
            table: "Products",
            column: "Sku",
            unique: true);

        // Data migration (if needed)
        migrationBuilder.Sql(@"
            UPDATE Products
            SET NormalizedName = UPPER(Name)
            WHERE NormalizedName IS NULL");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Products_Sku",
            table: "Products");
    }
}

csharp

public partial class AddProductIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "IX_Products_Sku",
            table: "Products",
            column: "Sku",
            unique: true);

        // Data migration (if needed)
        migrationBuilder.Sql(@"
            UPDATE Products
            SET NormalizedName = UPPER(Name)
            WHERE NormalizedName IS NULL");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Products_Sku",
            table: "Products");
    }
}

Anti-Patterns to Avoid

需要避免的反模式

Anti-Pattern	Why It's Bad	Better Approach
`ToList()` then filter	Loads all data to memory	Filter in query
Multiple DbContext per request	Transaction issues	Scoped lifetime
Lazy loading everywhere	N+1 queries	Explicit Include
Generic repository wrapper	Removes query power	Use DbContext directly
Ignoring generated SQL	Hidden performance issues	Log and review
`SaveChanges()` in loops	Many roundtrips	Batch then save

反模式	危害	优化方案
`ToList()` 后执行过滤	将所有数据加载到内存	在查询中执行过滤
单个请求创建多个DbContext	引发事务问题	使用作用域生命周期
全局启用懒加载	导致N+1查询	显式使用Include
封装通用仓储层	损失EF Core的查询能力	直接使用DbContext
忽略生成的SQL	隐藏性能问题	记录并评审SQL
循环中调用 `SaveChanges()`	产生多次网络往返	批量操作后统一保存

Performance Best Practices

性能最佳实践

Index frequently queried columns:

csharp

builder.HasIndex(p => p.CreatedAt);
builder.HasIndex(p => new { p.Category, p.Status });

Use pagination:

csharp

var page = await db.Products
    .OrderBy(p => p.Id)
    .Skip(pageSize * pageNumber)
    .Take(pageSize)
    .ToListAsync();

Batch updates (EF Core 7+):

csharp

await db.Products
    .Where(p => p.Category == "Obsolete")
    .ExecuteDeleteAsync();

await db.Products
    .Where(p => p.Category == "Sale")
    .ExecuteUpdateAsync(p => p.SetProperty(x => x.Price, x => x.Price * 0.9m));

Minimize network roundtrips:

csharp

// Bad - 3 roundtrips
var product = await db.Products.FindAsync(id);
var reviews = await db.Reviews.Where(r => r.ProductId == id).ToListAsync();
var related = await db.Products.Where(p => p.Category == product.Category).ToListAsync();

// Good - 1 roundtrip
var data = await db.Products
    .Where(p => p.Id == id)
    .Select(p => new
    {
        Product = p,
        Reviews = p.Reviews,
        Related = db.Products.Where(r => r.Category == p.Category).Take(5)
    })
    .FirstOrDefaultAsync();

为高频查询列添加索引:

csharp

builder.HasIndex(p => p.CreatedAt);
builder.HasIndex(p => new { p.Category, p.Status });

使用分页:

csharp

var page = await db.Products
    .OrderBy(p => p.Id)
    .Skip(pageSize * pageNumber)
    .Take(pageSize)
    .ToListAsync();

批量更新（EF Core 7+）:

csharp

await db.Products
    .Where(p => p.Category == "Obsolete")
    .ExecuteDeleteAsync();

await db.Products
    .Where(p => p.Category == "Sale")
    .ExecuteUpdateAsync(p => p.SetProperty(x => x.Price, x => x.Price * 0.9m));

减少网络往返次数:

csharp

// Bad - 3 roundtrips
var product = await db.Products.FindAsync(id);
var reviews = await db.Reviews.Where(r => r.ProductId == id).ToListAsync();
var related = await db.Products.Where(p => p.Category == product.Category).ToListAsync();

// Good - 1 roundtrip
var data = await db.Products
    .Where(p => p.Id == id)
    .Select(p => new
    {
        Product = p,
        Reviews = p.Reviews,
        Related = db.Products.Where(r => r.Category == p.Category).Take(5)
    })
    .FirstOrDefaultAsync();

Concurrency Patterns

并发处理模式

csharp

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ConcurrencyCheck]
    public int Version { get; set; }

    // Or use RowVersion
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// Handle concurrency conflicts
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    // Resolve conflict...
}

csharp

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ConcurrencyCheck]
    public int Version { get; set; }

    // Or use RowVersion
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// Handle concurrency conflicts
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    // Resolve conflict...
}

Deliver

交付产出

EF Core models and queries that match the domain
safer migrations and lifetime management
performance-aware data access decisions
proper indexing and query optimization

匹配业务领域的EF Core模型和查询
更安全的迁移和生命周期管理
兼顾性能的数据访问决策
合理的索引和查询优化

Validate

验证标准

query behavior is intentional (check SQL logs)
migrations are reviewable and correct
no N+1 queries in common paths
indexes exist for filtered/sorted columns
DbContext lifetime is scoped properly
concurrency is handled for critical entities

查询行为符合预期（检查SQL日志）
迁移可评审且逻辑正确
常用路径无N+1查询
过滤/排序列已创建对应索引
DbContext生命周期作用域配置正确
核心实体已做并发处理