CODEWITHSUNDEEP
nhibernatefluent-nhibernatenhibernate-mapping
Ivar
Ivar

Reputation: 119

nhibernate3 query - collections with collections

I have a few dropdowns in my webpage. These are linked and have a similar class structure with bi-directional linking.

In other words: class Alpha has a list of class Beta which in turn has a list of class Charlie. Each class Beta also has its own list of Alpha (the ones it belongs to) and each class Charlie has its own list of Beta.

I am using NHibernate 3 with fluent nhibernate and automappings.

Now. If I simply would run a 

session.CreateCriteria<Alpha>().SetMaxResults(1000).List<Alpha>();

I get the N+1 problem when I loop over the collections.

The way I see it the following SQL's should be all that's queried to the database

select top 1000 * from Alpha
select top 1000 * from Beta
select top 1000 * from Charlie
select * from Alpha2Beta
select * from Beta2Charlie

But how do I write the query for this to work??

Upvotes: 1

Views: 101

Answers (3)

Rytmis
Rytmis

Reputation: 32067

As far as I know, there's no way you can help this on a query by query level, like you can with join fetching. However, if you change the mappings and set the default fetch mode for the associations to be "subquery", you might be pleasantly surprised:

From the Hibernate Documentation (works equally well with NHibernate):

With fetch="subselect" on a collection you can tell Hibernate to not only load this collection in the second SELECT (either lazy or non-lazy), but also all other collections for all "owning" entities you loaded in the first SELECT. This is especially useful for fetching multiple collections in parallel"

What this means is that when the first association is required, NHibernate will, instead of loading one association, recall the query you used to get the root entity, then load the association data for all instances of the root entity type that were returned by the query.

That said, if you're loading 1K entities and you expect the associations to have more than a couple of records each, you're probably just going to go from a (SELECT N+1)^2 to a "holy crap I just loaded the entire database into memory". ;-)

(Note that if you do this and have a scenario where you load the Alpha list, but only need the associated Betas for a single Alpha, you're still going to load all of them and there's nothing you can do about that. In practice though, I've found this to be a very rare scenario, so usually subselect fetch suits me very well.)

Upvotes: 0

Nikola Radosavljević
Nikola Radosavljević

Reputation: 6911

There's a nice trick Ayende showed in his blog. I haven't tried it personally as I decided to change my BL to avoid this problem, so take this with a grain of salt.

You should be able to load collections separately and let NHibernate connect entities, using NHibernate Futures. Since it's not a light subject it's better that you read his blog post.

Upvotes: 1

JeffreyABecker
JeffreyABecker

Reputation: 2743

If you're using Criteria you'll need to include Dyanmic Fetching method calls.

Upvotes: 0

Related Questions