EF Core关联导航属性时如何减少JOIN查询列数以优化性能？

基于ASP.NET Core Web API、Entity Framework Core和Azure SQL数据库的项目，实体模型如下：

[Table("Parent")]
public record Parent
{
    [Key]
    public string ParentId { get; set; }

    public virtual ICollection<Children1> Children1s { get; set; }

    // 其他属性
}

[Table("Children1")]
public record Children1
{
    [Key]
    public string Children1Id { get; set; }

    [ForeignKey("ParentId")]
    public virtual Parent Parent { get; set; }

    public virtual ICollection<Children2> Children2s { get; set; }

    public DateTime JoiningDate { get; set; } // 排序字段
    // 近200个其他属性
}

// Children2实体简化
public record Children2
{
    [Key]
    public string Children2Id { get; set; }
    // 其他属性
}

需求：获取所有Parent数据，以及每个Parent关联的最新（按JoiningDate降序）Children1数据，同时获取该Children1关联的Children2数据。

已尝试两种查询方式，但均因EF Core生成的SQL中LEFT JOIN包含Children1的所有列，导致Azure SQL查询速度极慢：

1. 投影查询（存在变量名错误：d.Children1s应为p.Children1s）
2. 使用Include+ThenInclude的方式（会加载完整的Children1实体）

全局已配置UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)，且已为查询字段创建索引，但性能提升有限。

1. 修正投影查询，精准控制返回列 #

投影是避免加载冗余列的核心方式，需确保只选择需要的字段，同时避免在子查询中提前调用ToList()（交由EF Core自动处理，避免客户端求值）：

var query = context.Parents
    .AsNoTracking()
    .Select(p => new 
    {
        p.ParentId,
        p.Description,
        // 获取每个Parent的最新Children1及关联的Children2
        LatestChildren1 = p.Children1s
            .OrderByDescending(c => c.JoiningDate)
            .Take(1)
            .Select(c => new 
            {
                c.Children1Id,
                c.Description,
                // 仅选择Children2需要的字段
                Children2s = c.Children2s.Select(c2 => new 
                {
                    c2.Children2Id,
                    c2.SomeNeededField
                })
            })
            .FirstOrDefault()
    })
    .ToList();

原理：这种方式EF Core会生成仅包含指定列的SQL，不会加载Children1的其他冗余字段，大幅减少数据传输和JOIN开销。

2. 先获取最新Children1的ID，再关联查询 #

如果投影查询仍有性能瓶颈，可以先通过分组获取每个Parent对应的最新Children1的ID，再通过ID精确关联查询所需字段：

// 第一步：获取每个Parent的最新Children1Id
var latestChildren1Ids = context.Children1
    .GroupBy(c => c.ParentId)
    .Select(g => new 
    {
        ParentId = g.Key,
        LatestChildren1Id = g.OrderByDescending(c => c.JoiningDate)
                            .Select(c => c.Children1Id)
                            .FirstOrDefault()
    })
    .ToList();

// 第二步：关联查询Parent、最新Children1及Children2
var result = context.Parents
    .AsNoTracking()
    .Join(
        latestChildren1Ids,
        p => p.ParentId,
        l => l.ParentId,
        (p, l) => new { Parent = p, LatestChildren1Id = l.LatestChildren1Id }
    )
    .Select(x => new 
    {
        x.Parent.ParentId,
        x.Parent.Description,
        LatestChildren1 = context.Children1
            .Where(c => c.Children1Id == x.LatestChildren1Id)
            .Select(c => new 
            {
                c.Children1Id,
                c.Description,
                Children2s = c.Children2s.Select(c2 => new 
                {
                    c2.Children2Id,
                    c2.SomeNeededField
                })
            })
            .FirstOrDefault()
    })
    .ToList();

原理：先通过分组获取最新ID，再通过ID精确查询，避免主查询中对大表进行复杂排序和JOIN，降低SQL执行计划的开销。

3. 使用原生SQL直接控制查询列 #

如果LINQ查询仍无法满足需求，直接编写原生SQL是最直接的方式，完全掌控返回的列：

// 定义DTO用于映射查询结果
public record ParentWithLatestChildrenDto
{
    public string ParentId { get; set; }
    public string ParentDescription { get; set; }
    public string Children1Id { get; set; }
    public string Children1Description { get; set; }
    public string Children2Id { get; set; }
    public string Children2SomeNeededField { get; set; }
}

// 原生SQL查询
var sql = @"
SELECT 
    p.ParentId,
    p.Description AS ParentDescription,
    c1.Children1Id,
    c1.Description AS Children1Description,
    c2.Children2Id,
    c2.SomeNeededField AS Children2SomeNeededField
FROM Parent p
LEFT JOIN (
    -- 子查询获取每个Parent的最新Children1
    SELECT * FROM (
        SELECT 
            Children1Id,
            ParentId,
            Description,
            JoiningDate,
            ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY JoiningDate DESC) AS RowNum
        FROM Children1
    ) t WHERE t.RowNum = 1
) c1 ON p.ParentId = c1.ParentId
LEFT JOIN Children2 c2 ON c1.Children1Id = c2.Children1Id";

var result = context.Set<ParentWithLatestChildrenDto>()
    .FromSqlRaw(sql)
    .ToList();

原理：原生SQL完全由你掌控，只选择需要的列，彻底避免EF Core自动生成不必要的字段，适合复杂查询场景。

4. 补充索引优化 #

确保存在以下组合索引，进一步提升查询速度：

• Children1表：(ParentId, JoiningDate DESC)（快速定位每个Parent的最新Children1）
• Children2表：(Children1Id)（快速关联Children2）

内容的提问来源于stack exchange，提问作者Yoda sw