使用DISTINCT时，LINQ to SQL不会生成ORDER BY吗? [英] LINQ to SQL does not generate ORDER BY when DISTINCT is used?

问题描述

下面的基本LINQ to SQL语句不会导致orderby工作.正如您在T-SQL中看到的那样，没有orderby.你知道为什么吗?

The following basic LINQ to SQL statement does not result in the orderby working. As you can see in the T-SQL there is no orderby. Do you know why?

LINQ to SQL:

      var results = (from stats in db.t_harvest_statistics
                       orderby stats.unit_number
                       select stats.unit_number).Distinct().ToList();

在遵循TSQL的结果之上

SELECT 
[Distinct1].[unit_number] AS [unit_number]
FROM ( SELECT DISTINCT 
[Extent1].[unit_number] AS [unit_number]
FROM [dbo].[t_harvest_statistics] AS [Extent1]
     )  AS [Distinct1]

推荐答案

这是SQL和关系代数的限制，其中ORDER BY与DISTINCT有关.

That is a limitation of SQL and Relational Algebra of where the ORDER BY is in relation to the DISTINCT.

由于ORDER BY是 view操作，因此在SQL中(在顶层")必须进一步"存储.尽管一个人可以编写带有ORDER BY的SQL的SQL，但它与RA操作有关，但通常会导致未定义的行为(有时可行).因此，Linq2Sql可以自由地忽略ORDER BY，这是有道理的，尽管也许有一个例外会更好……反正它不会那么微妙;-)(实际上，对于任何 Linq提供程序，不提供Distinct的更严格"定义.)

The ORDER BY must be "further out" in the SQL (at the "top level") since it's a view operation. While one can write SQL that has the ORDER BY "further in", in relationship to a RA operation, it often results in Undefined Behavior (that sometimes works). In this light it makes sense that Linq2Sql is free to ignore the ORDER BY although, perhaps an exception would be better... it would be less subtle anyway ;-) (Actually, this same issue exists for any Linq provider that does not provide a "stricter" definition of Distinct.)

删除Distinct()，Linq2Sql应该再次按预期生成ORDER BY.解决方案只是切换操作顺序，以便ORDER BY再次处于顶层".

Remove the Distinct() and the Linq2Sql should once again generate the ORDER BY as expected. The solution is just to switch the order of operations so the ORDER BY is once again at the "top level".

这由文章在LINQ中使用Distinct和OrderBy :

此行为可能看起来很奇怪. 问题是Distinct运算符不授予它保持值的原始顺序.应用于LINQ to SQL，这意味着在查询(如queryA)的情况下可以忽略排序约束

This behavior might appear strange. The problem is that the Distinct operator does not grant that it will maintain the original order of values. Applied to LINQ to SQL, this mean that a sort constraint can be ignored in the case of a query like queryA.

解决方案非常简单:将OrderBy运算符放在Distinct运算符之后，就像下面的queryB定义一样:

The solution is pretty s[i]mple: put the OrderBy operator after the Distinct one, like in the following queryB definition:

var queryB = 
    (from o in db.Orders
     select o.Employee.LastName)
    .Distinct().OrderBy( n => n );

快乐的编码.

这篇关于使用DISTINCT时，LINQ to SQL不会生成ORDER BY吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！