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
On Sun, Oct 23, 2011 at 9:00 AM, mnbeer <[email protected]> wrote:
> 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).
>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
