Correct behavior of OrderBy

Question

I have encountered something that puzzles me and I would like to see your opinion on the matter. It turns out that linq to sql and entity framework threats consecutive order by's differently.

The following code is used just for example and I am not claiming it has any sense at all:

Linq to sql:

DataClasses1DataContext db = new DataClasses1DataContext();
        var result = (from c in db.Products
                      orderby c.ProductName
                      orderby c.UnitPrice
                      orderby c.UnitsOnOrder
                      select c).ToList();

What it generats on the server side:

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
ORDER BY [t0].[UnitsOnOrder], [t0].[UnitPrice], [t0].[ProductName]

The same test with Entity Framework generates this:

    SELECT 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
ORDER BY [Extent1].[UnitsOnOrder] ASC

As you can see Linq To Sql adds all the requested order by's where the last one has the highest priority (which in my opinion is correct). On the other hand entity framework respects only the last order by and disregards all the others.

Now I know there is an order by then by clause that can be used but I am just wondering which behavior is more correct. Also as far as I remember the query extenders used in asp are working with a separate order by which if applied on a query generated from a different data source will not work correctly (according to the above example one of the order by's will be omitted)

is the UnitsOnOrder unique? Looks quer to me – Random Dev Oct 03 '11 at 08:16 — Random Dev, Oct 03 '11 at 08:16

score 5 · Accepted Answer · answered Oct 03 '11 at 08:17

5

My opinion is that EF is correct. I don't know why L2S would do what you're describing - in my opinion, if you add an OrderBy clause instead of using ThenBy, it should overwrite any existing OrderBys.

When you're working with Linq-To-Objects, you should see OrderBy replace any previous ones, so it makes more sense to me to have the data-driven LINQ act the same.

If the behavior changed the way you're describing, then it seems that Microsoft agrees, since EF was designed to replace L2S.

answered Oct 03 '11 at 08:17

Joe Enos

39,478
11
80
136

That's exactly what `ThenBy` is for. To combine more ordering conditions. All others just override previous ones. – Robert Koritnik Oct 03 '11 at 08:25
The problem with this structure of orderby remains the fact that the query extenders in ASP.NET add a different order by to your already existing query. So if your already existing query has already added another order by than the one from the query extender will be ignored. Now I am not saying that this is necessarily wrong but I am just really puzzled. – Petar Petkov Oct 03 '11 at 08:31
Not sure what the solution is there - personally, I believe that ASP.NET should not have any influence over the query, and the sorting/filtering should be done explicitly in the data-layer code. This leaves ASP.NET to simply spit out the data as-is, instead of doing its own custom stuff in the query extenders. Call me old-fashioned, but the more you do in code instead of markup, the easier it is to accomplish your goals - C# code is a lot easier to write and debug than the fancy ASP.NET stuff that Microsoft keeps adding. – Joe Enos Oct 03 '11 at 08:55
The hole idea is to simplify the things up. So it seems that the query extenders will simply not work with entity framework. Dont get me wrong I am fine with the hand coding.. have been doing that anyway but I was just wondering. Thanks for your valuable input Joe! – Petar Petkov Oct 03 '11 at 09:12
No problemo - best of luck with your project. – Joe Enos Oct 03 '11 at 09:19

score 4 · Answer 2 · answered Oct 03 '11 at 08:21

4

What i've learned is that the order by is written like this:

DataClasses1DataContext db = new DataClasses1DataContext();
        var result = (from c in db.Products
                      orderby c.UnitsOnOrder, c.UnitPrice, c.ProductName
                      select c).ToList();

And like that you can see the order clear to every one.

answered Oct 03 '11 at 08:21

Frederiek

1,605
2
17
32

1

This is also preferred way of ordering in LINQ. – Robert Koritnik Oct 03 '11 at 08:26
The problem with this structure of orderby remains the fact that the query extenders in ASP.NET add a different order by to your already existing query. So if your already existing query has already added another order by than the one from the query extender will be ignored. Now I am not saying that this is necessarily wrong but I am just really puzzled. – Petar Petkov Oct 03 '11 at 08:31

Correct behavior of OrderBy

2 Answers2

Linked