NHibernate Projection using SqlQuery...

Question

Hi,

I'm trying to reproduce the HqlQuery style 'select new ObjectToProjectOut' functionality. i.e. take a list of columns returned from a query and return as a list of ObjectToProjectOut types that are instantiated using a Constructor with as many parameters as the columns in the query.

This is in effect what 'select new ObjectToProjectOut' achieves in Hql.... but clearly that's not available in SqlQuery. I think I need to set a result transform and use either PassThroughResultTransformer, DistinctRootEntityResultTransformer etc to get it to work.

Anyone know what I should use ?

Answer 1

You could use the AddEntity method to fill entities from a SQL query.

Here are two examples from the NHibernate docs:

sess.CreateSQLQuery("SELECT * FROM CATS")
    .AddEntity(typeof(Cat));

sess.CreateSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS")
    .AddEntity(typeof(Cat));

Answer 2

ok.... after looking at the NHibernate code it seems that I was looking for AliasToBeanConstructorResultTransformer. Of course!

However I've find an nHibernate bug. No really. If you have the same column name returned twice from to different tables (market name and account name, say) then by the time nHibernate returns the array from the db to the transformer, the first occurance of 'Name' will be used for both. Nasty.

Work around is to uniquely alias. With Hql, the sql generated is heavily aliased, so this is only a bug with SqlQuery.

Grrrr.Today must be my day, also find another nHibernate bug/issue I've posted to StackOverflow for comment.