HQL query with Left Join in NHibernate 2

Question

How can I give conditions for left join in NHibernate 2.0 HQL query.

Eg in SQL.

select t1.* from table1 t1
left join table t2 on t2.id = t1.id and t2.column2 = t1.column2

I tried the below HQL query, but got an exception "unexpected token: with"

select t1 from Table1 t1
left join t1.Table2 t2 with t2.column 2 = t1.column2

score 0 · Answer 1 · edited May 23 '17 at 11:52

There is no need to use ON statement on (LEFT or any other) JOIN. That condition will be injected by mapping. There is the HQL doc:

14.3. Associations and joins

So, this HQL will work:

SELECT t1 from Entity1 AS t1 
  LEFT JOIN t1.ReferencePropertyToEntity2 t2

and the generated sql will be like this:

SELECT t1 from Table1 AS t1 
  LEFT JOIN Table2 t2 
     ON t1.ReferenceColumnToTable2 = t2.Table2_ID

But in case, that we want to do more restrictions, we can extend ON clause with more conditions - but they will be all applied with AND

SELECT t1 from Entity1 AS t1 
  LEFT JOIN t1.ReferencePropertyToEntity2 t2 
    WITH t2.IsActive = 1
     OR t1.IsDeleted = 0

will result in

SELECT t1 from Table1 AS t1 
  LEFT JOIN Table2 t2 
     ON t1.ReferenceColumnToTable2 = t2.Table2_ID
     AND (
       t2.IsActive = 1 OR t1.IsDeleted = 0
     )

So, in case that we want to use WITH to totally replace ON generated by mapping, we have to go different way - with CROSS JOIN:

HQL query with Left Join in NHibernate 2

1 Answers1

14.3. Associations and joins