How to use multi-level, deep, fetch join in complex queries?

devguy · **Joined:** Thu Jun 26, 2008 9:11 am **Posts:** 2

Hi guys,
I'm pretty new to NHibernate, and I need some help. I'm using the latest release of NH, and I'm querying SQL Server 2005.

My tables and entities have the following structure:

Portal
....Id
....Names (Dictionary<string, string>)
....Settings (Dictionary<string, string>)
....Tabs (List<Tab>)
........Id
........Name
........Names (Dictionary<string, string>)
........Settings (Dictionary<string, string>)
........Modules (List<Module>)
............Id
............Name
............Settings (Dictionary<string, string>)
............Names (Dictionary<string, string>)

I'm trying to use a fetch-join query to get all the data in a single query, because I immediately need all data (I'll return it to the front-end server, via WCF). The query is as follows:

Portal p = hb.Session.CreateCriteria(typeof(Portal))
.Add(Expression.Eq("Id", Id))
.SetFetchMode("Tabs", FetchMode.Join)
.SetFetchMode("Settings", FetchMode.Join)
.SetFetchMode("Modules", FetchMode.Join)
.SetFetchMode("Names", FetchMode.Join)
.UniqueResult<Portal>();

What I noticed is the the Tabs and Modules child collections are filled immediately, while the Names and Settings collections are not, they are lazy loaded. Might it be that's because I have collections with the same names at various levels, and NHibernate doesn't know to which of them it should apply the fetch-join?

Can you suggest a way to run a single query and fill the entire data tree? Thanks a lot for your support.
Cheers,
- Marco

dotjosh · **Joined:** Fri Jun 29, 2007 1:28 am **Posts:** 2

I'm BUMPING this, I have the same exact question.

a.ritchie · **Joined:** Wed Mar 14, 2007 12:35 pm **Posts:** 24

Check your max fetch depth in the config. I think by default it is 3. The depth that you are searching is 4th level which may explain the issue.

From documentation:
hibernate.max_fetch_depth - Set a maximum "depth" for the outer join fetch tree for single-ended associations (one-to-one, many-to-one). A 0 disables default outer join fetching.
eg. recommended values between 0 and 3

wolli · **Posted:** Thu Aug 14, 2008 1:35 am

FetchMode.Join is non-lazy, but I don't know what happens if you combine it with a lazy attribute on the collection. Maybe it's ignored then.
There may also be a problem if you use bags. In that case you can't fetch the items with joins because hibernate can't decide if the duplicate objects that a returned through the join are real (which is allowed in a bag) or because of the join.