LINQ分组查询无法被EF Core转换，寻求解决方法

先来看你提供的代码和遇到的报错：

原代码 #

using (var tigerContext = new TigerContext()) { 
    var stockRemaningList = from stocks in tigerContext.Stock 
                            join item in tigerContext.ItemsSum on stocks.LOGICALREF equals item.STOCKREF into temp 
                            from x in temp.DefaultIfEmpty() 
                            group x by new { stocks} into s 
                            select new StocksRemaning { 
                                StockCode = s.Key.stocks.CODE, 
                                StockRef = s.Key.stocks.LOGICALREF, 
                                StockName = s.Key.stocks.NAME, 
                                ActualStock = s.Sum(i=>i.ONHAND) 
                            }; 
    return stockRemaningList.ToList(); 
}

报错信息 #

LINQ表达式“GroupBy<TransparentIdentifier<Stock, ItemsSum>, <>f__AnonymousType10, ItemsSum>( source: LeftJoin<Stock, ItemsSum, int, TransparentIdentifier<Stock, ItemsSum>>( outer: DbSet, inner: DbSet, outerKeySelector: (s) => s.LOGICALREF, innerKeySelector: (ı) => ı.STOCKREF, resultSelector: (s, ı) => new TransparentIdentifier<Stock, ItemsSum>( Outer = s, Inner = ı )), keySelector: (ti) => new { stocks = ti.Outer }, elementSelector: (ti) => ti.Inner)”无法被转换。请将查询重写为可转换的形式，或通过调用AsEnumerable()、AsAsyncEnumerable()、ToList()或ToListAsync()显式切换到客户端求值。

问题根源 #

EF Core没法把「按整个Stock实体分组」的操作转换成合法的SQL——SQL的GROUP BY要求明确列出所有非聚合字段，你用new { stocks }把整个实体塞进分组键里，EF Core无法解析这种结构生成对应的SQL语句。

两种可行的修复方案 #

方案1：先聚合再连接（推荐，性能更优） #

先对ItemsSum按STOCKREF分组计算总库存，再和Stock表做左连接。这种方式让数据库先完成聚合运算，传输的数据量更小，EF Core也能轻松转换为SQL：

using (var tigerContext = new TigerContext())
{
    // 第一步：计算每个库存对应的总在手量
    var stockTotalOnHand = from item in tigerContext.ItemsSum
                           group item by item.STOCKREF into stockGroup
                           select new
                           {
                               StockRef = stockGroup.Key,
                               TotalOnHand = stockGroup.Sum(i => i.ONHAND)
                           };

    // 第二步：和Stock表左连接，组装最终结果
    var stockRemaningList = from stock in tigerContext.Stock
                            join total in stockTotalOnHand 
                                on stock.LOGICALREF equals total.StockRef into tempJoin
                            from total in tempJoin.DefaultIfEmpty()
                            select new StocksRemaning
                            {
                                StockCode = stock.CODE,
                                StockRef = stock.LOGICALREF,
                                StockName = stock.NAME,
                                // 没有对应ItemsSum记录时，默认库存为0
                                ActualStock = total?.TotalOnHand ?? 0
                            };

    return stockRemaningList.ToList();
}

方案2：调整分组键为具体属性 #

如果你想保留原有的LeftJoin+GroupBy结构，可以把分组键从整个Stock实体改成你需要的具体属性（LOGICALREF是唯一标识，加上需要展示的CODE和NAME即可），这样EF Core能生成符合SQL规范的GROUP BY语句：

using (var tigerContext = new TigerContext())
{
    var stockRemaningList = from stocks in tigerContext.Stock
                            join item in tigerContext.ItemsSum 
                                on stocks.LOGICALREF equals item.STOCKREF into temp
                            from x in temp.DefaultIfEmpty()
                            // 分组键改为具体的Stock属性，而非整个实体
                            group x by new 
                            { 
                                stocks.LOGICALREF, 
                                stocks.CODE, 
                                stocks.NAME 
                            } into s
                            select new StocksRemaning
                            {
                                StockCode = s.Key.CODE,
                                StockRef = s.Key.LOGICALREF,
                                StockName = s.Key.NAME,
                                // 处理LeftJoin带来的null值，Sum时默认取0
                                ActualStock = s.Sum(i => i?.ONHAND ?? 0)
                            };
    return stockRemaningList.ToList();
}

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