NHibernate: generic way of getting number of lines returned by arbitrary criteria query

Question

I know about CriteriaTransformer.TransformToRowCount but according to link and my experience it doesn't work with aggregate functions (and they used quite often). Since I'm writing kinda paging framework for my app it would be very tedious to write "count" query for every data query.
Any ideas how this can be optimized?

Answer 1

Here is a long discussion on paging. It's oracle sql, and easy to follow. Essentially,

• Don't waste cycles computing something no one will ever see.
• Just display a next and previous link. (Select 1 more than you will display, to determine if you should hide the next button)
• If you want a last button, reverse the order-by in your sql.

For comparison:

• Adjust google's search url to get page 101, and they will tell you: Sorry, Google does not serve more than 1000 results for any query. (You asked for results starting from 1001.)

Answer 2

I believe this article may offer some insights that you could adapt for this purpose.

Answer 3

This is not hard:

private IPagedList<ProjT> FindPaged<ProjT>(DetachedCriteria criteria, int pageIndex, int pageSize, IResultTransformer resultTransformer)
{
    int firstResult = pageIndex == 1 ? 0 : (pageIndex - 1) * pageSize;

    var countCriteria = CriteriaTransformer
        .Clone(criteria)
        .SetProjection(Projections.RowCount());

    countCriteria.ClearOrders();

    IMultiCriteria multiCriteria = Session.CreateMultiCriteria();
    multiCriteria.Add(countCriteria);

    criteria.SetFirstResult(firstResult).SetMaxResults(pageSize);

    if (resultTransformer != null)
    {
        criteria.SetResultTransformer(resultTransformer);
    }

    multiCriteria.Add(criteria);

    var result = multiCriteria
        .List()
        .Cast<System.Collections.ArrayList>()
        .ToList();

    PagedList<ProjT> list = new PagedList<ProjT>(
        result[1].Cast<ProjT>().ToList<ProjT>(),
        pageIndex,
        pageSize,
        (int)result[0][0]);

    return list;
}

Where IPagedList is:

public interface IPagedList<T> : ICollection<T>
{
    int TotalPages { get; }
    int TotalCount { get; }
    int PageIndex { get; }
    int PageSize { get; }
    bool HasPreviousPage { get; }
    bool HasNextPage { get; }
    bool IsFirstPage { get; }
    bool IsLastPage { get; }
}

and implementation:

public class PagedList<T> : List<T>, IPagedList<T>
{
    public PagedList(IEnumerable<T> source, int pageIndex, int pageSize) :
        this(source, pageIndex, pageSize, source.Count())
    { }

    public PagedList(IEnumerable<T> source, int pageIndex, int pageSize, int totalCount)
    {
        this.TotalCount = totalCount;
        this.PageSize = pageSize;
        this.PageIndex = pageIndex;

        double pc = this.TotalCount / this.PageSize;
        if (this.TotalCount % this.PageSize > 0)
        {
            pc++;
        }
        this.TotalPages = (int)pc;

        this.HasPreviousPage = (PageIndex > 1);
        this.HasNextPage = (PageIndex * PageSize) < TotalCount;
        this.IsFirstPage = (this.PageIndex == 1);
        this.IsLastPage = (this.PageIndex == this.TotalPages);

        this.AddRange(source);
    }

    public int TotalPages { get; private set; }
    public int TotalCount { get; private set; }
    public int PageIndex { get; private set; }
    public int PageSize { get; private set; }
    public bool HasPreviousPage { get; private set; }
    public bool HasNextPage { get; private set; }
    public bool IsFirstPage { get; private set; }
    public bool IsLastPage { get; private set; }
}