EF Core 3.1多表关联GroupBy取最新数据超时与报错如何解决？

问题根因 #

• 第一种、第三种写法超时：全量关联三表数据后加载到内存做分组排序，数据量大时数据库查询、网络传输、内存计算的开销都会触发超时
• 第二种写法报错：EF Core 3.1 对GroupBy后直接访问导航属性的SQL翻译支持有限，无法生成合法SQL导致运行异常

最优实现方案（EF Core 3.1 兼容，全库端执行） #

使用窗口函数分区排序逻辑，所有计算在数据库侧完成，仅返回需要的目标数据，性能最高：

using (var dbContext = _dbContextProvider.DbContext)
{
    // 先构造带排名的关联查询
    var rankQuery = from t1 in dbContext.PriceSite
                    where t1.ValidFromGmtDtm > shellSiteNotification.ValidFromDtm // 可根据业务调整时间筛选条件
                    join t2 in dbContext.PriceSiteProduct on t1.PriceSiteId equals t2.PriceSiteId
                    join t3 in dbContext.Product on t2.ProductId equals t3.ProductId
                    select new
                    {
                        PriceSiteId = t1.PriceSiteId,
                        ValidFrom = t1.ValidFromGmtDtm,
                        ProductId = t3.ProductId,
                        ProductNm = t3.ProductNm,
                        ProductValues = t3.PumpPriceProduct,
                        // 按产品名称分区，按生效时间倒序排名
                        Rank = EF.Functions.RowNumber(
                            partitionBy: t3 => t3.ProductNm,
                            orderings: x => x.OrderByDescending(t1 => t1.ValidFromGmtDtm))
                    };

    // 取每个分区排名第一的记录即为每个产品最新的关联记录
    var result = await rankQuery.Where(x => x.Rank == 1).ToListAsync();
}

备选兼容方案（不依赖窗口函数） #

如果数据库不支持窗口函数，可采用先分组取最大时间，再关联回原表的方案，同样可以全库端执行：

using (var dbContext = _dbContextProvider.DbContext)
{
    // 第一步：按产品名称分组，取每个分组的最大生效时间
    var maxTimeQuery = from t1 in dbContext.PriceSite
                       where t1.ValidFromGmtDtm > shellSiteNotification.ValidFromDtm
                       join t2 in dbContext.PriceSiteProduct on t1.PriceSiteId equals t2.PriceSiteId
                       join t3 in dbContext.Product on t2.ProductId equals t3.ProductId
                       group t1.ValidFromGmtDtm by t3.ProductNm into g
                       select new 
                       {
                           ProductNm = g.Key,
                           MaxValidFrom = g.Max()
                       };

    // 第二步：关联原表取对应完整记录
    var resultQuery = from max in maxTimeQuery
                      join t3 in dbContext.Product on max.ProductNm equals t3.ProductNm
                      join t2 in dbContext.PriceSiteProduct on t3.ProductId equals t2.ProductId
                      join t1 in dbContext.PriceSite on 
                          new { t2.PriceSiteId, max.MaxValidFrom } equals 
                          new { t1.PriceSiteId, MaxValidFrom = t1.ValidFromGmtDtm }
                      select new
                      {
                          PriceSiteId = t1.PriceSiteId,
                          ValidFrom = t1.ValidFromGmtDtm,
                          ProductId = t3.ProductId,
                          ProductNm = t3.ProductNm,
                          ProductValues = t3.PumpPriceProduct
                      };

    var result = await resultQuery.ToListAsync();
}

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