The LINQ expression 'First()' could not be translated and will be evaluated locally. when using groupby

Question

After upgrading to ef core 3.0. i am having issues with grouping my results. If i remove the grouping section it works fine. But of course i need to get a sum of the amount so need it.

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

Answer 1

Reason for this is EFCore unable to translate your linq query into sql query. Therefore it is taking data into memory and apply your linq after that. It is very memory consuming thing.

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

Mitigations

If a query can't be fully translated, then either rewrite the query in a form that can be translated, or use AsEnumerable(), ToList(), or similar to explicitly bring data back to the client where it can then be further processed using LINQ-to-Objects.

As alternative way, I prefer to use Max(), when taking values other than group key.

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

Answer 2

I think the actual sql query you eventually want is something like:

SELECT
,   s.Title
,   s.Id
,   Sum(o.Amount) AmountOnExchange
FROM [Offers] o
JOIN [Schack] s ON o.schack_id = s.id
WHERE o.Amount > 0
GROUP BY s.Id, s.Title

In SQL you have to group by every field you want to reference in the select. The query optimizer is smart enough to just group by the ID if it is included.

Translating this back into Linq you'd get something like:

var models = _context.Offers
    .Where(o => o.Amount > 0)
    .GroupBy(o => new {o.Shack.Id, o.Shack.Title})
    .Select(group => new ShackGapiModel
        {
             Id = = group.Key.Id,
             Title = group.Key.Title,
             AmountOnExchange = group.Sum(o => o.Amount)                   
        });

or

var models = 
    from o in _context.Offers
    where o.Amount > 0
    group o by new {o.Shack.Id, o.Shack.Title} into g
    select new ShackGapiModel
        {
             Id = = g.Key.Id,
             Title = g.Key.Title,
             AmountOnExchange = g.Sum(o => o.Amount)                   
        };