如何使用 Linq 或 Lambda 与 IQueryable 到 GroupBy 并在 C# 中获取集合中的第一条/最后一条记录？

我正在使用 Entity Framework core 3.1 连接到数据库，我正在尝试获取按类别 ID 分组的最高和最低产品详细信息。基于显示的答案这个问题 https://stackoverflow.com/questions/6963707/linq-query-group-by-and-selecting-first-items/7456167?noredirect=1#comment114606791_7456167 and 这个问题 https://stackoverflow.com/questions/19012986/how-to-get-first-record-in-each-group-using-linq我尝试编写以下代码：

使用 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();

使用拉姆达：

 //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();

仅当我转换时两者都可以正常工作IQueryble of Products to IEnumerable例如使用.ToList()，但是在不转换的情况下运行时会弹出以下异常：

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().

有没有办法获取 IQueryable 集合的第一条/最后一条记录而不转换为 IEnumerable ？

并非所有 Linq 方法都可以转换为 SQL 查询（这就是您的表达式无法编译的原因）。

因此，使用 Take() 方法结合 order by 子句可以获得相同的结果。 按照你的例子：

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:

为了满足您的要求并避免内存中分组，我建议您使用导航属性，如下所示：

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

这将产生以下查询输出：

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]