Entity Framework v6 GroupBy失去原始订购

问题描述

我具有查询的以下部分(这是一个较大查询的结尾- 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对象的对象.大约有1万2千辆汽车，分为40个射程(RangeId-索引整数指示该射程).

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.

推荐答案

在LINQ to Entities查询中，GroupBy之前的任何顺序都将被忽略.您甚至在执行的SQL中都看不到它.这是因为Entity Framework将分组表达式按排序依据(在您的情况下为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失去原始订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！