CODEWITHSUNDEEP
nhibernatejoinhqlouter-join
Diego Santin
Diego Santin

Reputation: 73

NHibernate HQL Query with left outer join

I would like to retrieve some data from 2 different tables. They are named 'A' and 'B'. There is also another table called 'C'.

Both A and B have a reference to 'C', but 'C' does NOT have a reference neither to A nor B.

My SQL command would be something like this:

select 
    A.x,
    A.y,
    B.z 
from 
    A
LEFT OUTER JOIN C ON C.i = A.i
LEFT OUTER JOIN B ON B.i = C.i

The thing is: I have some data i need in table A, and some data on table B. I need to retrieve all data from table A where a property from table A is equal to a property of the table B, and therefore some data from table B. Thats easy, really simple HQl command (easier with LINQ):

select a.x, a.y, b.z
from A as a, B as b
where a.x = b.x

But the problem is: i also need to retrieve the data from A when a.x is NULL. I would of course get a null value from b.x

I tried using DefaultIfEmpty() on LINQ, but since i use session.Query() over an entity, DefaultIfEmpty is not implemented yet in NH 3.

How can i write that in HQL?

Upvotes: 0

Views: 1503

Answers (1)

Diego Mijelshon
Diego Mijelshon

Reputation: 52725

You can't use left joins in HQL for unmapped relationships.

You can just use a SQLQuery for this.

Upvotes: 1

Related Questions