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