Linq GroupBy and Sum - .First() could not be translated

Question

User379720387 posted

My data is owner, location, date, charge, payment, the plan is to get a list of owners, each having a location, and the Sum OwnerTotal (charge - payamount)

OwnerId	Location	Charge	PayAmount
1	Loc 1	100	0
1	Loc 1	60	50
1	Loc 1	80	0
2	Loc 2	100	0
2	Loc 2	80	0

1	Loc 1	190
2	Loc 2	180

[Route("ByProviderBilled")]
[HttpGet]
        public async Task<ActionResult<List<TxnsByProvider>>> GetByProviderBilled(int id, bool isbilled) => await context.Transactions
            .Where(p => p.ProviderId == id && p.IsBilled == isbilled).GroupBy(x => x.OwnerBillToId)
            .Select(txn => new TxnsByProvider
            {
                ownerBillToId = (int)txn.First().OwnerBillToId,
                Location = txn.First().OwnerLocation.Location,
                OName = txn.First().OwnerBillTo.Oname,
                OwnerTotal = (decimal)txn.Sum(o => o.Charge - o.PayAmount)
            })
            .OrderByDescending(t => t.OName)
            .ToListAsync();

.Select(s => s.OwnerBillToId)
          .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

Really don't want to take Transactions into memory as is suggested, so maybe there is a different approach I can take without First()?

Answer 1

User1535942433 posted

Hi wavemaster,

Is your project type EF Core?As alternative way,you could use Max(), when taking values other than group key.

Just like this:

IQueryable<ShackGapiModel> models = _context.Offers
                .Where(i => i.Amount > 0)
                .GroupBy(i => i.Id)
                .Select(s => new ShackGapiModel
                {
                    Id = = s.Key.Value,
                    Title = s.Max(a => a.title),
                    AmountOnExchange = s.Sum(a => a.AmountOnExchange)                   
                });

More details,you could refer to below article:

https://stackoverflow.com/questions/58476969/the-linq-expression-first-could-not-be-translated-and-will-be-evaluated-loca

Best regards,

Yijing Sun

Answer 2

User379720387 posted

EF Core yes!

public async Task<ActionResult<List<TxnsByProvider>>> GetByProviderBilled(int id, bool isbilled) => await context.Transactions
            .Where(p => p.ProviderId == id && p.IsBilled == isbilled)
            .GroupBy(x => x.OwnerBillToId)
            
            .Select(txn => new TxnsByProvider
            {
                ownerBillToId = (int)txn.Key.Value,
                Location = txn.Max(a => a.OwnerLocation.Location),
                OName = txn.Max(a => a.OwnerBillTo.Oname),
                OwnerTotal = (decimal)txn.Sum(o => o.Charge - o.PayAmount)
            })
            .OrderByDescending(t => t.OName)
            .ToListAsync();

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression:
KeySelector: (Nullable<int>)t.ownerBillToId, 
ElementSelector:EntityShaperExpression: 
    EntityType: Transaction
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .Max(a => a.OwnerLocation.Location)' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.