Inner or Right Outer Join in Nhibernate and Fluent Nhibernate on Many to Many collection

Question

How can I force NHibernate to do a RIGHT outer join or an INNER join instead of a LEFT outer join on a many to many collection?

The reason I would want to do this is because of filtering applied to the collection elements. With a left join, you get the same number of rows returned as an unfiltered query, but the elements that are filtered out just show NULL for all of the fields. However, with a right join, the correct number of rows and elements are returned from the query.

I would expect that one could specify the join somewhere in the mapping of the collection..

Answer 1

I don't think it's possible to specify a right or inner join in the collection mapping. The only options with the fetch clause are the default left outer join and sequential select.

The problem is that when you create a mapping, NHibernate needs to know how to fetch the collection elements for any arbitrary root item from the left side of the join. With a right or inner join, the root object may not exist in the returned collection, so you're stuck at that point.

If the filter criteria is static, you can specify a where clause in the mapping. I think this would be the recommended solution for your situation.

A workaround would be to make the collection private in your object, then create another property that calls an HQL query to implement the inner join and returns that collection. This returned collection would have the semantics you want, but you'll need separate methods to add or remove items from the collection.

Answer 2

You can use NHibernate's HQL syntax to generate a query that's reminiscent of SQL, but uses NHibernate's mapping abilities. HQL supports right outer join (or just right join for short). The following pages are good references for NHibernate's HQL query language:

• HQL Query Syntax
• HQL Queries