NHibernate version: 1.0.2.0
Database: SQL Server 2005

I have the following object relations:

AdoptionPlan 1-----* Process 1-----* Activity *-----* Policy

The mapping document has all relations set to lazy="true". For a given adoption plan, I want to eagerly fetch all processes, all activities and all policies. I have tried this using HQL as well as criteria query, but with no luck.

Here's my attempt using HQL:



This code generates the following exception: "cannot fetch multiple collections in one query"

Here's a second attempt using criteria query:



Now this query does not throw an exception, but looking at the logs, it is obviously ignoring the FetchMode.Join. I can see many-many selects happening instead of one.

Can someone tell me if NHibernate supports eager joining of nested tables?

Thanks.
Naresh

If NHibernate cannot do this, this would be a serious performance issue. This is a very standard set of relationships in any real life project.
Has anyone run across this issue? If so, how did you solve it?

Regards,
Manish

But using a join for all these relations would return an incredible amount of hugely redundant data. You would get a crapload of rows for each AdoptionPlan. This would be inefficient.

I think it is more performant to use select fetching combined with caching (set cache-usage properties).

Well, the performance is dependent on multiple factors such as size of data etc., but NHibernate should not prevent multi-level joins assuming poor performance. I tried this with Hibernate 3.x and it does the multi-level join without any problems. So the question is does NHibernate artificially prevent a multi-level join, or is it just a bug that will be eventually fixed? Is there a work around?

Naresh

I have this exact same problem. Basically,

Table A, B, and C

A has a one-to-many collection of B and B has a one-to-many collection of C.

Hibernate wants to do multiple selects, but I really want it in one select. Why? If I code this with a stored procedure, I can serve 200+ pages per second... getting this object from hibenate its more like 10 pages per sec.

And, when I get a collection of A, with all the other queries it'll have to run, we sometimes do 80+ selects on one page request. Thats alot of sql roundtrips!

Any work arounds to this? We need the data real time, so caching is not an option for us.

Can one of the committers please show us the light on this :-)? This could be a make-or-break decision for adopting NHibernate.

Thanks.

Fetching multiple collections in one query is not supported by NHibernate. Since Hibernate supports this, it will eventually be ported but don't expect it soon.

Fair enough. Can you give us an idea of the Hibernate 3.x porting plan? Is this being actively pursued? Are you looking for help?

Thanks.
Naresh

The plan is to automate the process using some kind of translator (or possibly some other approach, but a translator seems to be the best solution so far). I am working on the translator right now and I'm going to open-source it once/if it reaches some usable state.

Until then, see Contributions Welcome for contributing to 1.2.0.

Thanks for the update Sergey.

This is basically a requirement for us (fetching multiple collections in one join). Based on your professional opinion, how long do you think it would take a good developer with reasonable knowledge of nHIbernate to implement this one feature?

Is it something that could "easily" be done or would it require changing a massive amount of nhibernate code?

thanks for your input.

I believe it shouldn't be that hard, most of the relevant infrastructure is already ported from Hibernate, one just needs to add missing bits and test it. However understanding [N]Hibernate internals is quite difficult even for people who know the external APIs.