How to load child collection after loading the parent with nhibernate?

Question

I have this model

Product
- int Id
- string Name
- List<ProductChannelAffectation> Affectations

ProductChannelAffectation
- Channel Channel
- Product Product
- double Price

So I want to get the 10 first product that match to some condition and their affectation in 2 queries (no N+1 problem) and if possible in 1 trip to the DB.

So I read this Answer

https://stackoverflow.com/a/7035649/277067 OR this one https://stackoverflow.com/a/5285739/277067

But here there is 2 problem :

The condition is duplicated "Where(x => x.Id == ID)" across the 2 queries, it's ok when it's simple condition but what if it's complicated conditions (involving other tables, text search...)
There is no "Top" condition.

Lazy loading is here causing a N+1 problem.

I tried this

var products = _nhSession.QueryOver<Product>()
    .Where(...)
    .Take(10)
    .List()
    .ToList();
var idsproduct = products.Select(p => p.ID);
var affectation = _nhSession.QueryOver<ProductChannel>()
    .Where(c => c.Product.ID.IsIn(idsproduct))
    .ToList();

But there is still N+1 (for getting the affectations) before the second query is executed.

Here is how my association is declared

  <bag name="Affectations" access="property" table="Product" lazy="false">
      <key column="n_Product" />
      <one-to-many class="CTV.ProductChannel, CTV" />
    </bag>

I'm doing my declaration with activerecord.

score 1 · Answer 1 · answered Feb 23 '12 at 16:08

1

Have you tried setting a batch size

 <bag name="Affectations" ... batch-size='50'>

This will stop your n+1 problem and allow you to keep lazy loading. Basically setting batch-size to 50 will reduce the amount of queries issued to the database by a factor of 50. Without it set if you had 99 rows you would issue 99 queries to the database with batch-size set you would issue 2.

answered Feb 23 '12 at 16:08

Rippo

22,117
14
78
117

I found it after my question indeed. But it's not behaving as you're saying look here : http://stackoverflow.com/a/6293687/277067. the documentation is wrong about it. – remi bourgarel Feb 23 '12 at 16:50
What doesn't work? `batch-size` will solve your problem, the question you point at asks "how does batch-size" work. Did you try it and look at the SQL generated? – Rippo Feb 24 '12 at 04:05
Yes I did, for instance, If I have a batch-size of 50, and I have 30 product, it'll first load 25 product's affectations and then 5 product's affectations. – remi bourgarel Feb 24 '12 at 07:01
It's not really a problem in my case, but your explanation is wrong, with a batch size of 50 and 99 product I'll get more than 2 request. – remi bourgarel Feb 24 '12 at 11:47

score 0 · Answer 2 · answered Feb 23 '12 at 16:38

0

// subquery for the ids
var idsproduct = QueryOver.Of<Product>()
    .Where(...)
    .Take(10);
var affectation = _nhSession.QueryOver<Product>()
    .WithSubquery.Where(p => p.ID).In(idsproduct))
    .Fetch(p => p.Affectations).Eager
    .ToList();

answered Feb 23 '12 at 16:38

Firo

30,626
4
55
94

How to load child collection after loading the parent with nhibernate?

2 Answers2