Union with NHibernate and Criteria?

Question

Union with NHibernate and Criteria:

Is it possible in Criteria or QueryOver? If not, is there any other way to achieve a union of two result within the same query?

Answer 1

This is not possible even using HQL. See this other S.O. post

One way is to drop back to raw SQL and use a named query

<sql-query name="MyQuery">
<![CDATA[
select col1,col2 from table1
union
select col1,colA from table2
]]>
</sql-query>

And use the AliasToBeanResultTransformer to transform it back into your DTO/POCO

var query = Session
  .GetNamedQuery("MyQuery")
  .SetResultTransformer(new AliasToBeanResultTransformer(typeof(MyDto)));
  return query.List<MyDto>();