Order by does not work with Concat() in LINQ

Question

Using VB.net and the following LINQ statement. I suspect the "Order by" does not work with Concat(). I want to list the current item the user has and then list more available items in asending order. So first i select the current item from the db and then select the next available items in order. LINQ is ignoring the order by statement and sorting by the PK (which is itemID) I examined the list immediately after executing the statement. When I break up the statement and do them separately they work as predicted. Any ideas, suggestions or comments. Thanks, PM

(From items In myDatabase.ItemAssignments _
 Where items.BuildingID = buildingID _
 And items.ResidentID = ResidentID _
 Select items).Concat(From moreitems In myDatabase.ItemAssignments _
                      Where moreitems.occupied = 0 _
                      And moreitems.BuildingID = buildingID _
                      Order by moreitems.Floor, moreitems.ItemNumber _
                      Select moreitems)

Answer 1

The Concat does indeed ignore the order by clause when it comes to LINQ to SQL. This can be verified from the generated SQL if you're using LINQPad or setup the DataContext.Log property.

One way to deal with this is to introduce a dummy value via an anonymous type to help with the ordering. I've split up the queries below for clarity, although the same approach is possible using the query syntax you started out with up until you need to specify the ordering.

Dim firstQuery = From items In myDatabase.ItemAssignments _
                 Where items.BuildingID = buildingID _
                 And items.ResidentID = ResidentID _
                 Select New With { .Row = items, .Order = 1 }
Dim secondQuery = From moreitems In myDatabase.ItemAssignments _
                  Where moreitems.occupied = 0 _
                  And moreitems.BuildingID = buildingID _
                  Select New With { .Row = moreitems, .Order = 2 }

Dim query = firstQuery.Concat(secondQuery) _
                      .OrderBy(Function(o) o.Order) _
                      .ThenBy(Function(o) o.Row.Floor) _
                      .ThenBy(Function(o) o.Row.ItemNumber) _
                      .Select(Function(o) o.Row)

Another less desirable option is to call the AsEnumerable method on one of the queries, which will pull the results from the database. Depending on the number of items involved and if further filtering is needed, this may have an adverse affect on performance.

To use this this approach change the first part of your original query to use:

From items In myDatabase.ItemAssignments.AsEnumerable() ...

The ordering on your second part will then work as intended and the generated SQL will reflect as much.