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

entity-framework-core

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)                   
                });
1
2
10/20/2019 8:21:47 PM

Popular Answer

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://docs.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)                   
                });
1
10/21/2019 9:33:30 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow