预加载 Eager Loading

使用 Include 和 ThenInclude 高效加载关联数据

📖 目录

• 什么是预加载
• Include 基础用法
• ThenInclude 多级加载
• 过滤 Include
• 性能注意事项
• 常见陷阱
• 最佳实践

---

什么是预加载

三种加载策略对比

// 1. 预加载 (Eager Loading) - 本文主题
var order = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .FirstOrDefaultAsync(o => o.Id == id);

// 2. 延迟加载 (Lazy Loading)
// 需要安装 Microsoft.EntityFrameworkCore.Proxies
// 访问导航属性时自动加载

// 3. 显式加载 (Explicit Loading)
var order = await context.Orders.FindAsync(id);
await context.Entry(order).Reference(o => o.Customer).LoadAsync();

---

预加载的优势

┌─────────────────────────────────────┐
│      预加载 vs N+1 查询             │
├─────────────────────────────────────┤
│ N+1 查询:                           │
│ SELECT * FROM Orders                │
│ SELECT * FROM Customers WHERE Id=1  │
│ SELECT * FROM Customers WHERE Id=2  │
│ ... (N 次查询)                      │
│                                     │
│ 预加载:                             │
│ SELECT * FROM Orders                │
│ SELECT * FROM Customers WHERE Id IN │
│   (1, 2, 3, ...)                    │
│ (2 次查询)                          │
└─────────────────────────────────────┘

性能提升: 10-100 倍（取决于数据量）

---

Include 基础用法

1. 加载单个导航属性

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    
    // 导航属性
    public Customer Customer { get; set; }
}

// 预加载 Customer
var order = await context.Orders
    .Include(o => o.Customer)
    .FirstOrDefaultAsync(o => o.Id == orderId);

Console.WriteLine($"Order {order.Id} by {order.Customer.Name}");

生成的 SQL:

-- 查询 1: Orders
SELECT * FROM Orders WHERE Id = @id

-- 查询 2: Customers
SELECT * FROM Customers WHERE Id IN (@customerId1, @customerId2, ...)

---

2. 加载集合导航属性

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // 集合导航属性
    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

// 预加载所有订单
var customer = await context.Customers
    .Include(c => c.Orders)
    .FirstOrDefaultAsync(c => c.Id == customerId);

foreach (var order in customer.Orders)
{
    Console.WriteLine($"Order {order.Id}: ${order.TotalAmount}");
}

---

3. 多个 Include

var order = await context.Orders
    .Include(o => o.Customer)        // 加载客户
    .Include(o => o.Items)           // 加载订单项
    .Include(o => o.Payment)         // 加载支付信息
    .FirstOrDefaultAsync(o => o.Id == orderId);

注意: 每个 Include 都会生成额外的 SQL 查询（使用 AsSplitQuery 时）

---

ThenInclude 多级加载

1. 二级导航属性

public class Order
{
    public int Id { get; set; }
    public ICollection<OrderItem> Items { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    
    // 二级导航
    public Product Product { get; set; }
}

// 加载 Order → Items → Product
var order = await context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

// 使用
foreach (var item in order.Items)
{
    Console.WriteLine($"{item.Product.Name}: {item.Quantity} x ${item.UnitPrice}");
}

---

2. 多级深度加载

public class Category
{
    public int Id { get; set; }
    public ICollection<Product> Products { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public int SupplierId { get; set; }
    
    public Supplier Supplier { get; set; }
    public ICollection<Review> Reviews { get; set; }
}

public class Review
{
    public int Id { get; set; }
    public int UserId { get; set; }
    
