如何使用带有IQueryable的Linq或Lambda到GroupBy，并在C#中获取集合中的第一条记录/最后一条记录? [英] How to use Linq or Lambda with IQueryable to GroupBy and get the first/last record on the collection in C#?

问题描述

我正在使用Entity Framework Core 3.1连接到数据库，我正在尝试获取按类别ID分组的最高和最低产品详细信息.基于此问题和这个问题编写以下代码:

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:

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

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

只有当我使用例如 .ToList()将 Products 的 IQueryble 转换为 IEnumerable 时，两者均能正常工作>，但在不进行转换的情况下运行时会弹出以下异常:

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

是否有任何方法可以获取IQueryable集合的第一条记录/最后一条记录，而无需转换为IEnumerable?

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

推荐答案

不是所有的Linq方法都可以转换为SQL查询(这就是为什么表达式无法编译的原因).

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

因此，可以将Take()方法与order by子句结合使用来获得相同的结果.按照您的示例:

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

更新:

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

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]

这篇关于如何使用带有IQueryable的Linq或Lambda到GroupBy，并在C#中获取集合中的第一条记录/最后一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！