This post demonstrates how you can do efficient paging using ASP.NET MVC and NHibernate.
To make paging efficient we need to pass the start index and the number of records to return to the database. So that we can display page links we also need to pass some information to our view such as the number of pages available and the current page index. I found the cleanest way of doing this was to create a new class PagedList<T>.
First I defined an interface that a PagedList should implement, IPagedList:
public interface IPagedList {
int RecordCount { get; set; }
int PageIndex { get; set; }
int PageSize { get; set; }
int PageCount { get; set; }
bool HasPreviousPage { get; }
bool HasNextPage { get; }
}
Then I create my PagedList that implements this interface and inherits from List<T>:
public class PagedList<T> : List<T>, IPagedList
{
public PagedList(IList<T> source, int pageIndex, int pageSize, int recordCount) {
this.RecordCount = recordCount;
this.PageSize = pageSize;
this.PageIndex = pageIndex;
this.PageCount = recordCount / pageSize;
if (recordCount % pageSize > 0)
this.PageCount++;
this.AddRange(source);
}
public int RecordCount { get;set; }
public int PageIndex { get; set; }
public int PageSize { get; set; }
public int PageCount { get; set; }
public bool HasPreviousPage { get { return (PageIndex > 0);}}
public bool HasNextPage {get{ return (PageIndex * PageSize) <= RecordCount;}}
}
The properties are fairly self explanatory.
Next I extend our repository interface IRepository to include a new method GetPaged:
public interface IRepository<T> {
int Save(T entity);
void Delete(T entity);
T GetById(int id);
ICollection<T> GetAll();
PagedList<T> GetPaged(int pageIndex, int pageSize);
}
My PSScriptRepository (returns PSScript domain objects) has the following implementation of GetPaged:
public PagedList<PSScript> GetPaged(int pageIndex, int pageSize)
{
using (ISession session = NHibernateHelper.OpenSession()) {
var rowCount = session.CreateCriteria<PSScript>()
.SetProjection(Projections.RowCount())
.FutureValue<Int32>();
var results = session.CreateCriteria<PSScript>()
.SetFirstResult((pageIndex - 1) * pageSize)
.SetMaxResults(pageSize)
.Future<PSScript>()
.ToList<PSScript>();
return new PagedList<PSScript>(results, pageSize, pageSize, rowCount.Value);
}
}
Note the use of Future<T> and FutureValue<T>. These functions allow for deferred execution and means that instead of hitting the database twice (once for the count, once for our records) the queries are combined and we only go to the database once. Bloody clever stuff!
Read more: my great discovery