Entity Framework v6 GroupBy 丢失原始排序 [英] Entity Framework v6 GroupBy Losing Original Ordering

问题描述

我有以下查询部分(这是更大查询的结尾 - queryBuilder 是一个 IQueryable)

I have the following part of a query (It's the end of a larger query - queryBuilder is an IQueryable)

            var results = queryBuilder
                          .OrderBy(x => x.Vehicle.RangeId)
                          .ThenBy(x => x.Rate.Rental)
                          .GroupBy(x => x.Vehicle.RangeId)
                          .Select(x => x.FirstOrDefault())
                          .OrderBy(x => x.Rate.Rental);

它使用具有 Vehicle 对象和 Rental 对象的对象.大约有 12,000 辆汽车，分为大约 40 个范围(RangeId - Indexed int 规定了这一点).

It's working with an object with a Vehicle object and Rental object. There are around 12K vehicles and split into about 40 ranges (RangeId - Indexed int dictates that).

上面的查询工作正常，但没有达到我的预期.我希望它先按 RangeId 订购车辆，然后按租金(最低租金优先).

The query above works fine however is does not do what I expect. I expect for it to order the vehicles by RangeId and then by rental (Lowest rental first).

然后按 RangeId 对它们进行分组，并从组中选择第一个，这应该"是最便宜的租金，因为它在调用 groupby 之前被订购.

Then group them by the RangeId and select the first one from the group, which 'should' be the cheapest rental as it's ordered to be like before the groupby is called.

但是.不是.它只是随机得到一个，没有顺序.有时它是第二便宜的.有时第 5 次等等.出于某种原因，GroupBy 不遵守原始订单！

However. It's not. It just randomly gets one in no order. Sometimes its the second cheapest. Sometimes 5th and so on. For some reason the GroupBy is not honouring the original order!

我可以通过执行以下操作来完成这项工作.但是表现绝对糟糕，需要很长时间才能完成.

I can get this working, by doing the following. However the performance is absolutely dire and takes ages to complete.

            var results = queryBuilder
                          .OrderBy(x => x.Vehicle.RangeId)
                          .ThenBy(x => x.Rate.Rental)
                          .GroupBy(x => x.Vehicle.RangeId)
                          .Select(x => x.OrderBy(o => o.Rate.Rental).FirstOrDefault())
                          .OrderBy(x => x.Rate.Rental);

任何帮助或指针将不胜感激.我无法弄清楚为什么原始查询没有保持一切井井有条.

Any help or pointers would be appreciated. I can't firgure out why the original query is not keeping everything in order.

推荐答案

我希望它先按 RangeId 订购车辆，然后再按租赁方式订购车辆

I expect for it to order the vehicles by RangeId and then by rental

在 LINQ to Entities 查询中，GroupBy 之前的任何排序都会被忽略.您甚至不会在执行的 SQL 中看到它.这是因为实体框架采用分组表达式排序(在您的情况下为 x => x.Vehicle.RangeId).这是为什么呢?

In an LINQ to Entities query, any ordering before a GroupBy is simply ignored. You won't even see it in the executed SQL. That is because Entity Framework takes the grouping expression to order by (in your case x => x.Vehicle.RangeId). Why is that?

LINQ 的 GroupBy 看似与 SQL 的 GROUP BY 相似，但实际上却大不相同.

LINQ's GroupBy is seemingly similar to SQL's GROUP BY, but actually it's quite different.

GROUP BY 是破坏性的"，我的意思是除了 GROUP BY 中的列之外的任何信息都会丢失(除了聚合表达式).如果你这样做......

GROUP BY in SQL is "destructive", by which I mean that any information other than the columns in the GROUP BY is lost (apart from aggregate expressions). If you do ...

SELECT Brand, COUNT(*) 
FROM Cars
GROUP BY Brand

...您只能看到 Brand 及其数量.您看不到组中的汽车.

... you only see Brand and their counts. You don't see the cars in the groups.

这正是 LINQ 的 GroupBy 所做的:它生成 complete 对象组.原始数据中的所有信息仍然存在.您会看到按品牌分组的汽车.

That's exactly what LINQ's GroupBy does: it produces groups of complete objects. All information in the original data is still there. You'll see cars grouped by their brands.

这意味着将 GroupBy 转换为 GROUP BY 的 ORM 会给自己构建结果集带来困难.LINQ to SQL 就是这样做的.它首先执行 GROUP BY 查询，然后需要单独的查询(实际上是每组一个)来弥补丢失"的数据.

That means that ORMs that translate GroupBy as GROUP BY give themselves a hard time building the result set. LINQ to SQL does that. It executes a GROUP BY query first and then it needs separate queries (one per group actually) to make up for the "lost" data.

EF 以不同的方式实现 GroupBy.它在一次查询中获取所有数据，然后在内存中构建组.您不会在生成的 SQL 中看到 GROUP BY.您会看到一个 ORDER BY .我认为 EF 更喜欢排序的 SQL 查询结果，以便在内存中进行更有效的处理.(我可以想象与管道中的其他 LINQ 语句更好地结合).

EF implements GroupBy differently. It gets all data in one query and then it builds the groups in memory. You won't see GROUP BY in the generated SQL. You see an ORDER BY instead. I think EF prefers a sorted SQL query result for more efficient processing in memory. (And I can imagine combines better with other LINQ statements in the pipeline).

这就是为什么 GroupBy 之前的任何排序都被忽略的原因.以及为什么只能在分组后应用排序.

So that's why any ordering before GroupBy is ignored. And why you can only apply ordering after the grouping.

表现非常糟糕

从这里很难说这是为什么.也许您可以在内存中进行排序:

It's hard to tell from here why that is. Maybe you can do the ordering in memory:

var results = queryBuilder
              .GroupBy(x => x.Vehicle.RangeId)
              .Select(x => x.OrderBy(o => o.Rate.Rental).FirstOrDefault())
              .Select(o => new { o.Rate.Rental, o }
              .AsEnumerable()
              .OrderBy(x => x.Rental);

但这也可能是索引问题.如果 Rate.Rental 上没有适当的索引，则按该列排序会很昂贵.

But it may also be an indexing issue. If there's no proper index on Rate.Rental, ordering by that column is expensive.

这篇关于Entity Framework v6 GroupBy 丢失原始排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！