NHibernate V1.2

I assume this is a newbie question, but I havent seen any examples of fetching that spans more than one level of relations.

Consider the following scenario involving a 3-level relationship:

I have a "GrandParents" table.
I have a "Parents" table with many-to-one relationship with "GrandParents"
I have a "Children" table with many-to-one relationship with "Parents".

How to "hydrate" a full relationship in a single select?
I have been using hql, but not successfully.

I cannot use session.CreateQuery("from Grandparents") because this will cause n+1 select problem for Parents and Children

I cannot use session.CreateQuery("from GrandParents g left join fetch g.Parents p") because this will cause n+1 select problem for children

I cannot use session.CreateQuery("from GrandParents g left join fetch g.Parents p left join fetch p.Children c") because this throws an exception about multiple collections in a query.

This is not possible with HQL, may be possible with Criteria.

if you always need the complete object graph you have the possibility to set the attribute 'lazy=false' in your collection mappings. This doesn't guarantee that NHibernate uses a single select, but you will get the complete graph. There is a setting in the NHibernate configuration which affects the SQL generation (max_fetch_depth) and you can define the 'fetch=join' attribute in your many-to-one mapping.

I have experimented with the settings a little bit, but didn't recognized an effect. But my object graph is very complex (up to six level deep with some cross references). Also i'm still using NHibernate 1.0.4 (hope i can upgrade to 1.2 in the next time).

Regards
Klaus

>> if you always need the complete object graph you have the possibility to set the attribute 'lazy=false' in your collection mappings.

Indeed you can, but be very wary of this. Before you know it, you'll be retrieving the whole database and that will be slow (you might not notice, until you test with real quantities of data).

you are right in principle. But it depends on your model and mapping and how you use it in your application. Our object graph contains around 30 classes nested up to 6 levels depth. The graph will be built up from several hundreds of database records. But we only read one root object a time from the database. So the load time is acceptable. If we have to show lists which contains our root object we use views to read the data from database. As you can map views like tables in NHibernate this won't break our presistence architecture.

Regards
Klaus

Regardless of best performance practice, regardless of lazy/eager mappings, I was wondering if it is at all possible to load a large hierarchy (3+tables) of relationships using a SINGLE select, and I havent seen a solution to this problem yet.

Im asking this question on a theoretical level

In principle this should be possible. In a former project we built views which joined 10 and more tables. With some hints they performed quite fast. But we had seen that it's essential to have a very performant server to process such SQL's. Also there will be a great overhead on data transfer as the rows returned will contain much duplicated data.

In our current project our client works in a document oriented style (somehow like a word document). We have just some overhead on loading and saving data. Depending on the complexity of the object hierarchy a load / save takes around 1 - 2 minutes. In between everything happens in memory.

If i trace the database traffic with the Sql-Server Profiler i can see, that NHibernate doesn't generate optimal SQL. There are parts in our object hierarchy which could be loaded via join, but NHibernates loads the collections row by row. Nevertheless the overall performance is acceptable and we don't have to worry much about persisting our data. So we are very suited with the facilites of NHibernate.

Regards
Klaus