A Re-Usable NHibernate Paging Extension Method

Every developer knows that they should always try to avoid returning an unbounded result set from the database, right?  Of course they do.  The obvious solution to this problem is to page through the results in smaller chunks that the database (and most likely the end-user) can handle efficiently.  I didn’t want to have to reinvent the wheel for every query I wrote so I wrapped the necessary NHibernate logic up in a nice, tidy extension method.

Side note: NHibernate Profiler is an indispensible tool for optimizing your NHibernate code.  I couldn’t live without it, nor would I want to.  And no I don’t receive kickbacks from Ayende.

Of course, in order to make this truly useful, I needed a custom collection that could keep track of the various paging settings like the current page index, the total number of records in the database, how many records are returned for each page, etc.  For this, I blatantly stole borrowed the PagedList<T> code from Rob Conery and ScottGu.  I also later found a great project on github by Troy Goode that is basically the same but with some supporting unit tests and optimizations.  Either way, here is teh codez that I use.

PagedList.cs – contains an interface and a default implementation of a generic PagedList<T>.  It also has a helper extension method so you can quickly turn any IEnumberable<T> into a PagedList<T>.

PagedList.cs

1. public interface IPagedList
2. {
3.     long TotalCount { get; set; }
4.     int TotalPages { get; set; }
5.     int PageIndex { get; set; }
6.     int PageSize { get; set; }
7.     int FirstItem { get; }
8.     int LastItem { get; }
9.     bool HasPreviousPage { get; }
10.     bool HasNextPage { get; }
11. }
12.  
13. [Serializable]
14. public class PagedList<T> : IPagedList
15. {
16.     public List<T> Items { get; private set; }
17.     public int TotalPages { get; set; }
18.     public long TotalCount { get; set; }
19.     public int PageIndex { get; set; }
20.     public int PageSize { get; set; }
21.  
22.     public PagedList()
23.     {
24.         Items = new List<T>();
25.     }
26.  
27.     public PagedList(IEnumerable<T> source, long totalCount, int pageIndex, int pageSize)
28.     {
29.         PageSize = pageSize;
30.         PageIndex = pageIndex;
31.         TotalCount = totalCount;
32.         TotalPages = (int)(TotalCount / pageSize);
33.  
34.         if (TotalCount % pageSize > 0)
35.             TotalPages++;
36.  
37.         Items = new List<T>(source);
38.     }
39.  
40.     public bool HasPreviousPage
41.     {
42.         get { return (PageIndex > 0); }
43.     }
44.  
45.     public bool HasNextPage
46.     {
47.         get { return PageIndex < TotalPages - 1; }
48.     }
49.  
50.     public int FirstItem
51.     {
52.         get
53.         {
54.             return (PageIndex * PageSize) + 1;
55.         }
56.     }
57.  
58.     public int LastItem
59.     {
60.         get { return FirstItem + PageSize - 1; }
61.     }
62. }
63.  
64. public static class Pagination
65. {
66.     public static PagedList<T> ToPagedList<T>(this IEnumerable<T> source, int totalCount, int pageIndex, int pageSize)
67.     {
68.         return new PagedList<T>(source, totalCount, pageIndex, pageSize);
69.     }
70. }

NHibernateExtensions.cs – this is where the NHibernate goodness comes in.  This warrants a little explanation but take a look first and see if you can see what’s going on.

NHibernateExtensions.cs

1. public static class NHibernateExtensions
2. {
3.     public static PagedList<T> PagedList<T>(this ICriteria criteria, ISession session, int pageIndex, int pageSize) where T: class
4.     {
5.         if (pageIndex < 0)
6.             pageIndex = 0;
7.  
8.         var countCrit = (ICriteria)criteria.Clone();
9.         countCrit.ClearOrders(); // so we don't have missing group by exceptions
10.  
11.         var results = session.CreateMultiCriteria()
12.             .Add<long>(countCrit.SetProjection(Projections.RowCountInt64()))
13.             .Add<T>(criteria.SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize))
14.             .List();
15.  
16.         var totalCount = ((IList<long>)results[0])[0];
17.  
18.         return new PagedList<T>((IList<T>)results[1], totalCount, pageIndex, pageSize);
19.     }
20. }

First of all, this method is an extension of NHibernate’s ICriteria class so it obviously requires you to be using NHibernate’s Criteria API.  This type of thing would be much more difficult using the HQL syntax.

Essentially what we want to do is leverage NHibernate’s multi-criteria feature to send two different SQL statements to the server at the same time.  One to retrieve the total record count of the query and two to get the actual results of that query, limited to the page size and page index that we specify.  That’s why we Clone the original criteria that is passed into the method.  This will be what is used to get the total record count for the query.  We call the ClearOrders method on this instance of the ICriteria because: 1) we don’t care about how the query is ordered if we’re just getting the total row count; and 2) the order statements can cause problems if there are groupings in the criteria. Trust me on this.

We then create the multi-criteria object on line 11 and add the total row count criteria that we cloned and the original criteria passed in but we use the SetFirstResult and SetMaxResults methods to limit the number of records returned.

Finally, we retrieve the values from the two result sets.  The first being the total row count.  The second, the actual results of the query and return the results using our fancy new PagedList<T> class.

The usage of this extension method couldn’t be simpler.

Code Snippet

1. public PagedList<Order> GetCompletedOrders(int pageIndex, int pageSize)
2. {
3.     // _currentSession is an instance of ISession, instantiation not shown
4.     return _currentSession.CreateCriteria<Order>()
5.         .Add(Expression.Eq("Status", (int)OrderStatus.Complete))
6.         .AddOrder(Order.Desc("OrderCompletedDate"))
7.         .PagedList<Order>(_currentSession, pageIndex, pageSize);
8. }

As a bonus, I’m going to post some code that I use in my ASP.NET MVC projects that allows you generate a Pager control complete with customizable, CSS classes, next/previous text customizations, AJAX support, etc. from our PagedList<T> class in ONE LINE OF CODE.  Stay tuned…

Update: here’s the post on those ASP.NET MVC Pager helper classes I mentioned.