Back to Details

optimizing-ef-core-queries

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Optimizing EF Core Queries

优化EF Core查询

When to Use

适用场景

  • EF Core queries are slow or generating too many SQL statements
  • Database CPU/IO is high due to ORM inefficiency
  • N+1 query patterns are detected in logs
  • Large result sets cause memory pressure
  • EF Core查询运行缓慢，或生成过多SQL语句
  • 因ORM效率低下导致数据库CPU/IO占用过高
  • 日志中检测到N+1查询模式
  • 大结果集引发内存压力

When Not to Use

不适用场景

  • The user is using Dapper or raw ADO.NET (not EF Core)
  • The performance issue is database-side (missing indexes, bad schema)
  • The user is building a new data access layer from scratch
  • 用户使用的是Dapper或原生ADO.NET（而非EF Core）
  • 性能问题出在数据库侧（索引缺失、schema设计不合理）
  • 用户正在从头搭建新的数据访问层

Inputs

输入参数

InputRequiredDescription
Slow EF Core queriesYesThe LINQ queries or DbContext usage to optimize
SQL output or logsNoEF Core generated SQL or query execution logs
输入项必填描述
慢EF Core查询是待优化的LINQ查询或DbContext使用代码
SQL输出或日志否EF Core生成的SQL或查询执行日志

Workflow

操作流程

Step 1: Enable query logging to see the actual SQL

步骤1：启用查询日志查看实际生成的SQL

csharp
// In Program.cs or DbContext configuration:
optionsBuilder
    .UseSqlServer(connectionString)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging()  // shows parameter values (dev only!)
    .EnableDetailedErrors();
Or use the 
Microsoft.EntityFrameworkCore
log category:
json
{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}
csharp
// In Program.cs or DbContext configuration:
optionsBuilder
    .UseSqlServer(connectionString)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging()  // shows parameter values (dev only!)
    .EnableDetailedErrors();
或者使用
Microsoft.EntityFrameworkCore
日志分类：
json
{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

Step 2: Fix N+1 query patterns

步骤2：修复N+1查询模式

The #1 EF Core performance killer. Happens when loading related entities in a loop.
Before (N+1 — 1 query for orders + N queries for items):
csharp
var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
    // Each access triggers a lazy-load query!
    var items = order.Items.Count;
}
After (eager loading — 1 or 2 queries total):
csharp
// Option 1: Include (JOIN)
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Option 2: Split query (separate SQL, avoids cartesian explosion)
var orders = await db.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

// Option 3: Explicit projection (best - only fetches needed columns)
var orderSummaries = await db.Orders
    .Select(o => new OrderSummary
    {
        OrderId = o.Id,
        Total = o.Items.Sum(i => i.Price),
        ItemCount = o.Items.Count
    })
    .ToListAsync();
When to use Split vs Single query:
ScenarioUse
1 level of IncludeSingle query (default)
Multiple Includes (Cartesian risk)
AsSplitQuery()
Include with large child collections
AsSplitQuery()
Need transaction consistencySingle query
EF Core性能的头号杀手。 在循环中加载关联实体时会触发该问题。
优化前（N+1 —— 1次查询获取订单 + N次查询获取订单项）：
csharp
var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
    // Each access triggers a lazy-load query!
    var items = order.Items.Count;
}
优化后（预先加载 —— 总共仅1或2次查询）：
csharp
// Option 1: Include (JOIN)
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Option 2: Split query (separate SQL, avoids cartesian explosion)
var orders = await db.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

// Option 3: Explicit projection (best - only fetches needed columns)
var orderSummaries = await db.Orders
    .Select(o => new OrderSummary
    {
        OrderId = o.Id,
        Total = o.Items.Sum(i => i.Price),
        ItemCount = o.Items.Count
    })
    .ToListAsync();
拆分查询 vs 单查询适用场景：
场景适用方案
仅1层Include单查询（默认）
多层Include（存在笛卡尔积风险）
AsSplitQuery()
Include关联大型子集合
AsSplitQuery()
需要事务一致性单查询

Step 3: Use NoTracking for read-only queries

步骤3：只读查询使用NoTracking

Change tracking overhead is significant. Disable it when you don't need to update entities:
csharp
// Per-query
var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

// Global default for read-heavy apps
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
Use 
AsNoTrackingWithIdentityResolution()
when the query returns duplicate entities to avoid duplicated objects in memory.
变更跟踪的开销非常可观。 当你不需要更新实体时可以禁用该功能：
csharp
// Per-query
var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

