LINQ查询在LinqPad正常运行但EF Core 3.1应用中报错求助

我有一段LINQ查询在LinqPad中可正常运行，代码如下：

var query =  this._dbContext.SHIPMENT_HEADER
            .Join(
                this._dbContext.SHIPMENT_DETAIL,
                h => h.INTERNAL_SHIPMENT_NUM,
                d => d.INTERNAL_SHIPMENT_NUM,
                (a, b) => new { Header = a, Detail = b })
            .Where(s => s.Header.SHIPMENT_ID == shipmentId)
            .GroupBy(g => new { g.Header.INTERNAL_SHIPMENT_NUM, g.Header.ORDER_NUM })
            .Select(x => new
            {
                InternalShipmentNumber = x.Key.INTERNAL_SHIPMENT_NUM,
                OrderNumber = x.Key.ORDER_NUM,
                OrderLines = x.Select(l => new
                {
                    InternalLineNumber = l.Detail.INTERNAL_SHIPMENT_LINE_NUM,
                    LineNumber = l.Detail.ORDER_LINE_NUM ?? 0,
                }),
            }).FirstOrDefault();

迁移到使用EF Core 3.1的.NET应用时，出现以下错误：

{"The LINQ expression '(GroupByShaperExpression: KeySelector: new { 
    INTERNAL_SHIPMENT_NUM = (s.INTERNAL_SHIPMENT_NUM), 
    ORDER_NUM = (s.ERP_ORDER)  },  ElementSelector:new { 
    Header = (EntityShaperExpression: 
        EntityType: SHIPMENT_HEADER
        ValueBufferExpression: 
            (ProjectionBindingExpression: Header)
        IsNullable: False
    ), 
    Detail = (EntityShaperExpression: 
        EntityType: SHIPMENT_DETAIL
        ValueBufferExpression: 
            (ProjectionBindingExpression: Detail)
        IsNullable: False
    )  } )
    .Select(l => new { 
        InternalLineNumber = l.Detail.INTERNAL_SHIPMENT_LINE_NUM, 
        LineNumber = l.Detail.ORDER_LINE_NUM ?? 0
     })' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()."}

原因分析 #

EF Core 3.1对GroupBy后的嵌套Select翻译支持有限，LinqPad可能使用LINQ to SQL或EF旧版本，支持此类写法，但EF Core 3.1无法将这种嵌套投影转换为SQL语句。

方法1：利用实体导航属性（推荐） #

如果SHIPMENT_HEADER和SHIPMENT_DETAIL已配置一对多导航属性（例如SHIPMENT_HEADER包含ShipmentDetails集合属性），可直接简化查询，避开手动Join和GroupBy：

var query = _dbContext.SHIPMENT_HEADER
    .Where(h => h.SHIPMENT_ID == shipmentId)
    .Select(h => new
    {
        InternalShipmentNumber = h.INTERNAL_SHIPMENT_NUM,
        OrderNumber = h.ORDER_NUM,
        OrderLines = h.ShipmentDetails.Select(d => new
        {
            InternalLineNumber = d.INTERNAL_SHIPMENT_LINE_NUM,
            LineNumber = d.ORDER_LINE_NUM ?? 0
        })
    })
    .FirstOrDefault();

该写法EF Core可直接翻译为SQL关联查询，性能更优。

方法2：显式切换到客户端评估 #

若无法使用导航属性，可在GroupBy后调用AsEnumerable()将数据拉至客户端处理。注意：此方式会先加载GroupBy后的所有数据到内存，数据量大时需谨慎使用：

var query = _dbContext.SHIPMENT_HEADER
    .Join(
        _dbContext.SHIPMENT_DETAIL,
        h => h.INTERNAL_SHIPMENT_NUM,
        d => d.INTERNAL_SHIPMENT_NUM,
        (a, b) => new { Header = a, Detail = b })
    .Where(s => s.Header.SHIPMENT_ID == shipmentId)
    .GroupBy(g => new { g.Header.INTERNAL_SHIPMENT_NUM, g.Header.ORDER_NUM })
    .AsEnumerable() // 切换到客户端评估
    .Select(x => new
    {
        InternalShipmentNumber = x.Key.INTERNAL_SHIPMENT_NUM,
        OrderNumber = x.Key.ORDER_NUM,
        OrderLines = x.Select(l => new
        {
            InternalLineNumber = l.Detail.INTERNAL_SHIPMENT_LINE_NUM,
            LineNumber = l.Detail.ORDER_LINE_NUM ?? 0,
        }),
    })
    .FirstOrDefault();

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