Paging with NHibernate using a custom Extension method to make it 'easier' :)...
Update 20081022:
I have updated the articles code to reflect the bug fixes kindly suggested by Paco :). I have now used this in my project and have had no issues with it yet.
The fixes included clearing order by's on the count query and also returning the count as an Int64.
Cheers
Stefan
------------------
Evening All,
What better thing to do on a friday night than code and blog :P. Thought it was about time I shared my custom paging helper methods for NHibernate. I quite liked this solution as it worked nicely and was easy to use. I will go into as much detail as I can here without putting you to sleep.
The Idea
Basically my idea was to replicate something like in Linq to SQL, where you can basically define a query and call Skip(x).Take(x), in the end I came up with the idea of calling an ToPagedResult(index, pageSize); extension method, this would then return a PagedResult<T> object, the PageResult object would basically just be a container which would hold the total results and the total item count. Simple really, reason for this is just to make paging results a little easier and reduce code waste by wrapping my common functionality in my extension method.
The Solution
The solution will need 2 things, first it will need my PagedResult<T> class, and then the ToPagedResult Extension method. Firstly the PagedResult class:
/// <summary>
/// A paged result set, will have the items in the page of data
/// and a total item count for the total number of results.
/// </summary>
public class PagedResult<TEntity> {
#region Properties
/// <summary>
/// The items for the current page.
/// </summary>
public IList<TEntity> Items { get; protected set; }
/// <summary>
/// Gets the total count of items.
/// </summary>
public long TotalItems { get; set; }
#endregion
#region Constructor
/// <summary>
/// Initialise an instance of the paged result,
/// intiailise the internal collection.
/// </summary>
public PagedResult() {
this.Items = new List<TEntity>();
}
/// <summary>
/// Initialise our page result, set the items and the current page + total count
/// </summary>
/// <param name="items"></param>
/// <param name="totalItems"></param>
public PagedResult(IList<TEntity> items, long totalItems) {
Items = items;
TotalItems = totalItems;
}
#endregion
}
This is a simple class really just holds the page of items and the total item count, only reason for this is just a neat way to return the results. In another version of this class I have added properties for the page index, total pages properties too but in this example I kept it simple and just added what is needed.
Next up is the extension method, basically I have in my project an NHibernateExtensions class which holds all my common extension methods, but for the example I am only including the ToPagedResult extension, the code for this is below:
public static class NHibernateExtensions {
/// <summary>
/// Based on the ICriteria will return a paged result set, will create two copies
/// of the query 1 will be used to select the total count of items, the other
/// used to select the page of data.
///
/// The results will be wraped in a PagedResult object which will contain
/// the items and total item count.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="criteria"></param>
/// <param name="startIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static PagedResult<TEntity> ToPagedResult<TEntity>(this ICriteria criteria, int startIndex, int pageSize) {
// Clone a copy of the criteria, setting a projection
// to get the row count, this will get the total number of
// items in the query using a select count(*)
ICriteria countCriteria = CriteriaTransformer.Clone(criteria)
.SetProjection(Projections.RowCountInt64());
// Clear the ordering of the results
countCriteria.Orders.Clear();
// Clone a copy fo the criteria to get the page of data,
// setting max and first result, this will get the page of data.s
ICriteria pageCriteria = CriteriaTransformer.Clone(criteria)
.SetMaxResults(pageSize)
.SetFirstResult(startIndex);
// Create a new pagedresult object and populate it, use the paged query
// to get the items, and the count query to get the total item count.
var pagedResult = new PagedResult<TEntity>(pageCriteria.List<TEntity>(),
(long)countCriteria.UniqueResult());
// Return the result.
return pagedResult;
}
}
The extension method works as follows, it is an extension on the ICriteria, could be made to work with DetachedCriteria too but in my case I only need it for ICriteria, based on the query it will make 2 copies using the CriteriaTransformer.Clone method, one query will be used to get the total item count, so we set a count projection on it.
The second copy is used to get the actual page of data, it uses NHibernates SetMaxResults and SetFirstResult methods to do this, then finally we create a new instance of our PagedResult container setting the items and item count using the two queries. Finally returning the paged result.
Usage Example
To use this extension method we first define an ICriteria query and then call the extension method to get the data, and example on out People table would be something like so, the search is just getting a list of people with age > 20. The page to get will be 0, i.e. the first page and there will be 10 items per page.
// Create the criteria to get people with age > 20
ICriteria criteria = this.session.CreateCriteria(typeof (Person))
.Add(Restrictions.Gt("Age", 20));
// Get the paged result using the above criteria and our new extension method.
PagedResult<Person> pagedResult = criteria.ToPagedResult<Person>(0, 10);
Finish
Hope you might find this useful in some of your projects, although it it something very simple it has saved me a great deal of time with paging in my project and means writing less code which is always a bonus. Send me any comments/suggestions you wish I am always open to criticism :).
Cheers
Stefan