Nhibernate QueryOver - Get Child Count for Pagination

Question

I am using Nhibernate 3.0 and need to implement paging onto a site. Basically we have a ProductCategory which has a collection of Products associated with it. So far I have this which does work

var result = Session.QueryOver<TEntity>().TransformUsing(Transformers.DistinctRootEntity)
            .Where(category => category.CategoryId == criteria.CategoryId)
            .Fetch(category => category.Products).Eager
            .Take(pageSize)
            .Skip((pageIndex - 1)*pageSize)
            .Future<TEntity>();

This returns me the category I am requesting and the child products paged correctly based on the page size and page index passed in.

What I want to do now is actually get the total row count of the products, so for example, even though I am only returning 5 products for example, I need to know that there are 100 in total.

Many Thanks

Miroslav Popovic Miroslav Popovic · Accepted Answer · 2012-07-05T12:51:04

You should do that with another query by going from the Product side and employing .RowCount().

Something like this (if your Product has a Category property):

int count = session.QueryOver<Product>()
    .Where(x => x.Category.Id == categoryId)
    .RowCount();

Here you can find several more ways to get count: How do I get row count using the NHibernate QueryOver api?

Another blog post that might be helpful to you:
NHibernate 3.0 QueryOver - Searching, and Paging at database level

Nhibernate QueryOver - Get Child Count for Pagination

1 Answers