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

  • 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()?

    Thursday, May 20, 2021 4:32 PM

All replies

  • 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

    Friday, May 21, 2021 2:30 AM
  • 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.

    Friday, May 21, 2021 2:41 AM