I try to get the summary for items in a specific place. I try to group them becasuse one item can be multiple times in the specific place and get summary for that item.It works fine with ITEMID and REMAIN but it fails when i try to access Description My command looks like this

var items1 = await _context.Set<Comitemtran>()
    .Include(c => c.Bzitem)?
    .Include(c => c.Bzchopp)?
    .Include(c => c.Bzitemlot)?
    .Include(c => c.Bzstoreplace)?
    .Where(c => c.Bzstoreplaceid == placecode).GroupBy(c => c.Bzitemid).Select(group => new ItemTransView
    {
        BZITEMID = group.Key,
        BZDESC = group.Where(c => c.Bzitemid == group.Key).FirstOrDefault().Bzitem.Bzdesc,


        BZREMAIN = group.Sum(s => s.Bzquantity)
    }).ToListAsync();

When i execute it i get this response on postman:

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression: KeySelector: c.BZITEMID, ElementSelector:EntityShaperExpression: EntityType: Comitemtran ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .Select(s => s.Bzitem.Bzdesc) .First()' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

CodePudding user response：

Add Description to grouping key. In this case query will be translated to the SQL

var items1 = await _context.Set<Comitemtran>()
    .Where(c => c.Bzstoreplaceid == placecode)
    .GroupBy(c => new { c.Bzitemid, c.Bzitem.Bzdesc })
    .Select(group => new ItemTransView
    {
        BZITEMID = group.Key.Bzitemid,
        BZDESC = group.Key.Bzdesc,
        BZREMAIN = group.Sum(s => s.Bzquantity)
    })
    .ToListAsync();

Note that Include's are ignored when followed by Select.