C# DISTINCT() 和 ORDERBY 问题

DISTINCT() and ORDERBY issue

提问by Dante

I am learning about LINQ-to-SQL and everything was going well until something strange happened:

我正在学习 LINQ-to-SQL，一切都很顺利，直到发生了一些奇怪的事情：

I tried to make an example of distinct, so, using the Northwind dabatase I wrote the following query:

我试图举一个例子distinct，因此，使用 Northwind 数据库我编写了以下查询：

var query = 
    from o in db.Orders
    orderby o.CustomerID
    select new
    {
        o.CustomerID
    };

If I print the SQL generated by LINQ-to-SQL for the query stored in queryit looks like this:

如果我打印由 LINQ-to-SQL 为存储在query其中的查询生成的 SQL，则如下所示：

SELECT [t0].[CustomerID]
FROM [dbo].[Orders] AS [t0]
ORDER BY [t0].[CustomerID]

So, as usual, the query brings all the CustomerIDfor each Orderin the Orderstable ordered alphabetically.

因此，像往常一样，查询将按字母顺序排列在表中的所有CustomerIDfor each 。OrderOrders

But! If I use the Distinct()method like this:

但！如果我使用这样的Distinct()方法：

var query = (
    from o in db.Orders
    orderby o.CustomerID
    select new
    {
        o.CustomerID
    }).Distinct();

The query brings the expected results of the Distinctclause, but the CustomerIDs are not ordered despite I wrote orderby o.CustomerID!

查询带来了Distinct子句的预期结果，但CustomerID尽管我写了s 并没有排序orderby o.CustomerID！

The SQL query for this second LINQ query is the following:

第二个 LINQ 查询的 SQL 查询如下：

SELECT DISTINCT [t0].[CustomerID]
FROM [dbo].[Orders] AS [t0]

As we can see **the ORDER BYclause is missing. Why is that?

正如我们所看到的 **ORDER BY缺少子句。这是为什么？

Why does the ORDER BYclause disappears when I use the Distinct()method?

为什么ORDER BY使用Distinct()方法时子句消失了？

采纳答案by Joachim Isaksson

From the Queryable.Distinct documentation;

来自Queryable.Distinct 文档；

The expected behavior is that it returns an unordered sequence of the unique items in source.

预期的行为是它返回源中唯一项的无序序列。

In other words, any order the existing IQueryable has is lost when you use Distinct()on it.

换句话说，现有 IQueryable 的任何顺序在您使用Distinct()它时都会丢失。

What you want is probably something more like this, an OrderBy() afterthe Distinct() is done;

你想要的可能更像这样，在 Distinct() 完成之后的OrderBy() ；

var query = (from o in db.Orders
             select new
             {
                 o.CustomerID
             }).Distinct().OrderBy(x => x.CustomerID);

回答by KeithS

Try rearranging the members to place the OrderBy after the Distinct. You'll have to revert to method chaining:

尝试重新排列成员以将 OrderBy 放在 Distinct 之后。您必须恢复到方法链接：

db.Orders.Select(o=>o.CustomerId).Distinct().OrderBy(id=>id);

This would be the more efficient way to set up the query in Enumerable Linq anyway, because the OrderBy would then operate only on the unique items and not on all of them. Also, according to MSDN, Enumerable.Distinct does not guarantee the return order of the elements anyway, so ordering before deduping is pointless.

无论如何，这将是在 Enumerable Linq 中设置查询的更有效方法，因为 OrderBy 将仅对唯一项目而不是所有项目进行操作。此外，根据MSDN， Enumerable.Distinct 无论如何都不能保证元素的返回顺序，因此在重复数据删除之前进行排序是没有意义的。

回答by Jaime Torres

Due to the use of distinct, the order of the returned list is not guaranteed. LinqToSql is smart enough to recognize this, therefor it ignores it.

由于使用了distinct，返回列表的顺序无法保证。LinqToSql 足够聪明地认识到这一点，因此它忽略了它。

If you place the order by AFTER your Distinct, everything will happen as you desire.

如果您在 Distinct 之后下订单，一切都会如您所愿。

var query = (from o in db.Orders
             select new
             {
                 o.CustomerID
             }).Distinct().OrderBy(o => o.CustomerID);

or

或者

var query = db.Orders.Select(o => o.CustomerID).Distinct().OrderBy(o => o.CustomerID);

Please see this article for clarification:

请参阅这篇文章以进行澄清：

http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

回答by Ratamahatta

You can simulate ORDERBY and DISTINCT with this counstruction:

您可以使用以下结构模拟 ORDERBY 和 DISTINCT：

var distinctItems = employees.GroupBy(x => x.EmpID).OrderBy(x => x).Select(y => y.First());