One of the most common operations in web sites that list data such as inventory items, blog posts, pictures, etc. is data paging. If you happen to be using NHibernate for you project and want a really clean way to page and sort through your items, then read on.
Problem: The other day I was trying to optimize some data paging that was using Linq on a large record set instead of paging the records at the data level. I knew that NHibernate had the methods SetMaxResults and SetFirstResult for limiting results, but what I didn’t know is how to execute all the data retrieval, including total pages count, all in on query, especially using all the filters that the List<> query was using. I knew where I wanted to get but was missing a few puzzle pieces. I can’t take all the credit for the solution below, but I will consolidate 2 great ideas into one post.*
Solution: Using NHibernate, we will utilize the following concepts/methods:
- CreateMulitCriteria() – a way to execute more than one ICriteria in one database call
- CriteriaTransformer.Clone() – clones the a given criteria object
- SetMaxResults – limits result set
- SetFirstResult – takes results from a starting location
Since paging is pretty common we can make use of Generics to alleviate repetitive coding, and Extensions to append functionality to the existing ISession. If you don’t want to use Extension, you can write a static helper class.
Define a wrapper for our paged results:
public class PagedResult<TModel>
{
public IList<TModel> Items { get; protected set; }
public long TotalItems { get; protected set; }
public PagedResult()
{
this.Items = new List<TModel>();
}
public PagedResult(IList<TModel> items, long totalItems)
{
Items = items;
TotalItems = totalItems;
}
}
Define the
Extension method to do the work.
public static class NHibernateExtensions
{
public static PagedResult<TModel> CreatePagedResult<TModel>(this ISession session, ICriteria criteria, int startIndex, int pageLength)
{
// clone the criteria for page count
ICriteria countCriteria = CriteriaTransformer.Clone(criteria)
.SetProjection(NHibernate.Criterion.Projections.RowCountInt64());
countCriteria.ClearOrders(); // clear order by
// set paging on base criteria
criteria.SetFirstResult(startIndex)
.SetMaxResults(pageLength);
// get all data in one execution
var multiCriteria = session.CreateMultiCriteria()
.Add(criteria)
.Add(countCriteria);
// execute both queries
IList results = multiCriteria.List();
var pagedResult = new PagedResult<TModel>(
((IList<TModel>)results[0]), // get paged items
(long)((IList)results[1])[0]); // get total records
return pagedResult;
}
}
First we clone the ICriteria which represents our parent query to get our list data. The parent ICriteria holds all the filter conditions, grouping, etc. that is going to be applied in determining the results. After cloning this, we apply a projection that generates the count query, and we also clear the ordering clause since it’s not needed. Next we take advantage of the paging capabilities in the ICriteria interface, SetFirstResult and SetMaxResults. This is similar to using
TSQL’s ROW_NUMBER function, or
MySql LIMIT. In order to execute the queries in one go, we use the ISession.CreateMulitCriteria() method which prepares us for the 2 query execution. Executing this will return our paged items and secondly our total records count. In the case of our total records we’re only concerned with the result in the first row hence the “((IList)results[1])[0]”.
Here is a capture from a
NHibernate Profiler. I had to remove a lot of the columns so the important pieces are noticeable, and highlighted some of the key items. Notice the top select is for the list of items, and the bottom select is for the total records. Since this is SQL server 2005+ it’s making use of the ROW_NUMBER function mentioned above.
* See: stackoverflow post’s comment by Geir-Tore Lindsve, Stefan Sedich’s Blog post
