A while ago I posted about how we can make use of NHibernate future queries to perform efficient paging of data.
One problem with this approach is that we were using the Criteria API to define our queries, which prevented us from providing a “generic” paging method.
Since NH 3.0 we have been able to use the Futures feature on Linq queries. My original PagedList class used the Count() method to get the total number of records. Unfortunately, Count() executes immediately so we end up generating two queries.
Fortunately Diego has provided a handy extension method that supports future count queries:
public static class NHibernateExtensions {
public static IFutureValue<TResult> ToFutureValue<TSource, TResult>(
this IQueryable<TSource> source,
Expression<Func<IQueryable<TSource>, TResult>> selector)
where TResult : struct {
var provider = (NhQueryProvider)source.Provider;
var method = ((MethodCallExpression)selector.Body).Method;
var expression = Expression.Call(null, method, source.Expression);
return (IFutureValue<TResult>)provider.ExecuteFuture(expression);
}
}
Since we need to apply ToFuture() after Skip() and Take() I had to change my PagedList class to support passing in a query that has already been paged and the total.
The updated class is as follows:
public interface IPagedList<T> : IList<T> {
int PageIndex { get; }
int PageSize { get; }
int TotalCount { get; }
int TotalPages { get; }
bool HasPreviousPage { get; }
bool HasNextPage { get; }
}
public class PagedList<T> : List<T>, IPagedList<T> {
public PagedList(IEnumerable<T> source, int pageIndex, int pageSize) :
this(source.GetPage(pageIndex, pageSize), pageIndex, pageSize, x => x.Count()) {}
public PagedList(IEnumerable<T> source, int pageIndex, int pageSize, Func<IEnumerable<T>, int> totalFunc) {
var totalCount = totalFunc(source);
this.TotalCount = totalCount;
this.TotalPages = totalCount / pageSize;
if (totalCount % pageSize > 0)
TotalPages++;
this.PageSize = pageSize;
this.PageIndex = pageIndex;
this.AddRange(source.ToList());
}
public int PageIndex { get; private set; }
public int PageSize { get; private set; }
public int TotalCount { get; private set; }
public int TotalPages { get; private set; }
public bool HasPreviousPage { get { return (PageIndex > 0); } }
public bool HasNextPage { get { return (PageIndex + 1 < TotalPages); } }
}
public static class PageListExtensions {
public static IEnumerable<T> GetPage<T>(this IEnumerable<T> source, int pageIndex, int pageSize) {
return source.Skip(pageIndex * pageSize)
.Take(pageSize);
}
}
I then created a single method that accepts any IQueryable<T> and returns a PagedList<T>:
protected virtual IPagedList<T> FetchPaged(IQueryable<T> query, int pageIndex, int pageSize) {
var futureCount = query.ToFutureValue(x => x.Count());
return new PagedList<T>(query.Skip(pageIndex * pageSize).Take(pageSize).ToFuture(),
pageIndex, pageSize, x => futureCount.Value);
}
Finally a test to verify it works as expected:
[Test]
public void Can_get_paged_list_with_criteria() {
for (var x = 0; x < 100; x++) {
repository.Create(new TestEntity { Name = (x % 2 == 0) ? "Even" : "Odd" });
}
var entities = repository.FetchPaged(t => t.Name == "Even", o => o.Asc(t => t.Name), 3, 10);
entities.TotalCount.ShouldEqual(50);
entities.TotalPages.ShouldEqual(5);
entities.Count.ShouldEqual(10);
}
Generates:
NHibernate: select cast(count(*) as INTEGER) as col_0_0_ from TestEntities testentity0_ where testentity0_.Name=@p0;
select testentity0_.Id as Id0_, testentity0_.Name as Name0_ from TestEntities testentity0_ where testentity0_.Name=@p1 order by testentity0_.Name asc limit 10 offset 30;
;@p0 = 'Even' [Type: String (0)], @p1 = 'Even' [Type: String (0)]
Subscribe