Using nhibernate to filter by the value of an object in a child collection.

Question

Here's the setup.

NHibernate, Fluent NHibenrate and Nhibernate Linq

The entities invoved are

• Fault - a record of a fault occuring
• Alarm - information about the fault, think of it as a fault type ( a list of all possible faults that can occur )
• AlarmDescription - human readable description, one for each language

A fault has an alarm and alarm has a collection of descriptions, one for each langague in the system.

An alarm can be referenced by many faults.

When a user searches one of the paramters they can search ( and order ) faults by is description. Which means passing down the specific language to use.

The SQL to accomplish is brain dead simple to get started :

SELECT f.*, a.*, d.Description
FROM Fault f
JOIN Alarm a ON f.Alarm_id = a.Id
JOIN AlarmDescription d ON a.Id = d.Alarm_id AND d.Language = @lang

The above query would give me all fault, their alarm and the descriptions for the selected langauge.

However getting Nhibernate to generate such a query is proving difficult.

So it boils down to one of the filters being a Child Collection of Child Object of the main object. I have tried to get this working with Linq2Nhibenrate, HQL and trying to get Native SQL to work as well. Native SQL seems the most likley to succeed but I cannot figure out how to get the aliases to map correctly. I'm up for any solution including changing the domain model. This one has had me stumped.

Answer 1

Here's a possible HQL:

select f, d.Description
from Fault f
join fetch f.Alarm a
join a.Descriptions d
where d.Language = :lang

This retrieves a list of tuples (object[2]) where the first element is a Fault (with an initialized Alarm), and the second one is the description text for the selected language.