EF Core 5分组查询时如何加载嵌套导航属性？

在.NET 5、EF Core 5、C# 9环境的项目中，需要从数据库某表查询分组数据并展示到UI表格，同时希望单次查询加载嵌套导航属性。当前遇到的问题是：分组操作后延迟加载失效，Include无法放在Select之后，现有代码无法获取导航属性数据，该如何实现？有没有更优方案？

目标实体类（对应数据库表） #

public class XXX : YYY
{
    public int SmthEntityId { get; set; }

    [ForeignKey(nameof(SmthEntityId))]
    public virtual SmthEntity SmthEntity { get; set; }

    public int AnotherEntityId { get; set; }

    [ForeignKey(nameof(AnotherEntityId))]
    public virtual AnotherEntity AnotherEntity { get; set; }

    /// <summary>
    /// 绑定分组后COUNT(*)的聚合结果（仅为个人实现思路）
    /// </summary>
    [NotMapped]
    public int Count { get; set; } = 1;
    
    // 其他属性
}

现有筛选、排序方法 #

public IEnumerable<XXXDto> Filter(XXXFilter filter)
{
    var query = _context.XXX
        // 这里的Include对最终结果无效
        //.Include(x => x.SmthEntity)
        //.Include(x => x.AnotherEntity)
    ;

    if (filter.SmthEntityId.HasValue)
        query = query.Where(x => x.SmthEntityId == filter.SmthEntityId);

    if (filter.AnotherEntityId.HasValue)
        query = query.Where(x => x.AnotherEntityId == filter.AnotherEntityId);

    // 分组后延迟加载失效
    query = query.GroupBy(x => new { x.SmthEntityId, x.AnotherEntityId})
                 .Select(g => new XXX() {
                        SmthEntityId = g.Key.SmthEntityId,
                        AnotherEntityId = g.Key.AnotherEntityId,
                        Count = g.Count()
                    });

    // Include不能放在Select之后，否则会抛出InvalidOperationException
    // 此处无法添加Include

    if (filter.SortDesc)
        query = query.OrderByDescending(x => EF.Property<object>(x, filter.SortColumn)).ThenByDescending(x => x.Id);
    else
        query = query.OrderBy(x => EF.Property<object>(x, filter.SortColumn)).ThenBy(x => x.Id);

    filter.Total = query.Count();

    query = query.Skip(filter.Skip).Take(filter.PageSize);

    return query.Select(x => DtoMapperHelper.GetXXXGrid(x));
}

DTO映射辅助类 #

public static XXXDto GetXXXGrid(XXX entity)
{
    if (entity is null)
        return null;

    return new XXXDto
    {
        SmthEntity = GetSmthEntity(entity.SmthEntity), // 映射为SmthEntity的DTO
        AnotherEntity = GetAnotherEntity(entity.AnotherEntity), // 映射为AnotherEntity的DTO
        Count = entity.Count
    };
}

方案1：分组后直接在Select中关联导航属性 #

EF Core支持在分组后的Select语句中直接关联导航实体，无需依赖Include。修改分组逻辑，通过主键直接查询对应导航实体，EF会自动生成Join语句完成单次数据拉取：

query = query.GroupBy(x => new { x.SmthEntityId, x.AnotherEntityId})
             .Select(g => new XXX() {
                    SmthEntityId = g.Key.SmthEntityId,
                    AnotherEntityId = g.Key.AnotherEntityId,
                    Count = g.Count(),
                    // 直接关联导航实体，EF自动生成Join
                    SmthEntity = _context.SmthEntity.FirstOrDefault(s => s.Id == g.Key.SmthEntityId),
                    AnotherEntity = _context.AnotherEntity.FirstOrDefault(a => a.Id == g.Key.AnotherEntityId)
                });

优势 #

• 单次数据库查询完成所有数据拉取，避免N+1问题
• 逻辑直接，无需额外操作

注意事项 #

• 确保导航实体的主键为Id（若不是，替换为对应主键字段名）
• 分组后的主键唯一，FirstOrDefault可确保每个分组只获取对应导航实体

方案2：预查询导航属性再关联 #

先筛选出当前条件下涉及的所有导航实体ID，批量拉取导航实体后，在内存中与分组结果关联，适合导航实体需要额外筛选的场景：

