1

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?

Mattias
  • 684
  • 3
  • 7
  • 16

3 Answers3

6

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>();
Community
  • 1
  • 1
Rippo
  • 22,117
  • 14
  • 78
  • 117
6

You can't do a union directly, but you can do two future queries and union the results in code:

var resultSet1 = this.Session.CreateCriteria<A>().Future<A>();
var resultSet2 = this.Session.CreateCriteria<B>().Future<B>();

After this, when either result set is enumerated, NHibernate will issue a single query to the database which will return multiple result sets. Note, if you are not using SQL Server, the database may not support multiple result sets.

LeftyX
  • 35,328
  • 21
  • 132
  • 193
eulerfx
  • 36,769
  • 7
  • 61
  • 83
3

You can use -

NHibernate.Criterion.Restrictions.Or(ICriterion FirstQuery,
                                     ICriterion SecondQuery)

as your Criteria in a single query.

Mr Mush
  • 1,538
  • 3
  • 25
  • 38
  • I'll try this in the morning and get back to you – Mattias Dec 21 '11 at 20:23
  • This also works, but since i do an OR-clause with the base class and the cross table i join with (which i should have explained in the question), the resulting query results in a significant performance penalty. Thanks for the answer though. +1 – Mattias Dec 22 '11 at 11:49