NHibernate HQL Join Not Returning All Required Rows

Question

Hi,

I am modifying an existing HQL query which returns individual columns rather than an object graph but now I am not getting all rows that I need.

Here a few facts about the current schema:

• An Estimate belongs to a Contract.
• The OwningDepartment property of a Contract can be null.
• The ParentBusinessStream property of a Department cannot be null

This is the query:

select e.ID, e.StatusCode.ID, e.InputDate, e.ParentClient.Name, e.ParentContractLocation.ParentLocation.Description, e.Description, e.InternalRef, e.ExternalRef, e.TotalIncTax, e.TaxTotal, e.Closed, e.ViewedByClient, e.HelpdeskRef, e.ParentContract.Reference, d.ParentBusinessStream.Title, d.Name
from Estimate e, Department d where (e.ParentContract.ID in (select cs.ParentContract.ID from ContractStaff cs
where cs.ParentStaff.ID=:staffID)) and ((d.ID = e.ParentContract.OwningDepartment.ID) OR (d.ID is null)) order by e.ID

Unfortunately my query is not returning Estimates where the parent contract does not have an owning department. Instead I want the relevant fields to just be null. I tried a left outer join but got the same results.

Any help would be very much appreciated. Apologies if I've done something stupid.

Cheers,

James

Answer 1

i've found that unusual queries containing left outer joins are better off by using an ISQLQuery which gives you access to proper SQL syntax as well as some HQL power.

Besides that, you don't provide mapping files which usually are helpful

Answer 2

I prefer to use ISQLQuery too but am having the same problem in that I need to be Database Independent. Anyone know how I can do this?

Answer 3

I think I have worked it out: d.ParentBusinessStream.Title is an implicit inner join but since d can be null it doesn't work correctly. I have changed my query to take this into account