Entity Framework中实现PARTITION BY查询取关联表最新记录

你当前用不了EF Core 5.0才支持的Filtered Include（即Include内加Where过滤的特性），最稳妥、性能最好的实现方式是拆分查询+手动绑定导航属性，兼容EF6、EF Core 3.1及更早版本，完全规避多Include产生的笛卡尔积冗余问题。

实现步骤 #

核心思路是放弃用EF自动加载两个历史全表，分三次独立查询，再把查询到的最新记录手动绑定到对应实体的导航属性上，EF的上下文跟踪会自动处理关联关系。

1. 查询主表基础数据 #

先加载Sub主表、SubsHistories关联、App基础信息，不加载两个历史表的全量数据：

// 加载主数据
var subs = await _context.Subs
    .Include(s => s.SubsHistories)
    .Include(s => s.App)
    .ToListAsync();

// 提取所有涉及的AppId，缩小后续查询范围，避免全表扫描
var appIds = subs
    .Where(s => s.App != null)
    .Select(s => s.App.Id)
    .Distinct()
    .ToList();

2. 批量查询所有App的最新历史记录 #

分别查询OwnerHistories、SubmitterHistories中每个App对应的最新单条记录，两种写法可选：

写法1：用LINQ分组查询（EF可自动翻译为SQL）

低版本EF可正常翻译分组取第一条的逻辑，生成的SQL和你手写的ROW_NUMBER分区查询逻辑等价：

// 查询最新Owner历史
var latestOwnerHistories = await _context.OwnerHistories
    .Where(oh => appIds.Contains(oh.AppId))
    .GroupBy(oh => oh.AppId)
    .Select(g => g.OrderByDescending(oh => oh.ODate).FirstOrDefault())
    .ToListAsync();
var ownerMap = latestOwnerHistories.ToDictionary(oh => oh.AppId, oh => oh);

// 查询最新Submitter历史
var latestSubmitterHistories = await _context.SubmitterHistories
    .Where(sh => appIds.Contains(sh.AppId))
    .GroupBy(sh => sh.AppId)
    .Select(g => g.OrderByDescending(sh => sh.SDate).FirstOrDefault())
    .ToListAsync();
var submitterMap = latestSubmitterHistories.ToDictionary(sh => sh.AppId, sh => sh);

写法2：直接执行手写原生SQL（兼容性最高）

如果你的EF版本LINQ翻译有问题，直接用你写好的原生SQL查询，完全可控：

// 查询最新Owner历史
var latestOwnerHistories = await _context.OwnerHistories.FromSqlInterpolated($@"
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY AppId ORDER BY ODate DESC) rn
        FROM OwnerHistories
        WHERE AppId IN ({appIds})
    ) t WHERE rn = 1
").ToListAsync();
var ownerMap = latestOwnerHistories.ToDictionary(oh => oh.AppId, oh => oh);

// 查询最新Submitter历史
var latestSubmitterHistories = await _context.SubmitterHistories.FromSqlInterpolated($@"
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY AppId ORDER BY SDate DESC) rn
        FROM SubmitterHistories
        WHERE AppId IN ({appIds})
    ) t WHERE rn = 1
").ToListAsync();
var submitterMap = latestSubmitterHistories.ToDictionary(sh => sh.AppId, sh => sh);

3. 手动绑定导航属性 #

把查询到的最新记录绑定到对应App的导航属性上，替代原来Include加载的全量集合：

foreach (var sub in subs)
{
    if (sub.App == null) continue;

    // 绑定最新Owner记录
    sub.App.OwnerHistories = ownerMap.TryGetValue(sub.App.Id, out var owner)
        ? new List<OwnerHistory> { owner }
        : new List<OwnerHistory>();

    // 绑定最新Submitter记录
    sub.App.SubmitterHistories = submitterMap.TryGetValue(sub.App.Id, out var submitter)
        ? new List<SubmitterHistory> { submitter }
        : new List<SubmitterHistory>();
}

方案优势 #

• 兼容性极强：支持所有EF版本，不需要依赖高版本特性
• 性能远高于多Include写法：总共只执行3次查询，完全避免多一对多关联JOIN产生的笛卡尔积冗余，返回数据量和你手写的原生SQL完全一致
• 逻辑可控：不会出现EF自动生成SQL不符合预期的问题，历史表数据量越大，性能提升越明显

如果最终返回给业务层不需要直接返回EF实体，也可以在查询完所有数据后直接映射为DTO，省去手动给导航属性赋值的步骤，性能还能再提升。

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