NHibernate unmapped table - object is a multicolumn type - ToListResultTransformer

Question

I have NHibernate executing a raw sql query which is built from the database. In the software the use is able to choose a table and a couple of columns with a defined order. In my software I use this information to build a sql query which selects the defined columns from the table and concatenates the columns to one column. Here a quick example.

The user defines that he wants to select the columns FirstName und SurName from the table User. Then I build this select statement:

select (firstname || surname) as resultData from user

And now I try to use the select statement:

var list = session.CreateSQLQuery(sqlQuery)
            .SetResultTransformer(new NHibernate.Transform.ToListResultTransformer())
            .List();

But my problem is now that I get a GenericADOException with the message

object is a multicolumn type

I didn't found much on the Internet for my problem. Only a thread which accomplishs the same task without this error: NHibernate SQL Query mapping on Single Column Result

Answer 1

Yeah, I found my problem. It was no problem with the code rather then the database.

There the columns could have null values, and NHibernate didn't liked that.

FirstName | Surname
-------------------
Sam       | Smith
          | Archer
Michael   | Brown

I solved the problem with an adjustment of the sql satement. I'm using now the coalesce function.

The new sql statement:

select (coalesce(firstname, '') || coalesce(surname, '')) as resultData from user