// Global default for read-heavy apps
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
当查询返回重复实体时，使用
AsNoTrackingWithIdentityResolution()
可避免内存中出现重复对象。

Step 4: Use compiled queries for hot paths

步骤4：热点路径使用编译查询

csharp
// Define once as static
private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Orders
            .Include(o => o.Items)
            .FirstOrDefault(o => o.Id == id));

// Use repeatedly — skips query compilation overhead
var order = await GetOrderById(db, orderId);
csharp
// Define once as static
private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Orders
            .Include(o => o.Items)
            .FirstOrDefault(o => o.Id == id));

// Use repeatedly — skips query compilation overhead
var order = await GetOrderById(db, orderId);

Step 5: Avoid common query traps

步骤5：规避常见查询陷阱

TrapProblemFix
ToList()
before 
Where()
Loads entire table into memoryFilter first: 
.Where().ToList()
Count()
to check existence		Scans all rowsUse 
.Any()
instead
.Select()
after 
.Include()
Include is ignored with projectionRemove Include, use Select only
string.Contains()
in Where		May not translate, falls to client evalUse 
EF.Functions.Like()
for SQL LIKE
Calling 
.ToList()
inside 
Select()
Causes nested queriesUse projection with 
Select
all the way
陷阱问题修复方案
Where()
前调用
ToList()
将整张表加载到内存先过滤再转列表：
.Where().ToList()
用
Count()
判断是否存在		扫描全表改用
.Any()
.Include()
后调用
.Select()
投影操作会忽略Include移除Include，仅使用Select
Where中使用
string.Contains()
可能无法转换为SQL，降级为客户端求值使用
EF.Functions.Like()
实现SQL LIKE效果
Select()
内部调用
.ToList()
导致嵌套查询全程使用
Select
做投影

Step 6: Use raw SQL or FromSql for complex queries

步骤6：复杂查询使用原生SQL或FromSql

When LINQ can't express it efficiently:
csharp
var results = await db.Orders
    .FromSqlInterpolated($@"
        SELECT o.* FROM Orders o
        INNER JOIN (
            SELECT OrderId, SUM(Price) as Total
            FROM OrderItems
            GROUP BY OrderId
            HAVING SUM(Price) > {minTotal}
        ) t ON o.Id = t.OrderId")
    .AsNoTracking()
    .ToListAsync();
当LINQ无法高效表达查询逻辑时：
csharp
var results = await db.Orders
    .FromSqlInterpolated($@"
        SELECT o.* FROM Orders o
        INNER JOIN (
            SELECT OrderId, SUM(Price) as Total
            FROM OrderItems
            GROUP BY OrderId
            HAVING SUM(Price) > {minTotal}
        ) t ON o.Id = t.OrderId")
    .AsNoTracking()
    .ToListAsync();

Validation

效果验证

  • SQL logging shows expected number of queries (no N+1)
  • Read-only queries use 
    AsNoTracking()
  • Hot-path queries use compiled queries
  • No client-side evaluation warnings in logs
  • Include/split strategy matches data shape
  • SQL日志显示查询数量符合预期（无N+1问题）
  • 只读查询使用了
    AsNoTracking()
  • 热点路径查询使用了编译查询
  • 日志中无客户端求值警告
  • Include/拆分查询策略匹配数据结构

Common Pitfalls

常见误区

PitfallSolution
Lazy loading silently creating N+1Remove 
Microsoft.EntityFrameworkCore.Proxies
or disable lazy loading
Global query filters forgotten in perf analysisCheck 
HasQueryFilter
in model config; use 
IgnoreQueryFilters()
if needed
DbContext
kept alive too long		DbContext should be scoped (per-request); don't cache it
Batch updates fetching then savingEF Core 7+: use 
ExecuteUpdateAsync
/ 
ExecuteDeleteAsync
for bulk operations
String interpolation in 
FromSqlRaw
SQL injection risk — use 
FromSqlInterpolated
(parameterized)
误区解决方案
懒加载静默触发N+1移除
Microsoft.EntityFrameworkCore.Proxies
包或禁用懒加载
性能分析时忽略全局查询过滤器检查模型配置中的
HasQueryFilter
；必要时使用
IgnoreQueryFilters()
DbContext
存活时间过长		DbContext应采用作用域生命周期（每次请求一个实例），不要缓存它
批量更新先查再存EF Core 7+：使用
ExecuteUpdateAsync
/ 
ExecuteDeleteAsync
执行批量操作
FromSqlRaw
中使用字符串插值		存在SQL注入风险 —— 使用
FromSqlInterpolated
（参数化查询）