CODEWITHSUNDEEP
c#nhibernate
remi bourgarel
remi bourgarel

Reputation: 9389

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

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 :

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.

Upvotes: 0

Views: 455

Answers (2)

Firo
Firo

Reputation: 30813

// 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();

Upvotes: 0

Rippo
Rippo

Reputation: 22424

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.

Upvotes: 1

Related Questions