multiple results in a single call

Question

When paging data, I want to not only return 10 results, but I also want to get the total number of items in all the pages.

How can I get the total count AND the results for the page in a single call?

My paged method is:

public IList GetByCategoryId(int categoryId, int firstResult, int maxResults) {

        IList<Article> articles = Session.CreateQuery(
            "select a from Article as a join a.Categories c where c.ID = :ID")
            .SetInt32("ID", categoryId)
            .SetFirstResult(firstResult)
            .SetMaxResults(maxResults)
            .List<Article>();



        return articles;
    }

Answer 1

The truth is that you make two calls. But a count(*) call is very, very cheap in most databases and when you do it after the main call sometimes the query cache helps out.

Your counter call will often be a little different too, it doesn't actually need to use the inner joins to make sense. There are a few other little performance tweaks too but most of the time you don't need them.

Answer 2

I believe you actually can do what you ask. You can retieve the count and the page in one go in code but not in one SQL statement. Actually two queries are send to the database but in one round trip and the results are retrieved as an array of 2 elements. One of the elements is the total count as an integer and the second is an IList of your retrieved entities.

There are 2 ways to do that:

• MultyQuery
• MultiCriteria

Here is a sample taken from the links below:

IList results = s.CreateMultiQuery()
       .Add("from Item i where i.Id > :id")
       .Add("select count(*) from Item i where i.Id > :id")
       .SetInt32("id", 50)
       .List(); 

IList items = (IList)results[0];
long count = (long)((IList)results[1])[0];

Here is more info on how you can do that. It is really straight forward.

http://ayende.com/Blog/archive/2006/12/05/NHibernateMutliQuerySupport.aspx http://ayende.com/Blog/archive/2007/05/20/NHibernate-Multi-Criteria.aspx

If you read the 2 articles above you will see that there is a performance gain of using this approach that adds value to the anyway more transparent and clear approach of doing paging with MultiQuery and MultiCriteria against the conventional way.

Answer 3

Please note that recent versions of NHibernate support the idea of futures, so you can do.

var items = s.CreateQuery("from Item i where i.Id > :id")
       .SetInt32("id", 50)
       .Future<Item>(); 

var count = s.CreateQuery("select count(*) from Item i where i.Id > :id")
       .SetInt32("id", 50)
       .FutureValue<long>();

This is a much more natural syntax, and it will still result in a single DB query. You can read more about it here: http://ayende.com/Blog/archive/2009/04/27/nhibernate-futures.aspx