NHibernate: a general way to get the number of rows returned by a query for arbitrary criteria

Question

I know about CriteriaTransformer.TransformToRowCount, but according to the link and my experience does not work with aggregate functions (and they are used quite often), Since I am writing some kind of swap structure for my application, it would be very tedious to write a “count” request for every data request.
Any ideas how this can be optimized?

Answer 1

It's 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 is the IPagedList:

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; }
}

Answer 2

The following is a lengthy discussion of paging . This is oracle sql, and easy to follow. Essentially

• , .
• . ( 1 , , , )
• , sql.

:

• URL- , 101, : , Google 1000 . ( , 1001.)