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>Define the Extension method to do the work.
{
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;
}
}
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;
}
}
* See: stackoverflow post’s comment by Geir-Tore Lindsve, Stefan Sedich’s Blog post
1 comment:
Awesome post, that will definitely come in handy
Post a Comment