如何在LINQ + EF Core 6中GroupBy后过滤记录？

给定如下Contract实体，一个客户可拥有多个不同起始日期和状态的合同：

public class Contract {
 [Key]
 int ContractId { get; set; }
 int CustomerId { get; set; } 
 string Status { get; set; }
 DateTime ContractStartDate { get; set; }
}

现有数据集如下：

需求是：先找到每个客户的最新合同，再过滤掉特定状态的记录（不能先过滤状态再分组，否则最新的非活跃记录会被提前剔除）。

尝试了以下代码：

var latestContracts = Query<Contract>()
    .GroupBy(grp => grp.CustomerId)
    .Select(s => s.OrderByDescending(s => s.ContractStartDate).First())
    .Where(w => w.Status == "Active");

调用ToListAsync()时出现错误：

'The LINQ expression 'DbSet<Customer>()
    .GroupBy(c => c.CustomerId)
    .Select(g => g
        .AsQueryable()
        .OrderByDescending(e => e.ContractStartDate)
        .First())
    .Where(e0 => e0.AccountContractTermStatus == "Active")' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

移除Where调用后查询可正常获取最新结果，询问是否有简便解决办法。

EF Core 6对GroupBy后直接Select分组内第一条数据的LINQ语法翻译支持有限，推荐使用**窗口函数（RowNumber）**的方式实现，这种写法能被EF Core正确转换为SQL，且在数据库端完成计算，效率更高：

var latestContracts = Query<Contract>()
    .Select(c => new 
    {
        Contract = c,
        RowNum = EF.Functions.RowNumber()
            .Over(partitionBy: c.CustomerId, orderBy: c.ContractStartDate descending)
    })
    .Where(x => x.RowNum == 1)
    .Select(x => x.Contract)
    .Where(c => c.Status == "Active"); // 需根据需求替换状态，示例中若要保留Inactive则改为"Inactive"

说明： #

• 使用EF.Functions.RowNumber()生成每个客户分组内的行号，按ContractStartDate倒序排列，最新的合同行号为1；
• 先筛选出行号为1的记录（即每个客户的最新合同），再根据状态过滤；
• 这种写法完全在数据库端执行，不会出现客户端评估的性能问题。

如果一定要保留GroupBy的思路，也可以先将分组后的结果转为客户端集合再过滤，但这种方式会将所有客户的最新合同拉取到内存中处理，数据量较大时不推荐：

var latestContracts = Query<Contract>()
    .GroupBy(grp => grp.CustomerId)
    .Select(s => s.OrderByDescending(s => s.ContractStartDate).First())
    .AsEnumerable() // 切换到客户端评估
    .Where(w => w.Status == "Active");

验证结果 #

对于给定的数据集，若将状态过滤条件改为w.Status == "Inactive"，上述两种方法都能正确返回ContractID为2和4的记录。

内容的提问来源于stack exchange，提问作者Brian M.