Polymorphic count with NHibernate

Posted on August 16, 2011 · 1 min read · tagged with: #Cassandra #Deiphobus #NoSql

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)

[sourcecode language=”csharp” light=”true”] // whoa! var allObjects = session.QueryOver().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.

[sourcecode language="csharp" light="true"]
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!


Comments

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.

by Charlie Kilian at 2012-07-26 20:19:35 +0000

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

by Farzad at 2012-09-17 19:50:55 +0000

IMHO its a nice addition, not a patch. Use it wisely, as there is no polymorphic Get on NHibernate (but you can implement one quite easily)

by scooletz at 2012-09-18 19:17:14 +0000

Nice :)

by macpak at 2011-08-16 14:22:38 +0000

Thanks :)

by scooletz at 2011-08-16 17:42:05 +0000