LINQ to SQL 在使用 DISTINCT 时不生成 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]

推荐答案

这是 ORDER BY 与 DISTINCT 相关的SQL 和关系代数的限制.

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

ORDER BY 必须在 SQL 中更远"(在顶级")，因为它是一个视图操作.虽然可以编写具有 ORDER BY 进一步输入"的 SQL，但与 RA 操作相关，它通常会导致未定义行为(有时会起作用).从这个角度来看，Linq2Sql 可以随意忽略 ORDER BY 是有道理的，尽管例外可能会更好......无论如何它会不那么微妙;-)(实际上，同样的问题存在于任何不提供更严格"的 Distinct 定义的 Linq 提供程序.)

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

快乐编码.

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