Eager loading a tree with nHibernate re-queries leaf nodes

Question

Looking for some expertise before I declare the NHibernate framework broken or myself crazy!

I'm trying to eagerly load a self-referencing tree with NHibernate and can successfully load most children lists, however I can't seem to get it to work with leaf nodes. My query is:

"select p from Proposal p join fetch p.Structures s join fetch s.theChildrenList c " +
"where p._persistenceId = :p1 and s.theProposal = :p1 and c.theProposal = :p1")
.SetParameter("p1", aProposalId)
.SetResultTransformer(new DistinctRootEntityResultTransformer()).
UniqueResult<Proposal>();

This will return all the records correctly, but does not properly populate the childrenList of leaf nodes (which should be by definition empty). Instead when I get a proxy and upon searching the tree, thousands of pointless zero record queries.

I've tried: 1. using left join rather than join 2. Making the children's list not-lazy and fetch="join" and removing the hql (as a proof of concept, performance downside is unacceptable elsewhere) 3. Messing with the not-found property that I saw someone use in the hibernate forums

All of which give me the same results... one large query with all the data (good) and thousands of small queries which return no data (bad). Any ideas?

I'm using NHibernate 2.2 and here is the relevant part of the mappings file for reference:

  <many-to-one    name="theParentStructure"
                  column="PARENT_STRUCTURE_ID"
                  class="Structure"
                  access="field"
                  update="false"
                  insert="false"
                  not-found="ignore"/>

  <bag            name="theChildrenList"
                  generic="true"
                  table="STRUCTURE"
                  access="field"
                  cascade="all-delete-orphan"
                  inverse="true" fetch="join" lazy="false">  <--Both with and without the last two properties
     <key column="PARENT_STRUCTURE_ID" />
     <one-to-many class="Structure"/>
  </bag>

Any help would be appreciated!!

MonkeyCoder MonkeyCoder · Accepted Answer · 2011-10-08T18:56:50

I was interested in this topic quite some time ago, so please don't take everything that I say for granted (maybe someone with more NHibernate experience could correct me in case I'm wrong).

Last time I've checked the described situation was quite a problem as Nhibernate was generating separate queries for each level of child entities. If you know in advance that your depth of the tree is not going to be extremely big than probably you could live without the eager loading of children... But if your structure is not limited in depth you should probably look into alternatives.

Assuming you are using SqlServer

One solution that I've found quite easy to implement was to use the recursive self-join / CTE. This solution involves switching to stored-procedures as the source of your query and to re-create the hierarchy manually in code. To fetch entire tree you only need one db query (which can include all sorts of filters and sub-queries + ordering) and you can still re-use your NHibernate mappings for all CRUD operations. One disadvantage of this solution is that you later on have to maintain the query code on the db side (column and mapping changes etc.).

Modified preorder tree traversal algorithm

This is my ideal solution for tree persistence as it does not require any stored-procedures and can be perfectly integrated with NHibernate and the Criteria API (which for me is a huge advantage as I can freely create advanced and reusable filters in code). From the performance point of view all selects are almost cost free - you move the balance towards the insert, update and delete operations as you need to re-calculate the tree on every change - but this can be done with hql like so (pseudo-code):

HQLNamedQuery hql = new HQLNamedQuery();
hql.Query = "UPDATE " + typeof(THierarchy).FullName +
    " SET TraversalLeft = (TraversalLeft + :traversalChange) " +
    " WHERE BaseNodeId = :baseNodeId AND TraversalLeft > :minTr AND TraversalLeft <= :maxTr";

I've managed to implement all sorts of operations using this technique (like add, add range, move, re-order, get descendants, get ancestors, count descendants etc.) and I have to say that once you get the basic principle you can perfectly integrate it in different projects.

Link to the article that got me started on this topic (it doesn't use NHibernate unfortunately): http://weblogs.asp.net/aghausman/archive/2009/03/16/storing-retrieving-hierarchical-data-in-sql-server-database.aspx

Mapping a tree in NHibernate

Maybe you should also take a look at this post (exact part starts from An alternative approach): http://nhibernate.hibernatingrhinos.com/16/how-to-map-a-tree-in-nhibernate

This basically comes down to adding two additional collections to your mapping: Ancestors and Descendants and performing three queries: main query, load ancestors and load descendants - this should prevent NHibernate from performing additional queries when accessing child nodes.

I hope this helps.

Eager loading a tree with nHibernate re-queries leaf nodes

2 Answers