NHibernate Pitfalls: Collection Restrictions
This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.
Beware using filters and where clause restrictions to filter your NHibernate collections fetched with join. There is a known problem (JIRA issue NH-1930) which can be described like this:
- You have a collection (set, bag, list, etc) mapped with the join fetch mode (eagerly loaded):
1: <set where="DELETED = 0" cascade="all" inverse="true" lazy="false" fetch="join" name="Details">
2: <key column="`ORDER_ID`" />
3: <one-to-many class="OrderDetail" />
4: </set>
- You apply to it a where clause or a filter;
- The restriction is applied to the generated SQL’s global where clause:
1: SELECT a.ID,
2: a.SOMEFIELD,
3: b.ID,
4: b.FKID,
5: b.SOMEOTHERFIELD,
6: b.ISDELETED
7: FROM a
8: LEFT JOIN b
9: ON b.FKID = a.ID
10: WHERE b.ISDELETED = 0
instead of the INNER JOIN ON clause:
1: SELECT a.ID,
2: a.SOMEFIELD,
3: b.ID,
4: b.FKID,
5: b.SOMEOTHERFIELD,
6: b.ISDELETED
7: FROM a
8: LEFT JOIN b
9: ON b.FKID = a.ID AND b.ISDELETED = 0
- If the related table does not have rows that match the association, no records will be returned at all, not even for the main entity that you are querying!
In order to go around it, you can either turn to using select as the fetch mode, disable the restriction or specify it like "DELETED = 0 OR DELETED = NULL". Or, of course, wait for the issue to be fixed!
