SQL AS how to do with OrmLite

#1

I’ve got two tables that I need to UNION, and found a recommendation to use ToSqlStatement on both and concatenate with a " UNION ALL " string.
Howerver, the column name differs, so on one table I’ll need SELECT myCol as otherName.

How do I do this with OrmLite?

#2

No specific API that does it, you’d just need to concatenate the SQL expressions:

#3

Yes, that’s the answer I was referring to, but my question is how to rename columns? Because when you do a UNION the columns must have the same name… (it may seem strange to union between two tables, but I know what I’m doing).

#4

You may be able to use table aliases:
https://docs.servicestack.net/ormlite/reference-support.html#table-aliases

#5

That didn’t do what I need. I’ve used that before for self joins or similar, e.g Employee emp and Employee manager.

But what I need is:

(At least SqlServer and SqLite):
SELECT x AS Name

This is also valid syntax (Sql Server):
SELECT x ‘Name’

With this I could then
select statement table1
UNION
select statement table2, renaming column to match table1

Full raw SQL is always a possibility…

#6

Using anonymous types will use an alias for that property, e.g. this uses ‘A’ and ‘B’:

var q = db.From<Person>()
  .Where(x => x.Age < 50)
  .Select(x => new { A = x.Id, B = x.LastName });

More anonymous type usage examples in: https://docs.servicestack.net/ormlite/apis/select

#7

Thanks, the anonymous type did the rename.

Now I got another problem, which is related to the UNION.
Using the method you suggested, with q1.ToSelectStatement() + " UNION ALL " q2.ToSelectStatement(),
I get trouble if both statements have variables. The “@0” appears twice…

#8

You can avoid conflicts by rasterizing the SQL Params into the SQL query with SqlExpressions’s ToMergedParamsSelectStatement() API.

1 Like
#9

And there’s an API for that too :wink: Thank you.

For future readers, a summary:

I’m merging a column from two tables, and the column is not called the same in the two tables, even if it’s the same info:

var query1 = db.From<Table1>().Where("....").Select(x => x.TheColumn)
var query2 = db.From<Table2>().Where("....").Select(y => new { TheColumn = y.OtherName });
var combinedSql = query1.ToMergedParamsSelectStatement() + " UNION " + query2.ToMergedParamsSelectStatement(); // UNION ALL gives duplicates, which I don't want
var mergedResults = db.Column<int>(combinedSql);
1 Like