How to perform a table UNION in SubSonic

Question

I have a query that performs a UNION between two tables. I'm trying to implement the same thing with SubSonic. I have two related questions:

• What is the SubSonic syntax to perform a union.
• How do I rename a column. Assuming the UNION will require to match the column names between the two tables

Answer 1

There isn't a UNION syntax in SubSonic. You could use an inline query as dicussed here e.g.

MyTableCollection MyTable = new InlineQuery().ExecuteAsCollection<MyTableCollection>(
    “SELECT Col1,Col2 FROM TableA Union SELECT Col1,Col2 FROM TableB");

The best solution I've found is to implement the UNION inside a view and then get SubSonic to generate a model for that view instead.