Union all support in ServiceStack ORM lite 4.5.4

ServiceStack.OrmLite
1

Hi

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)?

2

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

3

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


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

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

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

5

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