Using navigation properties inside a grouped select statement-CodePudding

I'm trying to use a column from a navigation property to do a calculation inside a grouped select statment like so:

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();

However, it can't translate this saying

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.

I've also tried using:

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)))});

This seems like it should be a simple query. Is there a way around this?

CodePudding user response：

Put navigation property into projection:

var query = 
    from items in ItemDatabase
    where items.status == "INV"                                           
    group new { items, items.ItemInfo } by items.origin into grp                 
    select new
    {
        origin = grp.Key,
        total = grp.Sum(e => e.items.count * (e.ItemInfo.price   e.ItemInfo.upcharge)),
    };