Polymorphic count with NHibernate

If you’re a user of NHibernate, I hope you enjoy using polymorphic queries. It’s one of the most powerful features of this ORM allowing you to write queries spanning against several hierarchies. Finally, you can even query for the derivations of object to get all the objects from db (don’t try this at home:P). The most important fact is, that having a nicely implemented dialect NH can do it in one DB call, separating specific queries by semicolon (in case of MS SQL)

// whoa!
var allObjects = session.QueryOver<object>().List();

Although the feature is powerful, you can find a small problem. How to count entities returned by the query without getting’em all to the memory. Setting a simple projection Projections.RowCount() will not work. Why? ‘Cause it’s gonna query each table with COUNT demanding at the same time that IDataReader should contain only one, unique result. It won’t happen, and all you’ll be left with it’ll be a nice exception. So, is it possible to count entities in a polymorphic way? Yes! You can define in a extension method and use it every time you need a polymorphic count.

private static int GetPolymorphicCount(ISession s, Type countedType)
{
    var factory = s.GetSessionImplementation().Factory;
    var implementors = factory.GetImplementors(countedType.FullName);

    return implementors
        .Select(i => s.CreateCriteria(i)
                .SetProjection(Projections.RowCount())
                .FutureValue<int>())
        .ToArray() // to eagerly create all the future values for counting
        .Aggregate(0, (count, v) => count + v.Value); // sum up counts
}

Happy counting!

About these ads

5 responses to “Polymorphic count with NHibernate

  1. Great post! Can’t this be added somehow as a patch to nhibernate? I believe this is related to NH-2500 or NHLQ-58.

  2. Thanks for pointing me in the right direction.

    I had a similar problem, but with a different symptom. I was trying to use .FutureValue():

    var futureCount = work.Session
    .CreateQuery( @”select count(*)
    from Amber.BLL.IMappedFormInstance inst
    where inst.Patient.PatientCode = :patientCode” )
    .SetString( “patientCode”, patientCode )
    .FutureValue();

    It doesn’t throw an exception like using .UniqueResult() does. Instead, it just returns the count from the first result set (in my case, that was 0).

    Switching to your code fixed the problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s