Programming Languages Hacks

Importanti regole per linguaggi di programmazione rilevanti come Java, C, C++, C#…

  • Subscribe

  • Lettori

    I miei lettori abituali

  • Twitter

NHibernate – Paging by root entity with eager join fetch of a child collection

Posted by Ricibald on July 15th, 2012

With nhibernate we can fetch entities using the fetch strategies explained in the link.

Now we consider only the use of fetching by join.

Scenario: I want to fetch by join the first 5 students with all the exams eagerly loaded.

If I write the QueryOver I end not with 5 results but with 100 results, almost all of these duplicated!

Why does this happen? The response is: count the rows! If you execute in SQL Server the query you ends with 5 results combinated with the corresponding exams. If every student has 20 exam, you ends with 100 students!

A simple solution: change fetch strategy to select. But if you want join fetch to overcame to N+1 problem you should use the following fixes:

You can merge duplicates by setting a DistinctRootEntityResultTransformer in the QueryOver, but merging them means that you obtain just the merged result of the first 5 rows: a single student!

To obtain all the students you should first create a query with none eagerly loaded, and then use this query as subquery to obtain the remaining references to eagerly loaded.

You can follow this example:

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

Other solutions from the official post in nhforge.

Leave a Reply

You must be logged in to post a comment.