Paging by root entity with eager fetch of a child collection

[mnbeer]

Perhaps this is ridiculously obvious, but how does one eagerly load a
child collection in NHibernate, while paging based on the parent (the
distinct root)? NHibernate’s paging arguments (skip, take) seem to be
passed through to the joined records, which makes perfect sense but
doesn’t really provide an object level paging option. I’m not
interested in projecting into a flattened object.

So for example, let’s say that I want to select 20 teachers at a time
but want their classes eagerly loaded (which for the sake of argument
may result in 57 generated db rows on page one and 63 generated db
rows on page 2).

[Joe Sadowski]

That is a difficult situation. We've been solving it using QueryOver
with a subquery like this:

// this will hold the criterial for the query
var subQuery = QueryOver.Of<Parent>();

// we build the criteria here...

// add paging id projection so we only get one page of ids
var pageSubQuery = subQuery.Clone()
.OrderBy(p => p.Id).Asc
.Select(p => p.Id)
.Skip(index ?? 0)
.Take(itemsPerPage ?? 20)
;

var items = session.QueryOver<Parent>()
.WithSubquery.WhereProperty(p => p.Id).In(pageSubQuery)
.OrderBy(p => p.Id).Asc // important to have the
same order as above!
.Fetch(p => p.Children).Eager
.TransformUsing(new DistinctRootEntityResultTransformer())
.Future()
;

// Count
var count = subQuery.GetExecutableQueryOver(session)
.Select(Projections.CountDistinct<Parent>(p => p.Id))
.FutureValue<int>()
;

By applying the paging on the subquery it gives us a list of the ids
of the parents on the first page. We then use this as the source for a
"where Id in (criteria)" statement that we know will only return the
right amount of parent records and we can fetch the children without
needing additional criteria on the outer query.

It works for us, but there might be a better way...

note: We use future values so that we get one trip to the database
that gives us the total count of parents matching the criteria and one
page of parents with their children.

-joe

> --
> You received this message because you are subscribed to the Google Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
>
>

[Jason Meckley]

or you can configure the child elements to be selected in batch
<set name=... batchSize="50">
...
</set>
session.queryover<parent>().where(...).skip(x).take(4).future<parent>().
then let the children lazy load in batches of 50 parents.
this is also a good option because you are not loading Cartesian record sets, you avoid the select n+1, and the query is relatively simple.

[mnbeer]

Both answers were very helpful. Thanks to you both.