DISTINCT() 和 ORDERBY 问题 [英] DISTINCT() and ORDERBY issue

问题描述

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

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

我试图做一个 distinct 的例子，所以，使用 Northwind dabatase 我写了以下查询:

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

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

如果我为存储在 query 中的查询打印 LINQ-to-SQL 生成的 SQL，它看起来像这样:

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

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

因此，像往常一样，查询将按字母顺序排列 Orders 表中每个 Order 的所有 CustomerID.

So, as usual, the query brings all the CustomerID for each Order in the Orders table ordered alphabetically.

但是！如果我像这样使用 Distinct() 方法:

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

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

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

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

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

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

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

正如我们所看到的 **ORDER BY 子句丢失了.这是为什么?

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

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

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

推荐答案

来自 可查询.独特的文档;

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

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

换句话说，当你在它上面使用 Distinct() 时，现有的 IQueryable 的任何顺序都会丢失.

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

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

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

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

这篇关于DISTINCT() 和 ORDERBY 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！