How to use Linq or Lambda with IQueryable to GroupBy and get the first/last record on the collection in C#?

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 ??

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]