fetching multiple nested associations eagerly using nhibernate (and queryover)

I have a database which has multiple nested associates. Basically, the structure is as follows:

Order -> OrderItem -> OrderItemPlaylist -> OrderPlaylistItem -> Track -> Artist

I need to generate a report based on all orders sold in a certain date, which needs to traverse into ALL the mentioned associations in order to generate the required information.

Trying to join all tables together would be an overkill, as it would result in an extremely large cartesian join with many redundant data, considering it would be joinin开发者_Python百科g 6 tables together. Code below:

q.Left.JoinQueryOver<OrderItem>(order => order.OrderItems)
     .Left.JoinQueryOver<OrderItemPlaylist>(orderItem => orderItem.Playlist)
     .Left.JoinQueryOver<OrderItemPlaylistItem>(orderItemPlaylist => orderItemPlaylist.PlaylistItems)
     .Left.JoinQueryOver<Track>(orderItemPlaylistItem => orderItemPlaylistItem.Track)
     .Left.JoinQueryOver<Artist>(track => track.Artist)

The above works, but with even a few orders, each with a few order items, and a playlist each consisting of multiple tracks, the results would explode to thousand records, growing exponentially with each extra order.

Any idea what would be the best and most efficient approach? I've currently tried enabling batch-loading, which greatly scales down the number of database queries but still does not seem to me like a good approach, but more like an 'easy-workaround'.

There is no need for all the data to be loaded in just one SQL query, given the huge amount of data. One SQL query for each association would be perfect I guess. Ideally it would be something where first you get all orders, then you get all the order items for the order and load them in the associated collections, then the playlists for each order item, so on and so forth.

Also, this doesn't have to be specifically in QueryOver, as I can access the .RootCriteria and use the Criteria API.

Any help would be greatly appreciated !

I believe this is what you are looking for

http://ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate

If you prefer one SQL query, what SQL syntax would you expect this to produce? I guess you can't avoid a long sequence of JOINs if you're going for one SQL query.

I guess what I would do is get the entities level by level, using several queries.

You should probably start off by defining the query as best you can in SQL, and looking at the execution plans to find the very best method (and whether your indexes are sufficiant).

At that point you know what you're shooting for, and then it's reasonably easy to try and code the query in HQL or QueryOver or even LINQ and check the results using the SQL writer in NHibernate, or the excellent NHProfiler http://www.nhprof.com.

You are probably right about ending up with several queries. Speed them up by batching as many as you can (that do not depend on each other) into single trips by using the "Future" command in Criteria or QueryOver. You can read more about that here: http://ayende.com/blog/3979/nhibernate-futures