// 先获取当前筛选条件下涉及的所有ID
var filteredIds = _context.XXX
    .Where(x => 
        (!filter.SmthEntityId.HasValue || x.SmthEntityId == filter.SmthEntityId) &&
        (!filter.AnotherEntityId.HasValue || x.AnotherEntityId == filter.AnotherEntityId))
    .Select(x => new { x.SmthEntityId, x.AnotherEntityId })
    .Distinct()
    .ToList();

// 批量预加载导航实体并转为字典
var smthEntities = _context.SmthEntity
    .Where(s => filteredIds.Select(f => f.SmthEntityId).Contains(s.Id))
    .ToDictionary(s => s.Id);

var anotherEntities = _context.AnotherEntity
    .Where(a => filteredIds.Select(f => f.AnotherEntityId).Contains(a.Id))
    .ToDictionary(a => a.Id);

// 执行分组查询并在内存中关联导航实体
var query = _context.XXX
    .Where(x => 
        (!filter.SmthEntityId.HasValue || x.SmthEntityId == filter.SmthEntityId) &&
        (!filter.AnotherEntityId.HasValue || x.AnotherEntityId == filter.AnotherEntityId))
    .GroupBy(x => new { x.SmthEntityId, x.AnotherEntityId})
    .Select(g => new {
        SmthEntityId = g.Key.SmthEntityId,
        AnotherEntityId = g.Key.AnotherEntityId,
        Count = g.Count()
    })
    .AsEnumerable()
    .Select(g => new XXX {
        SmthEntityId = g.SmthEntityId,
        AnotherEntityId = g.AnotherEntityId,
        Count = g.Count,
        SmthEntity = smthEntities.TryGetValue(g.SmthEntityId, out var s) ? s : null,
        AnotherEntity = anotherEntities.TryGetValue(g.AnotherEntityId, out var a) ? a : null
    });

优势 #

• 导航实体的查询逻辑可独立扩展（如添加额外筛选、排序）
• 均为批量操作，性能可控

缺点 #

• 需要两次数据库查询（分组+导航实体）
• 适合数据量较小的场景，避免内存处理过多数据

方案3：直接投影到DTO（最优推荐） #

既然最终要映射到DTO，可跳过实体类中间环节，直接在查询中投影到目标DTO，既简洁高效，又避免实体类NotMapped属性的限制：

public IEnumerable<XXXDto> Filter(XXXFilter filter)
{
    var query = _context.XXX
        .Where(x => 
            (!filter.SmthEntityId.HasValue || x.SmthEntityId == filter.SmthEntityId) &&
            (!filter.AnotherEntityId.HasValue || x.AnotherEntityId == filter.AnotherEntityId));

    // 分组并直接投影到DTO，同时关联导航属性字段
    var groupedQuery = query.GroupBy(x => new { x.SmthEntityId, x.AnotherEntityId})
                 .Select(g => new XXXDto {
                        Count = g.Count(),
                        // 直接投影导航实体的DTO字段
                        SmthEntity = new SmthEntityDto {
                            Id = g.Key.SmthEntityId,
                            Name = g.First().SmthEntity.Name, // 假设SmthEntity有Name字段
                            // 其他需要的字段
                        },
                        AnotherEntity = new AnotherEntityDto {
                            Id = g.Key.AnotherEntityId,
                            Code = g.First().AnotherEntity.Code, // 假设AnotherEntity有Code字段
                            // 其他需要的字段
                        }
                    });

    // 排序逻辑
    if (filter.SortDesc)
        groupedQuery = groupedQuery.OrderByDescending(x => EF.Property<object>(x, filter.SortColumn));
    else
        groupedQuery = groupedQuery.OrderBy(x => EF.Property<object>(x, filter.SortColumn));

    // 分页处理（Count需在分页前查询）
    filter.Total = groupedQuery.Count();
    var pagedQuery = groupedQuery.Skip(filter.Skip).Take(filter.PageSize);

    return pagedQuery.ToList();
}

优势 #

• 完全摆脱实体类限制，无需构造XXX实体
• EF生成最优SQL，仅拉取DTO所需字段，减少数据传输
• 无需额外映射方法，逻辑更清晰

注意事项 #

• 若排序涉及导航属性字段，需调整EF.Property参数，或直接指定排序字段（如x => x.SmthEntity.Name）
• 导航实体字段较多时，可将投影逻辑封装为单独方法，保持代码整洁

内容的提问来源于stack exchange，提问作者1nst4nce