Alliance with NHibernate and Criteria?

Question

Alliance with NHibernate and Criteria:

Is this possible in criteria or QueryOver? If not, is there another way to achieve combining the two results in the same query?

Answer 1

You cannot make a connection directly, but you can make two future queries and combine the results in code:

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

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

Answer 2

This is not possible even using HQL. See This Other SO Post

One way is to return 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 AliasToBeanResultTransformer to convert it back to DTO / POCO

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

Answer 3

You can use -

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

as your criterion in one request.