EF分组Select语句中使用导航属性遇翻译错误的解决办法

尝试在EF Core分组查询中使用导航属性的字段进行计算，两种写法均报错无法翻译表达式：

写法一： #

var result = await (
    from items in Items.Include(e=>e.ItemInfo)
    where items.status == "INV"                                           
    group rolls by items.origin into grp                 
    select new
    {
        origin = grp.Key.origin,
        total = (grp.Sum(e => e.count * (e.ItemInfo.price + e.ItemInfo.upcharge))),
    }).ToListAsync();

写法二： #

var result= Item.Include(e=>e.ItemInfo)
    .Where(e => e.status == "INV")
    .GroupBy(e => e.origin)
    .Select(g => new {origin = g.Key.origin,
                      total = (g.Sum(e=> e.count * (e.ItemInfo.price+e.ItemInfo.upcharge)))});

错误信息： #

The LINQ expression '(EntityShaperExpression:
EntityType: itemTable
ValueBufferExpression:
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
).count * (EntityShaperExpression:
EntityType: itemTable
ValueBufferExpression:
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
).ItemInfo.price' could not be translated.

可行解决方案 #

方案1：提前投影所需字段（推荐） #

先通过Select将需要的字段（包括导航属性中的字段）提取到匿名类型中，再执行分组求和操作，让EF Core能正确解析表达式：

var result = await Items
    .Where(e => e.status == "INV")
    .Select(e => new 
    {
        e.origin,
        e.count,
        Price = e.ItemInfo.price,
        Upcharge = e.ItemInfo.upcharge
    })
    .GroupBy(e => e.origin)
    .Select(g => new 
    {
        origin = g.Key.origin,
        total = g.Sum(e => e.count * (e.Price + e.Upcharge))
    })
    .ToListAsync();

说明：Include在分组查询中无意义，因为分组操作不会保留完整实体，提前投影字段能避免EF Core无法解析导航属性的问题。

方案2：显式使用Join关联表 #

如果EF Core版本较低，显式关联两张表并投影字段后再分组：

var result = await Items
    .Join(ItemInfos, 
          item => item.ItemInfoId, // 替换为实际外键字段
          info => info.Id, // 替换为ItemInfo的主键字段
          (item, info) => new 
          {
              item.origin,
              item.count,
              info.price,
              info.upcharge,
              item.status
          })
    .Where(e => e.status == "INV")
    .GroupBy(e => e.origin)
    .Select(g => new 
    {
        origin = g.Key.origin,
        total = g.Sum(e => e.count * (e.price + e.upcharge))
    })
    .ToListAsync();

方案3：检查导航属性配置 #

确认Item与ItemInfo的关系在DbContext中配置正确，比如一对一/多对一关系是否用HasOne/WithMany等方法正确声明，错误的关系配置可能导致EF无法识别导航属性。

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