Eager Fetching Deep Graphs with NHibernate

Eager fetching a deep graph with NHibernate can seem a pretty daunting task, but it can be done.

The trick is not to try to issue one query, but instead multiple queries that are batched together.

I would highly recommended you checkout Ayende’s great post on Eagerly loading entity associations efficiently with NHibernate.

Go ahead, read it, I will wait…

Ok, with that under our belt, consider this graph:

graph

Here we have a Customer, with multiple Locations and Contacts, and a reference to a Demographics and Address object. Each Location and Contact also have a reference to an Address.

If you need to load this whole graph, you are in for a bit of work, and you have to be really careful about the collection associations, if a Customer has 2000 locations, do you really want to load all those in memory?

Lets assume we don’t need the entire graph, but instead want the Customer, 5 of its Locations and their corresponding Address, 5 Contacts, the Demographics and the Primary Address.

As Ayende, taught us, we should do this in multiple queries.

We can issue three queries to do this, one for the Customer itself and  its references (many-to-one), Demographics and PrimaryAddress, one for the Location and its Addresses and one for the Contacts.

queries

Notice in the first query, we are eager fetching, the PrimaryAddress and the Demographics.

The second query, we are fetching the Contacts.

The third query is more interesting, we are fetching the Locations and its Address.

The resulting query, is pretty intense, but works. The below image is only showing a partial view of the actual query.

query

Disclaimer: This puts a bit of strain on the database, a better solution, IMHO, would be to ditch loading the graph entirely and instead issue a batch of small queries to fetch only the data that needed. Of course every situation is different, so you have to use what makes sense in your situation.

Hope this helps!

Joe

TrackBack

TrackBack URL for this entry:
https://www.typepad.com/services/trackback/6a0133ecbab7ab970b019aff9135a6970d

Listed below are links to weblogs that reference Eager Fetching Deep Graphs with NHibernate:

Comments

You can follow this conversation by subscribing to the comment feed for this post.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment