Union all support in ServiceStack ORM lite 4.5.4


In my application user can define multiple filters for specific type. I would like to somehow combine these queries in single one.

SqlExpression<Notice> query1 = Db.From<Notice>(s=>s.param1 == 2);
// ...queries are more complicated but result type is same
SqlExpression<Notice> query2 = Db.From<Notice>(s=>s.param1 == 3 && s.param2 == 3);

SqlExpression<Notice> unionAll = query1.UnionAll(query2);
var result = Db.Select(unionAll);

Any idea how to implement this in Orm Lite (4.5.4)?

ServiceStack.OrmLite does not support Union() feature, you should construct SQL by own. You can vote for Unions implementation on ServiceStack uservoice

Whilst it’s not supported yet, you should be able to construct it with something like:

var sql = query1.ToSelectStatement()
                 + " UNION ALL " +

var results = db.SqlList<Notice>(sql);

Is it possible to parse generated sql back into SqlExpression to next operations (additional filtering, ordering paging etc)?

You can’t parse a SQL String back into a typed C# expression but you can combine raw SQL Fragments with many of SqlExpression APIs, e.g:

query1.Where("Field = @arg", new { arg = 1 });

query1.UnsafeWhere("Field = 'foo'"); //bypass raw sql fragment restrictions
1 Like