    public User User { get; set; }
}

// 四级加载: Category → Products → Reviews → User
var category = await context.Categories
    .Include(c => c.Products)
        .ThenInclude(p => p.Reviews)
            .ThenInclude(r => r.User)
    .Include(c => c.Products)
        .ThenInclude(p => p.Supplier)
    .FirstOrDefaultAsync(c => c.Id == categoryId);

---

3. 同一层级多个导航

// 从同一个实体加载多个导航属性
var order = await context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Include(o => o.Items)
        .ThenInclude(i => i.Discount)
    .FirstOrDefaultAsync(o => o.Id == orderId);

注意: 需要重复 Include(o => o.Items)

---

过滤 Include

EF Core 5.0+ 支持

// 1. 过滤集合导航属性
var customer = await context.Customers
    .Include(c => c.Orders.Where(o => o.OrderDate >= DateTime.Today.AddDays(-30)))
    .FirstOrDefaultAsync(c => c.Id == customerId);

// 只加载最近 30 天的订单

// 2. 排序 + 限制数量
var customer = await context.Customers
    .Include(c => c.Orders
        .OrderByDescending(o => o.OrderDate)
        .Take(10))
    .FirstOrDefaultAsync(c => c.Id == customerId);

// 只加载最新的 10 个订单

// 3. 条件过滤
var category = await context.Categories
    .Include(c => c.Products.Where(p => p.IsActive && p.Price > 50))
    .FirstOrDefaultAsync(c => c.Id == categoryId);

// 只加载激活且价格大于 50 的产品

---

投影中的过滤 Include

var customers = await context.Customers
    .Select(c => new
    {
        c.Id,
        c.Name,
        RecentOrders = c.Orders
            .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
            .OrderByDescending(o => o.OrderDate)
            .Take(5)
            .Select(o => new
            {
                o.Id,
                o.OrderDate,
                o.TotalAmount
            })
            .ToList()
    })
    .ToListAsync();

---

性能注意事项

1. JOIN 爆炸问题

// ❌ 警告: 多个 Include 导致复杂 JOIN
var order = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Include(o => o.Payments)
    .Include(o => o.Shipping)
    .FirstOrDefaultAsync(o => o.Id == orderId);

// 生成的 SQL 包含多个 JOIN,可能导致:
// - 笛卡尔积
// - 性能下降
// - 内存占用过大

---

解决方案 1: AsSplitQuery

// ✅ 拆分为多个简单查询
var order = await context.Orders
    .AsSplitQuery() // 关键!
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Include(o => o.Payments)
    .Include(o => o.Shipping)
    .FirstOrDefaultAsync(o => o.Id == orderId);

// 生成 4-5 个简单查询,而非一个复杂 JOIN

性能对比（100 个订单项）:

方式	查询数	时间	内存
默认（JOIN）	1	500ms	80MB
AsSplitQuery	4	150ms	30MB
提升	-	3.3x	62%

---

解决方案 2: 投影查询

// ✅✅ 最佳: 只查询需要的字段
var order = await context.Orders
    .Where(o => o.Id == orderId)
    .Select(o => new
    {
        o.Id,
        o.OrderDate,
        CustomerName = o.Customer.Name,
        Items = o.Items.Select(i => new
        {
            i.Product.Name,
            i.Quantity,
            i.UnitPrice
        }).ToList(),
        TotalAmount = o.Items.Sum(i => i.Quantity * i.UnitPrice)
    })
    .FirstOrDefaultAsync();

优势:

• ✅ 单次查询
• ✅ 最小数据传输
• ✅ 无跟踪开销

---

2. 避免过度加载

// ❌ 错误: 加载不必要的数据
var products = await context.Products
    .Include(p => p.Category)
    .Include(p => p.Supplier)
    .Include(p => p.Reviews)
        .ThenInclude(r => r.User)
    .Include(p => p.OrderItems)
        .ThenInclude(oi => oi.Order)
            .ThenInclude(o => o.Customer)
    .ToListAsync();

// ✅ 正确: 按需加载
var products = await context.Products
    .Include(p => p.Category) // 只需要分类
    .ToListAsync();

---

3. 分页时的 Include

// ❌ 错误: Include 后分页可能不准确
var customers = await context.Customers
    .Include(c => c.Orders)
    .Skip(0)
    .Take(10)
    .ToListAsync();

// ✅ 正确: 先分页,再加载关联数据
var customerIds = await context.Customers
    .OrderBy(c => c.Name)
    .Skip(0)
    .Take(10)
    .Select(c => c.Id)
    .ToListAsync();

var customers = await context.Customers
    .Where(c => customerIds.Contains(c.Id))
    .Include(c => c.Orders)
    .ToListAsync();

---

常见陷阱

1. 忘记 Include

// ❌ 错误
var order = await context.Orders.FindAsync(orderId);
Console.WriteLine(order.Customer.Name); 
// ⚠️ Customer 为 null!

// ✅ 正确
var order = await context.Orders
    .Include(o => o.Customer)
    .FirstOrDefaultAsync(o => o.Id == orderId);

---

2. Include 不能用于投影

// ❌ 错误
var result = await context.Orders
    .Include(o => o.Customer)
    .Select(o => new { o.Id, o.Customer.Name })
    .ToListAsync();
// 警告: Include 被忽略

// ✅ 正确: 直接在 Select 中访问
var result = await context.Orders
    .Select(o => new { o.Id, o.Customer.Name })
    .ToListAsync();

---

3. 循环中的 Include

// ❌ 错误: N+1 查询
var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
    var customer = await context.Customers
        .Include(c => c.Address)
        .FirstOrDefaultAsync(c => c.Id == order.CustomerId);
}

// ✅ 正确: 一次性 Include
var orders = await context.Orders
    .Include(o => o.Customer)
        .ThenInclude(c => c.Address)
    .ToListAsync();

---

4. 字符串形式的 Include（避免使用）

// ❌ 不推荐: 容易出错,无编译时检查
var order = await context.Orders
    .Include("Customer")
    .Include("Items.Product")
    .FirstOrDefaultAsync(o => o.Id == orderId);

// ✅ 推荐: Lambda 表达式
var order = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

---

最佳实践

1. 根据场景选择加载策略

// API 返回: 投影查询
[HttpGet("{id}")]
public async Task<ActionResult<OrderDto>> GetOrder(int id)
{
    var order = await context.Orders
        .Where(o => o.Id == id)
        .Select(o => new OrderDto
        {
            Id = o.Id,
            CustomerName = o.Customer.Name,
            Items = o.Items.Select(i => new OrderItemDto
            {
                ProductName = i.Product.Name,
                Quantity = i.Quantity
            }).ToList()
        })
        .FirstOrDefaultAsync();
    
    return Ok(order);
}

// 需要修改实体: Include
var order = await context.Orders
    .Include(o => o.Items)
    .FirstOrDefaultAsync(o => o.Id == id);

order.Status = OrderStatus.Shipped;
await context.SaveChangesAsync();

// 只读展示: AsNoTracking + Include
var orders = await context.Orders
    .AsNoTracking()
    .Include(o => o.Customer)
    .ToListAsync();

---

2. 全局配置 AsSplitQuery

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        // 所有查询默认拆分
        sqlOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
    });
});

