NHibernate lazy loading nested collections with futures to avoid N+1 problem

Question

I have an object model that looks like this (pseudo code):

class Product {
    public ISet<Product> Recommendations {get; set;}
    public ISet<Product> Recommenders {get; set;}
    public ISet<Image> Images {get; set; }
}

When I load a given product and want to display the images of its recommendations, I run into an N+1 problem. (The recommendations are lazy-loaded, then a loop calls the .Images property of each one.)

Product -> Recommendations -> Images

What I want to do is eagerly load this particular part of the graph, but I can't figure out how to do it. I can load the recommendations eagerly, but not their images. This is what I have been trying, but it doesn't seem to work:

//get the IDs of the products that will be in the recommendations collection
var recommendedIDs = QueryOver.Of<Product>()
    .Inner.JoinQueryOver<Product>(p => p.Recommenders)
    .Where(r => r.Id == ID /*product we are currently loading*/)
    .Select(p => p.Id);

//products that are in the recommendations collection should load their 
//images eagerly
CurrentSession.QueryOver<Product>()
    .Fetch(p => p.Images).Eager
    .Where(Subqueries.WhereProperty<Product>(p => p.Id).In(recommendedIDs))
    .Future<Product>();

//load the current product
return CurrentSession.QueryOver<Product>()
    .Where(p => p.Id == ID);

Using QueryOver, what is the best way to accomplish this? I don't want to eagerly load images all the time, just in this particular scenario.

EDIT: I have changed my approach, and while it's not exactly what I had in mind, it does avoid the N+1 problem. I am now using two queries, one for the product, and one for the images of it's recommendations. The product query is straight-forward; here is the image query:

//get the recommended product IDs; these will be used in
//a subquery for the images
var recommendedIDs = QueryOver.Of<Product>()
    .Inner.JoinQueryOver<Product>(p => p.Recommenders)
    .Where(r => r.Id == RecommendingProductID)
    .Select(p => p.Id);

//get the logo images for the recommended products and
//create a flattened object for the data
var recommendations = CurrentSession.QueryOver<Image>()
    .Fetch(i => i.Product).Eager
    /* filter the images down to only logos */
    .Where(i => i.Kind == ImageKind.Logo)
    .JoinQueryOver(i => i.Product)
    /* filter the products down to only recommendations */
    .Where(Subqueries.WhereProperty<Product>(p => p.Id).In(recommendedIDs))
    .List().Select(i => new ProductRecommendation {
        Description = i.Product.Description,
        ID = i.Product.Id,
        Name = i.Product.Name,
        ThumbnailPath = i.ThumbnailFile
    }).ToList();

return recommendations;

score 17 · Accepted Answer · answered Aug 12 '11 at 04:40

JoinAlias is another way to eagerly fetch related records, plus we can use it to dig another level deeper through Recommendations down to Images. We'll use LeftOuterJoin because we want to load the product even if it has no recommendations.

Product recommendationAlias = null;
Image imageAlias = null;

return CurrentSession.QueryOver<Product>()
    .JoinAlias(x => x.Recommendations, () => recommendationAlias, JoinType.LeftOuterJoin)
    .JoinAlias(() => recommendationAlias.Images, () => imageAlias, JoinType.LeftOuterJoin)
    .Where(x => x.Id == ID)
    .TransformUsing(Transformers.DistinctRootEntity)
    .SingleOrDefault();

When discussing eager fetching of multiple collections with NHibernate, you often hear people mention Cartesian products, but that's not a concern here. If however, you wished to load the following graph instead...

 Product -> Recommendations -> Images
         -> Images

... then Product.Recommendations.Images X Product.Images would form a Cartesian product that we should avoid. We could do so like this:

Product recommendationAlias = null;
Image imageAlias = null;

var productFuture = CurrentSession.QueryOver<Product>()
    .JoinAlias(x => x.Recommendations, () => recommendationAlias, JoinType.LeftOuterJoin)
    .JoinAlias(() => recommendationAlias.Images, () => imageAlias, JoinType.LeftOuterJoin)
    .Where(x => x.Id == ID)
    .TransformUsing(Transformers.DistinctRootEntity)
    .FutureValue();

var imagesFuture = CurrentSession.QueryOver<Product>()
    .Fetch(x => x.Images).Eager
    .Where(x => x.Id == ID)
    .TransformUsing(Transformers.DistinctRootEntity)
    .Future();

return productFuture.Value;

score 3 · Answer 2 · answered Apr 11 '11 at 16:17

3

Force an eager load on the part of the graph you care about using the NHibernateUtil class.

 NHibernateUtil.Initialize(Product.Recommendations);

See the link below for further details.

http://nhforge.org/wikis/howtonh/lazy-loading-eager-loading.aspx

answered Apr 11 '11 at 16:17

Newbie

7,031
9
60
85

Frédéric · Answer 3 · 2018-12-12T14:39:54.117

If all you want is avoiding the N+1 trouble, use Batch fetching of lazy loads instead of eager loading.

It removes N+1 issues while having a minimal impact on code: you just have to change a configuration parameter or adjust the mappings.

In configuration, set default_batch_fetch_size to some sensible value for your usual lazy-loads counts. 20 is usually a good value.

Or in mappings, set the batch-size attributes on classes (<class>) and collections (<set>, <bag>, ...) for controlling case by case the lazy-load batching.

This will configure your lazily loaded entities and collections of entities to not only load themselves, but also some others awaiting entities (of the same class) or collections of entities (same collections of other entities of the same class).

I have written a detailed explanation of it in this other answer.

NHibernate lazy loading nested collections with futures to avoid N+1 problem

3 Answers3

Linked