/lambλda/calculus/

When you write queries that use joins in NHibernate, most of the time the result you get back is not unique at the root entity level. This is usually not a problem since you can stick a DistinctRootEntityResultTransformer on the query and get a nice unique result set.

However, if you want to page the result based on the root entities there is a problem. Since the DistinctRootEntityResultTransformer operates on the result set that comes back from the database and the paging modifies the database query the paging is made before the data is filtered to be unique. This leads to a result that you probably don\'t want.

To combine paging with joined queries you can split the query into two separate queries. The first query fetches the root entities based on some criteria and pages the result. The second query performs the join for the entities returned from the previous query, without caring about paging or uniqueness. Since NHibernate makes sure an entity is mapped to exactly one instance in a session the result from the second query loads data into the instances returned from the first query.

Here is an example:

// Assuming two classes: Post and Comment. A post has many comments.
public IEnumerable<Post> GetPostsByPageWithCommentsLoaded(int pageIndex, int pageSize)
{
    using (ISession session = SessionFactory.OpenSession())
   {
      // Load the posts using paging
      var posts = session.CreateCriteria(typeof(Post))
         .AddOrder(Order.Desc("DateTime"))
         .SetFirstResult(pageIndex * pageSize)
         .SetMaxResult(pageSize)
         .List<Post>();
     // Eager load the comments for all the posts returned in the previous query
     // Restrictions.In() is used to avoid fetching unnecessary data
    // temporaryPosts is never used. The point of the query is to load data into
    // the instances returned from the previous query
     var temporaryPosts = session.CreateCriteria(typeof(Post))
         .SetFetchMode("Comments", FetchMode.Eager)
         .Add(Restrictions.In("Id", posts.Select(p => p.Id).ToArray()))
         .List<Post>();
     return posts;
   }
}