---

3. 监控 Include 性能

public class IncludePerformanceInterceptor : DbCommandInterceptor
{
    private readonly ILogger<IncludePerformanceInterceptor> _logger;

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        if (command.CommandText.Contains("JOIN"))
        {
            _logger.LogInformation("Complex query with JOINs detected");
        }
        
        return base.ReaderExecuting(command, eventData, result);
    }
}

---

4. 缓存 Include 结果

public async Task<Customer> GetCustomerWithOrdersAsync(int customerId)
{
    string cacheKey = $"customer_{customerId}_with_orders";
    
    if (_cache.TryGetValue(cacheKey, out Customer customer))
    {
        return customer;
    }
    
    customer = await context.Customers
        .AsNoTracking()
        .Include(c => c.Orders)
        .FirstOrDefaultAsync(c => c.Id == customerId);
    
    _cache.Set(cacheKey, customer, TimeSpan.FromMinutes(10));
    
    return customer;
}

---

📊 性能对比总结

不同加载策略对比

策略	查询数	适用场景	性能
N+1 查询	N+1	❌ 避免	⭐
Include	1-2	中小数据集	⭐⭐⭐
AsSplitQuery	多个简单	大数据集	⭐⭐⭐⭐
投影查询	1	API/只读	⭐⭐⭐⭐⭐

---

实际案例数据

电商订单详情页（100 个订单项）:
├─ N+1 查询: 102 次查询, 3000ms
├─ Include (JOIN): 1 次查询, 800ms
├─ AsSplitQuery: 3 次查询, 200ms
└─ 投影查询: 1 次查询, 100ms

产品列表页（1000 个产品）:
├─ N+1 查询: 1001 次查询, 超时
├─ Include (JOIN): 1 次查询, 2000ms, 500MB 内存
├─ AsSplitQuery: 2 次查询, 400ms, 100MB 内存
└─ 投影查询: 1 次查询, 150ms, 20MB 内存

---

📚 延伸阅读

• LINQ 查询基础
• 延迟加载 Lazy Loading
• 投影查询 Select DTO
• 避免 N+1 查询

---

💡 小结

核心要点:

• ✅ Include 用于预加载关联数据
• ✅ ThenInclude 用于多级加载
• ✅ 多个 Include 时使用 AsSplitQuery
• ✅ API 返回优先使用投影查询
• ✅ 避免过度加载不必要的数据
• ✅ 注意分页时的 Include 顺序

使用原则:

少量数据 → Include
大量数据 → AsSplitQuery
API 返回 → 投影查询
需要修改 → Include + 跟踪
只读展示 → Include + AsNoTracking

下一步:

1. 学习 延迟加载 Lazy Loading
2. 掌握 投影查询
3. 理解 避免 N+1 查询