Convert SQL statement to Linq

Question

How can I convert the following SQL statement into a LinqToSQL statement?

select field, 1 as ordering from table where field2 = condition1
union all
select field, 7 as ordering from table where field2 = condition2
union all
select field, 3 as ordering from table where field2 = condition3
union all
select field, 2 as ordering from table where field2 = condition4
order by ordering

In effect I'm just joining a couple of queries and ordering the resultset based on the origin of the row.

I can manage the union, as below, but I can't seem to get LinqToSQL to order the entire result set, I can only get it to order each individual query.

from t in table
where
condition
select new { field, ordering = 1 }
).Union
(
from t2 in table2
where
condition
select new { field ordering = 7 }
).Union
(
from t3 in table3
where
condition
select new { field ordering = 3 }
).Union
(
from t4 in table4
where
condition
select new { field ordering = 2 }
);

Answer 1

LINQ to SQL will help you get just as much data on the wire as needed so reordering it in memory with an additional statement shouldn't be a performance hit. Of course this time it would be a LINQ to Objects instead.

Have you also tried calling OrderBy() or OrderByDescending() after your last Union?

Answer 2

Two thoughts; with UNION ALL, .Concat(...) is more appropriate than .Union(...).

Second; you can do the ordering separately in LINQ-to-Objects if necessary:

var origQry = ...

var qry = origQry.AsEnumerable().OrderBy(x => x.ordering);

This will then do the order-by in memory.