NHibernate and reporting

Question

I am building a website on top of nhibernate. Is there any efficient way to build reports? BY reports, I mean is there anyway to execute a complicated query that grabs random pieces of data? StoreProcedures? Hql?

Can I get single, non mapped values from hql?

Answer 1

Yes, you can do this. It's called dynamic instantiation. The syntax in HQL is

select new MyClass(cust.age)
from customers cust

Or, with Criteria:

.SetProjection(Projections.ProjectionList()
    .Add(Projections.Name("cust.age"), "age")
.SetResultTransformer(Transformers.AliasToBean<MyClass>())

Remember, you must have a matching constructor on MyClass!

Answer 2

Yes you can.

For simple stuff you can use HQL and select stuff into a new non-mapped object (its class must be "registered" with NH but there is no mapped per se). HQL syntax looks like select new NonMappedClass(column1, column2). You need an appropriate constructor to use this.

Reporting with HQL quickly breaks down. I often find myself knowning exactly what to do in SQL but having hard time figuring out the HQL way. Also the lack of real software tools for HQL slows you down (sorry NH Query Analyzer does not cut it). In these case, you can define a raw with an associated . When executing these queries, NH will return you a IList of array of Object. You need the cast the Object() into the proper type. You'll want this for advanced reporting queries.

This casting stuff is error prone and sucks big time. So you they did a nice AliasToBean query transformer that can map the result set column name to a property (names must match of course). I don't know about the latest NH release, but the older 1.2.1 AliasToBean converter seemed to have a bug in which it would converter nullable value type to the default value of the type instead of setting it to null. ie: int? would be 0 instead of null if the associated DB field was null. This prevented me from using AliasToBean in some case and I had to map by hand.

Best advice is don't fall into the trap of performing complex reporting with your business object and for loops. I've seen this in production. It will be a performance horror as your tables grows in size.