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, we'll show you how to easily add paging to a QueryOver query object in our DAL.
- In
Eg.Core.Data.Queries, add a class using the following code:public class PagedResult<T> { public int TotalCount { get; set; } public IEnumerable<T> Items { get; set; } } - Add an interface using the following code:
public interface IPagedQuery<T> : IQuery<PagedResult<T>> { int PageNumber { get; set; } int PageSize { get; set; } } - In
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 PageSize { get; set; } public override PagedResult<T> Execute() { var query = GetQuery(); SetPaging(query); return WithinTransaction(() => Execute(query)); } protected abstract IQueryOver<T, T> GetQuery(); protected virtual void SetPaging( IQueryOver<T, T> query) { int maxResults = PageSize; int firstResult = (PageNumber - 1) * PageSize; 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>() { Items = results, TotalCount = count.Value }; } } - In
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; } string OrderBy { get; set; } SortOrderDirection SortOrder { get; set; } } - Add the following enumeration for choosing the sort option:
public enum SortOrderDirection { Ascending, Descending } - In
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 SortOrderDirection SortDirection { 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); if (!string.IsNullOrEmpty(OrderBy)) { var order = Property.ForName(OrderBy)); switch (SortDirection) { case SortOrderDirection.Descending: query = query.OrderBy(order).Desc; break; case SortOrderDirection.Ascending: query = query.OrderBy(order).Asc; break; } } else { query = query.OrderBy(p => p.UnitPrice).Asc; } return query; } }
In this recipe, we have defined a common PagedResult<T> return type for all paged queries. We have also defined the IPagedQuery<T> interface, which specifies the paging parameters and 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. Future queries are executed when the count query result is put into the PagedResult<T> object.