Fetching multiple nested collections eagerly in separate SELECTs using NHibernate

Question

I have the following database structure, which I need to traverse entirely for a report.

Product -> ProductVariation -> ProductMedaItem
Product -> CategoryLinks -> Category

I have a query on the Products table, and want to prefetch the above data, as I will be accessing them all together. I want to avoid using JOINs, as the combinations would explode and result in loads of data.

The ideal solution in my eyes would be the following:

• A query is issued to load all matching products
• Then, the result set is parsed, and all product variations are loaded together, based on a query similar to 'Select * from ProductVariations where ProductId in ([all_product_id_set]);
• Again, this result set is parsed and all ProductMediaItem are fetched using a query similar to Select * from ProductMediaItem where ProductVariationId in ([all_product_variation_id_set]);
• So on and so forth

This would result in one SELECT for each different association. Thus, to load all product variations, their media items, and each products category links, and their respective categories would result in 5 SELECTs.

I have tried using criteria.SetFetchMode("ProductVariations", FetchMode.Select"), but this does nothing. My current workaround is to use batching, but this still results in around 50 - 60 queries to get all the data, which although not so slow, I think it could be made much faster.

I am using the latest version of NHibernate - 3.3.

Update 1

I would like to traverse the collection using normal OOP, example:

foreach (var p in Products)
{
    foreach (var variation in p.ProductVariations)
    {
        foreach (var mediaItem in variation.MediaItems)
        {
              ...
        }
    }
}

Answer 1

MultiQueries and MultiCriteria will work, or even easier would be futures, eg:

var productVariations = session.CreateQuery(
        "Select * from ProductVariations where ProductId in ([all_product_id_set])")
        .Future<ProductVariations>();

var mediaItems = session.CreateQuery(
        "select pv.MediaItems from ProductVariations pv where pv.ProductId in ([all_product_id_set])")
        .Future<ProductMediaItem>();

etc... When you enumerate any of them it will execute all the queries in 1 go, the only "trick" is to navigate down the relationship from Product to the various items you want to select