Merge results of 2 LINQ To SQL queries and keep their ordering

Question

I need to append the results of one LINQ To SQL query to another on the database server side without reordering rows.

I need all errored orders first, then the pending orders.

var error = database.Orders.
    Where(o => o.Status == XOrderStatus.Error).
    OrderByDescending(o => o.Id);
var pending = database.Orders.
    Where(o => o.Status == XOrderStatus.PendingReview).
    OrderByDescending(o => o.Id);
    var r = error.OrderedUnion(pending);

How I can implement the OrderedUnion() method? Concat, Union and Distinct methods completely eliminate OrderByDescending call (by design).

I know it is possible to do with two ToArray() calls, but I am interested having this done at the database level.

Answer 1

You can concatente them together and then order by a column that seperates the order groups. so for instance in an SQL Query you would do this:

ORDER BY XOrderStatus, Id

and that would order by ID but with the two OrderStatuses grouped.

I don't know linq to sql (sorry!) but a quick Google mentioned this may work:

.Orderby(o => o.XOrderStatus).ThenBy(o => o.Id)

Original Source:

Multiple "order by" in LINQ

Answer 2

I found much better solution:

var all = database.Orders.
    Where(o =>
        o.Status == XOrderStatus.Error ||
        o.Status == XOrderStatus.PendingReview).
    OrderBy(o => o.Status == XOrderStatus.Error ? 0 : 1).
    ThenByDescending(o => o.Id).
    ToArray();