In an effort to avoid overwhelming the user, and increase application responsiveness, large result sets are commonly broken into smaller pages of results. In this recipe, I'll show you how we can easily add paging to a QueryOver query object in our DAL.
Eg.Core.Data.Queries
, add a class using the following code:public class PagedResult<T> { public int TotalItems { get; set; } public IEnumerable<T> PageOfResults { get; set; } }
public interface IPagedQuery<T> : IQuery<PagedResult<T>> { int PageNumber { get; set; } int ItemsPerPage { get; set; } }
Eg.Core.Data.Impl.Queries
, add the following class:public abstract class PagedQueryOverBase<T> : NHibernateQueryBase<PagedResult<T>>, IPagedQuery<T> { public PagedQueryOverBase(ISessionFactory sessionFactory) : base(sessionFactory) { } public int PageNumber { get; set; } public int ItemsPerPage { get; set; } public override PagedResult<T> Execute() { var query = GetQuery(); SetPaging(query); return Transact(() => Execute(query)); } protected abstract IQueryOver<T, T> GetQuery(); protected virtual void SetPaging( IQueryOver<T, T> query) { int maxResults = ItemsPerPage; int firstResult = (PageNumber - 1) * ItemsPerPage; query.Skip(firstResult).Take(maxResults); } protected virtual PagedResult<T> Execute( IQueryOver<T, T> query) { var results = query.Future<T>(); var count = query.ToRowCountQuery().FutureValue<int>(); return new PagedResult<T>() { PageOfResults = results, TotalItems = count.Value }; } }
Eg.Core.Data.Queries
, add an interface for the example query:public interface IPagedProductSearch : IPagedQuery<Product> { string Name { get; set; } string Description { get; set; } decimal? MinimumPrice { get; set; } decimal? MaximumPrice { get; set; } PagedProductSearchSort Sort { get; set; } }
public enum PagedProductSearchSort { PriceAsc, PriceDesc, Name }
Eg.Core.Data.Impl.Queries
, implement the interface using the following class:public class PagedProductSearch : PagedQueryOverBase<Product>, IPagedProductSearch { public PagedProductSearch(ISessionFactory sessionFactory) : base(sessionFactory) { } public string Name { get; set; } public string Description { get; set; } public decimal? MinimumPrice { get; set; } public decimal? MaximumPrice { get; set; } public PagedProductSearchSort Sort { get; set; } protected override IQueryOver<Product, Product> GetQuery() { var query = session.QueryOver<Product>(); if (!string.IsNullOrEmpty(Name)) query = query.WhereRestrictionOn(p => p.Name) .IsInsensitiveLike(Name, MatchMode.Anywhere); if (!string.IsNullOrEmpty(Description)) query.WhereRestrictionOn(p => p.Description) .IsInsensitiveLike(Description, MatchMode.Anywhere); if (MinimumPrice.HasValue) query.Where(p => p.UnitPrice >= MinimumPrice); if (MaximumPrice.HasValue) query.Where(p => p.UnitPrice <= MaximumPrice); switch (Sort) { case PagedProductSearchSort.PriceDesc: query = query.OrderBy(p => p.UnitPrice).Desc; break; case PagedProductSearchSort.Name: query = query.OrderBy(p => p.Name).Asc; break; default: query = query.OrderBy(p => p.UnitPrice).Asc; break; } return query; } }
In this recipe, we've defined a common PagedResult<T>
return type for all paged queries. We've also defined the IPagedQuery<T>
interface, which specifies the paging parameters and a return type of PagedResult<T>
.
As defined in PagedQueryOverBase
, each subclassed query object must return a standard IQueryOver<T, T>
query from GetQuery()
. The PagedQueryOverBase
class sets the appropriate Skip
and Take
values based on the specified page number and items per page. Then it uses futures to get the results. The row count query is created from the result set query using the new ToRowCountQuery()
method. The future queries are executed when the count query result is put into the PagedResult<T>
object.