0

I'm using Entity framework core 3.1 to connect to the database, what I'm trying is to get the Highest and lowest product details which grouped by its category id. based on answers shown on this question and this question I tried to write the following code:

using Linq :

//NOTE: 'Products' is an IQueryable for all the products in the database.
   var highPrice = from a in Products
                          group a by a.CategoryId
                               into prods
                          select prods.OrderByDescending(a => a.Price).First();

using Lambda:

 //NOTE: 'Products' is an IQueryable for all the products in the database.
     var highPrice = Products.GroupBy(a => a.CategoryId, (key, a) => a.OrderByDescending(a => a.Price).First()).ToList();

Both works fine only if I converted the IQueryble of Products to IEnumerable using for example .ToList() , but when running without converting it pops the following exception:

System.InvalidOperationException
HResult=0x80131509
Message=The LINQ expression '(GroupByShaperExpression:
KeySelector: (a.CategoryId), 
ElementSelector:(EntityShaperExpression: 
EntityType: Product
ValueBufferExpression: 
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False))
.OrderByDescending(a => a.Price)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

Is there any way to get the first/Last records of IQueryable collection without converting to IEnumerable ??

Ahmed Abd Elmoniem
  • 157
  • 1
  • 4
  • 12
  • You are trying to find the Highest in each group. So should not it return IEnumerable? – Nafis Islam Nov 14 '20 at 13:07
  • Can you explain why you're trying to keep the lazy IQueryable in an unevaluated state? – John Nov 14 '20 at 13:10
  • @NafisIslam the final return should be IEnumerable, what I'm trying is the filtration be done on the IQueryable to be handled on the database side not in the memory side like what IEnumerable does, for performance only. – Ahmed Abd Elmoniem Nov 14 '20 at 13:13
  • @AhmedAbdElmoniem, I hope you will understand why this query is invalid https://stackoverflow.com/questions/64377955/linq-to-database-how-to-group-entities-properly-and-groupby-limitations – Svyatoslav Danyliv Nov 14 '20 at 13:58
  • @SvyatoslavDanyliv "*When you need effective queries, do not forget about SQL and learn base principles and it will save your time a lot."* The whole idea of LINQ with ORM is that one does not need to know/learn SQL and its principles, which btw are specific for relational databases. It's ORM LINQ query translator (EF Core in this case) fault that it doesn't translate it appropriately, or does not provided the necessary abstract LINQ extension for that, like your library does. Your library extensions are fine, but they should really be part of EF Core queryable extensions. – Ivan Stoev Nov 14 '20 at 14:31
  • 1
    Or it should be able to translate the above I would say "standard" LINQ pattern for TOP N items per group problem, similar to how they do with "left outer join" pattern, or correlated collection shape in result (something which doesn't exist in SQL). By not willing to do that, they are just killing the whole idea of LINQ and all their efforts to support it. So rather than seeking for workarounds and 3rd party solutions, people should go and vote on their GitHub to get that implemented. – Ivan Stoev Nov 14 '20 at 14:36
  • @IvanStoev, we think by different categories. There is no ideal LINQ way and probably will never appear. People need solution right now but not after several years. For me LINQ is a tool for generating SQL. – Svyatoslav Danyliv Nov 14 '20 at 15:03
  • @SvyatoslavDanyliv All I'm saying that the OP query is not wrong. Wrong is that the "modern" ORM dos not translate it in one or another way (for instance, it's "non-modern" predecessor EF6 does that). People should put pressure on them to do that by voting here [Support ability to select top N of each group #13805](https://github.com/dotnet/efcore/issues/13805). Otherwise the team excuses are that there is no enough interest, thus value. And this is quite common question/pattern which is asked for almost every day here. – Ivan Stoev Nov 14 '20 at 18:09
  • Even twice - https://stackoverflow.com/questions/64836474/ef6-query-to-latest-ef-core-could-not-be-translated – Ivan Stoev Nov 14 '20 at 18:09
  • @IvanStoev, two years for the issues... And I can tell you that there is no simple way to solve this problem. It is not easy to detect what developer needs in particular LINQ query and create effective SQL. EF6 I think just load whole table and groups on the client side - thanks for such solution. – Svyatoslav Danyliv Nov 14 '20 at 18:17

1 Answers1

4

Not all Linq methods can be translated to a SQL query (that's why your expression doesn't compile).

So, you can obtain the same result using the Take() method combined with order by clause. Following your example:

var mostExpensiveProductByCategory = dbContext.Products
                .GroupBy(x => x.CategoryId).Select(x => new
                {
                    CategoryId = x.Key,
                    Product = x.OrderByDescending(p => p.Price).Take(1)
                })
                .ToList();

var cheapestProductByCategory = dbContext.Products
                .GroupBy(x => x.CategoryId).Select(x => new
                {
                    CategoryId = x.Key,
                    Product = x.OrderBy(p => p.Price).Take(1)
                })
                .ToList();

UPDATE:

To achieve your requirement and avoid in-memory grouping, I would suggest you to work with navigation properties, see below:

var mostExpensiveProductByCategory = dbContext.Categories.Select(x => new 
{ 
    x.CategoryId, 
    Products = x.Products.OrderByDescending(p => p.Price).Take(1) 
}).ToList();

This will produce the following query output:

SELECT [c].[CategoryId], [t0].[ProductId], [t0].[CategoryId], [t0].[Price] 
FROM [Categories] AS [c] 
LEFT JOIN ( SELECT [t].[ProductId], [t].[CategoryId], [t].[Price] 
FROM ( SELECT [p].[ProductId], [p].[CategoryId], [p].[Price], ROW_NUMBER() OVER(PARTITION BY [p].[CategoryId] ORDER BY [p].[Price] DESC) AS [row] 
FROM [Products] AS [p] ) AS [t] WHERE [t].[row] <= 1 ) AS [t0] ON [c].[CategoryId] = [t0].[CategoryId] 
ORDER BY [c].[CategoryId], [t0].[CategoryId], [t0].[Price] DESC, [t0].[ProductId]
Simone Pozzobon
  • 81
  • 4
  • Hi Simone, Thank you for your answer, Anyhow by using SQL Server Profiler, it actually generates the same SQL query using `Products.ToList` on my code ... the main idea I thought it could be translated to sql query with `group by` and `having` statment or other SQL server functions. – Ahmed Abd Elmoniem Nov 14 '20 at 21:59
  • 1
    Mmm in that case I would suggest you to work with navigation properties, see below: var mostExpensiveProductByCategory = dbContext.Categories .Select(x => new { x.CategoryId, Products = x.Products.OrderByDescending(p => p.Price).Take(1) }).ToList(); – Simone Pozzobon Nov 15 '20 at 16:11
  • 1
    This will produce the following output in sql: SELECT [c].[CategoryId], [t0].[ProductId], [t0].[CategoryId], [t0].[Price] FROM [Categories] AS [c] LEFT JOIN ( SELECT [t].[ProductId], [t].[CategoryId], [t].[Price] FROM ( SELECT [p].[ProductId], [p].[CategoryId], [p].[Price], ROW_NUMBER() OVER(PARTITION BY [p].[CategoryId] ORDER BY [p].[Price] DESC) AS [row] FROM [Products] AS [p] ) AS [t] WHERE [t].[row] <= 1 ) AS [t0] ON [c].[CategoryId] = [t0].[CategoryId] ORDER BY [c].[CategoryId], [t0].[CategoryId], [t0].[Price] DESC, [t0].[ProductId] – Simone Pozzobon Nov 15 '20 at 16:11
  • YES IT WORKED !!! Please add this to a solution or edit your solution to accept it as an Answer ... – Ahmed Abd Elmoniem Nov 19 '20 at 16:58
  • Done! :) Thank you for your feedback! – Simone Pozzobon Nov 20 '20 at 18:48