NHibernate Select in From Case

Question

Hello

I have problems with a query in NHibernate.

The original SQL query looks like

SELECT Id
  ,Table1_Id
  ,Table2_Id
  ,Table3_Id
FROM (
    SELECT Id
      ,Table1_Id
      ,Table2_Id
      ,Table3_Id
    FROM Table_123
    WHERE Table2_Id = 72

    UNION SELECT
      100 As Id
     ,151 As Table1_Id
     ,72 As Table2_Id
     ,20 As Table3_Id
) a
WHERE Table2_Id = 72

I need this code writen in NHibernate.Criteria. Is that possible?

Greez Riyixy

Answer 1

Criteria and HQL don't support Union https://www.hibernate.org/117.html#A21

So you can't do this query using criteria/HQL in its current form but you will be able to use a native SQL Query like this:

IQuery sqlQuery = sess.CreateSQLQuery("select Id,Table1_Id....", "ClassName", typeof(ClassName));
sqlQuery.SetMaxResults(50);
IList entities = sqlQuery.List();

For future reference its better to express your questions using abstract types. Table1/Table2/Table3 makes it difficult to understand the problem.

Is the Union select really necessary - this basically adds a line to the resultset, you could just as easily create that entity in code if you wanted to have it returned.