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

score 5 · Accepted Answer · edited May 23 '17 at 11:44

5

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

edited May 23 '17 at 11:44

Community

1
1

answered Jul 05 '12 at 12:51

Miroslav Popovic

12,100
2
35
47

Thanks Miroslav, that has done the trick, I will use the FutureValue method, so I only have 1 round trip to the db. – MikeL Jul 05 '12 at 13:34

Nhibernate QueryOver - Get Child Count for Pagination

1 Answers1