Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

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!

Bookmark and Share

Comments

cws said:

You could also add a or to check for null on b right?

# May 31, 2011 8:16 AM

Ricardo Peres said:

@cws:

Yes, that is another option, although I, personally, don't like to use OR: a UNION is better for performance.

# May 31, 2011 9:59 AM

Samrat said:

a related qtuesion. we have a parent/child relationship set up between class A and class B.class B has a back-pointer to it's parent   parentA. the mapping for the property in Class B looks like this (sorry for the formatting) As can see, cascade is set to  none . So i assumed that when we saved an instance of the child class B nothing would happen to the parent object A. Ah, but it turns out that if you update the instance of class A which is hanging off of class B and the update class B the change to A is persisted to the database and the version number on class A is incremented. Sort of exactly the same behaviour i would have expected if cascase= true .  Any thoughts on why this is happening, anyone?thanks,Garth Engdahl

# September 30, 2012 2:35 AM