SQL Union to NHibernate Criteria

sql hibernate nhibernate sqlite sybase
Alexandre Vicenzi picture Alexandre Vicenzi · Sep 4, 2013 · Viewed 8.8k times · Source

There's a way to convert this SQL statement to a NHibernate Criteria?

(select b1.FieldA as Name, b1.FieldA as FullName from Sale b1 where b1.FieldA like '%john%' or b1.FieldA like '%john%' order by b1.Id desc)
union 
(select b2.FieldC as Name, b2.FieldD as FullName from Sale b2 where b2.FieldC like '%john%' or b2.FieldD like '%john%' order by b2.Id desc)
union 
(select c.FieldE as Name, c.FieldF as FullName from Client c where c.FieldE like '%john%' or c.FieldF like '%john%' order by c.Id desc)

I've found that NHibernate doesn't support Unions.

Answer

Thierry picture Thierry · Sep 4, 2013

Try using a view. It can be mapped directly in NHibernate and does not rely on a specific database implementation. You should remove the where clauses, and then you can build your NHibernate criteria against "Name" and "FullName".

  (select b1.FieldA as Name, b1.FieldA as FullName from Sale b1 order by b1.Id desc)
  union 
  (select b2.FieldC as Name, b2.FieldD as FullName from Sale b2 order by b2.Id desc)
  union 
  (select c.FieldE as Name, c.FieldF as FullName from Client c order by c.Id desc)

Related questions

could not extract ResultSet in hibernate

I have a problem with Hibernate. I try to parse to List but It throws an exception: HTTP Status 500 - could not extract ResultSet. When I debug, It fault at line query.list()... My sample code here @Entity @Table(name = "…

Read More
Hibernate show real SQL

if I set <property name="show_sql">true</property> in my hibernate.cfg.xml configuration file in the console I can see the SQL. But it's not real SQL... Can I see the SQL code that …

Read More
How to print a query string with parameter values when using Hibernate

Is it possible in Hibernate to print generated SQL queries with real values instead of question marks? How would you suggest to print queries with real values if it is not possible with Hibernate API?

Read More