Get Count without loading collection

jbliss1234 · **Joined:** Mon Sep 26, 2005 5:57 pm **Posts:** 39

How can I get count of a collection (as a result of a query) instead of loading the collection and then getting the count?

I would prefer to use ICriteria if possible instead of HQL.

Nels_P_Olsen · **Posted:** Wed Nov 23, 2005 6:28 pm

I've done something similar, based on a tip in the Hibernate documentation:

Code:

string queryText = "...";
QueryParameters queryParameters = ...;

Enumerator enumerator = session.Enumerable(
    queryText, queryParameters).GetEnumerator();

int count = 0;
if (enumerator.MoveNext())
{
    count = Convert.ToInt32(enumerator.Current);
}

Apparently you have to use HQL, I don't see any overloads supporting ICriteria ...

jnapier · **Joined:** Sat Sep 10, 2005 3:46 pm **Posts:** 178

I dont think you can do it with ICriteria but there is a simple way with HQL.

Code:

//create a filter on the collection
IQuery listQuery = this.Session.CreateFilter(documents, "Select count(*)");

//return the count
return (int)listQuery.UniqueResult();

I like to check if the collection has already been loaded yet though. That way, if the collection is already loaded, I dont have to hit the database.

Code:

   if(documents is PersistentCollection){      
            //get the list as a proxy
            PersistentCollection listProxy = documents as PersistentCollection;

            //if the list hasnt been initialized, query the collection to determine the count
            //this will be faster than bringing the whole collection down
            if(listProxy.IsDirectlyAccessible == false){
               //create a count filter for the collection
               IQuery listQuery = this.Session.CreateFilter(documents, "Select count(*)");

               //return the count
               return (int)listQuery.UniqueResult();
            }

//if we made it this far then use the regular ilist count operation
return documents.Count;
         }

KPixel · **Posted:** Tue Nov 29, 2005 10:13 am

Note that you should use:

Code:

if( NHibernate.NHibernateUtil.IsInitialized(documents) )
    ...

nhbdev · **Joined:** Thu Sep 29, 2005 3:30 pm **Posts:** 3

This may not be what you are looking for, but this is how I did.

return (int) Session.Find(queryString)[0];

jnapier · **Joined:** Sat Sep 10, 2005 3:46 pm **Posts:** 178

thanks for the tip KPixel. With Kpixel's tip my code above changed to

Code:

if(documents is PersistentCollection){      

            //if the list hasnt been initialized, query the collection to determine the count
            //this will be faster than bringing the whole collection down
            if(NHibernate.NHibernateUtil.IsInitialized(documents) == false){
               //filter the list for the specified document
               IQuery listQuery = this.Session.CreateFilter(documents, "Select count(*)");

               //return the count
               return (int)listQuery.UniqueResult();
            }
         }
         
         //if we made it this far then use the regular ilist count operation
         return documents.Count;

KPixel · **Posted:** Wed Nov 30, 2005 8:52 am

I assume you forgot to remove the line:

Code:

if(documents is PersistentCollection){

:wink:

tjames · **Joined:** Mon Jan 16, 2006 7:27 pm **Posts:** 1

jnapier wrote:

I dont think you can do it with ICriteria but there is a simple way with HQL.

Code:

//create a filter on the collection
IQuery listQuery = this.Session.CreateFilter(documents, "Select count(*)");

//return the count
return (int)listQuery.UniqueResult();

I like to check if the collection has already been loaded yet though. That way, if the collection is already loaded, I dont have to hit the database.

Code:

   if(documents is PersistentCollection){      
            //get the list as a proxy
            PersistentCollection listProxy = documents as PersistentCollection;

            //if the list hasnt been initialized, query the collection to determine the count
            //this will be faster than bringing the whole collection down
            if(listProxy.IsDirectlyAccessible == false){
               //create a count filter for the collection
               IQuery listQuery = this.Session.CreateFilter(documents, "Select count(*)");

               //return the count
               return (int)listQuery.UniqueResult();
            }

//if we made it this far then use the regular ilist count operation
return documents.Count;
         }

In regards to the code above, the first param of session.Filter is a collection. What I can't figure out is which collection I should be passing to that?

I basically have an application that searches for data and then displays it. However, if based on the search criteria, more than 50 items are going to be returned, the user has to narrow their search. The problem is that if the user enters criteria that matches 250,000 NHibernate loads all of them in order for me to get a count.

Thanks

sergey · **Posted:** Tue Jan 17, 2006 5:30 am

session.Filter works only on persistent collections. If you are searching for data, you probably have a simple ArrayList, you can't call Filter on that.

mteper · **Joined:** Wed Jun 08, 2005 4:59 pm **Posts:** 27

A follow-up question: I am trying to construct a query that does something equivalent to a sql "top" clause on the count of subcollection. The HQL is:

Code:

from ParentEntity parent order by count(parent.Children) desc

which I follow with:

Code:

IQuery query = session.CreateQuery(queryString);
query.SetFirstResult(0);
query.SetMaxResults(10);

The problem is that it tries to bring in ALL children and ALL children's children and so forth. Is there a way to do this more efficiently and have it generate SQL that effectively does an order by count()?

Thanks!

sergey · **Posted:** Fri Jan 27, 2006 5:13 am

Make sure your children collection is set to be lazy and that outer-join is disabled for it.

mteper · **Joined:** Wed Jun 08, 2005 4:59 pm **Posts:** 27

Yup, figured that out, thanks!!!

-MT