Linq-to-sql orderby thenby

问题:

I'm using the following query syntax

from table
where 
where
orderby 
orderby

Where the first orderby is a date and second orderby is a date. I would assume this would work like orderby thenby but appears to be doing something else.

1. How can I do an orderby thenby using the above syntax without using extension syntax. (Got it)

2. And what does the orderby, orderby do?

回答1:

How can I do an orderby thenby using the above syntax without using extension syntax.

Use a comma between the fields:

orderby a, b

---

And what does the orderby, orderby do?

When you use orderby twice in a row the elements conceptually will first be sorted using the first orderby, and then sorted again using the second orderby. Because the sorting is defined to be a stable sort (objects which are tied with the second orderby will remain in the same order as after sorting with the first orderby it effectively means that this:

var query = from x in l
            orderby x.A
            orderby x.B
            select x;

is equivalent to:

var query = from x in l
            orderby x.B, x.A
            select x;

The result is that the orderby terms are swapped from what you probably intended.

Testing it with LINQ to SQL

This can be verified by trying it in LINQ to SQL. I created the following query:

var query = from a in dc.Orders
            orderby a.Date
            orderby a.CustomerID
            select a;

and this was the generated SQL:

SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description]
FROM [dbo].[Order] AS [t0]
ORDER BY [t0].[CustomerID], [t0].[Date]

Note that the orderby a.Date is not ignored. Both terms are included in the ORDER BY clause, but in the opposite order than you might have intended.

回答2:

Performing a ThenBy in Query Expression Syntax is straighforward, simply follow the initial orderby with a comma and a 2nd statement:

// Create the data source.
List<Student> students = GetStudents();

// Create the query.
IEnumerable<Student> sortedStudents =
    from student in students
    orderby student.Last ascending, student.First ascending
    select student;

Applying a 2nd orderby using the standard query operators (extension methods) will actually apply the second orderby to the result of the query result which includes the first orderby. In effect, only the second orderby will apply, although you'll still spend CPU time calculating the first order.

This is actually answered directly in the MSDN documentation for the Enumerable.OrderBy and Enumerable.ThenBy methods.

Because IOrderedEnumerable inherits from IEnumerable, you can call OrderBy or OrderByDescending on the results of a call to OrderBy, OrderByDescending, ThenBy or ThenByDescending. Doing this introduces a new primary ordering that ignores the previously established ordering.

回答3:

The ThenBy operator applies a secondary, ascending sort order to the sequence. It is akin to applying a secondary sort order in T-SQL like :

Select * from Customer order by FirstName,lastName

In linq we can write this as:

var thenby = mode.CustList.Select(cus => new
        {
            cus. FirstName,
            cus. LastName,
            cus. EmailAddress
        }).OrderBy(cus => cus. FirstName).ThenBy(cus => cus. LastName);

You can check more about this here: http://dotnetreaders.com/articles/ThenBy_in_LINQ_c-sharp,_Orderby_Thenby_in_LINQ_C-sharp/204

回答4:

The answer to the thenby should be like this

orderby 1, 2

I'll leave part #2 for someone else.