NHibernate. QueryOver Take(n) - with Left.Join

Question

Got some strange behaviour when using Take() with join. Suppose the following example:

Comment comment = null;
var persons = _repository
        .QueryOver()
        .Left.JoinAlias(x => x.Comments, () => comment)
        .Where(x => x.Age > 20)
        .Take(5)
        .Future()
        .ToList();

Well I'd expect that 5 persons are present in the array, and each of them has a list of N comments.

But, the result givest 5 persons, with a maximum of 5 comments.

Why is .Take(5) also restricting the number of comments?

How to achieve the desired result?

This is a classic cartesian product problem. NH doesn't page the root BUT the root + children. Like Radim has answered fetch parents with paging and lazy load with batching the children — Rippo, May 18 '15 at 06:44

score 2 · Accepted Answer · edited May 23 '17 at 11:51

The point here is a difference in our need of paging and its implementation.

while we would expect 5 root elements to be returned,
the result is converted into 5 rows, 5 selected rows :

Some clue could be found in this Q & A: NHibernate QueryOver with Fetch resulting multiple sql queries and db hits

In case of paging with SQL Server 2012 dialect, we would see SQL like this:

SELECT ...
FROM root
JOIN collection
WHERE....
OFFSET 0 ROWS -- skip 0 or 10 or 20 rows
FETCH NEXT 5 ROWS ONLY; -- take 5 rows

So it could at the end result in returning just ONE root entity, if amount of chidren (comments) is 5+

Solution? I would suggest to do it by:

select just root entity
use batch fetching to load children

Selecting just root, could eve mean select also any many-to-one/Reference. This will end up in a star schema structure, which with left joins will again correctly page over the root entity.

Batch fetching is described here in doc:

19.1.5. Using batch fetching

Some similar issues:

Thank you for a detailed answer. Appreciate your help++ – Cristian E. May 18 '15 at 09:00 — Cristian E., May 18 '15 at 09:00
Great to see that, sir! Enjoy awesome NHibernate ;) – Radim Köhler May 18 '15 at 09:01 — Radim Köhler, May 18 '15 at 09:01

NHibernate. QueryOver Take(n) - with Left.Join

1 Answers1

19.1.5. Using batch fetching