NHibernate SQL Query mapping on Single Column Result

Question

I have a very simple question but cannot find a simple answer.

I have NHibernate executing a raw SQL query, which unfortunately is loaded from the DB (yes, we store SQL query in the DB, not the best design but bear with me). This means that basically I have no idea how many columns the query will return. What I know is that if the query is a single column then it is what I need and if it is more than one column then I need the first one, easy enough.

In my code I have basically 3 options:

session.CreateSQLQuery(SQLString).List();
session.CreateSQLQuery(SQLString).List<object>();
session.CreateSQLQuery(SQLString).List<object[]>();

Problem is that the List() will return either a List<int> (or the appropriate type, but int should be in my case) if the query returns a single column, or a List<object[]>() if I have multiple columns. Same goes for List<object> except that it will not return a List<int> in this case. Of course trying to always cast to object[] (3rd option) and get the first element does not work, since nHibernate returns an int and that cannot be cast to object[].

Is there a way to force nHibernate to always return an object[] even in the case of a single column? If not, is there an easy way to check the number of columns in the result and act accordingly?

Thanks!

Probably NHibernate DTO will satisfy your need. Except List<object[]> — Anand
– Anand, Commented Jan 4, 2012 at 13:39
If you don't know what your query will return then how do you know what to do with the result, or do you decide that by the shape of the result? — Gert Arnold
– Gert Arnold, Commented Jan 4, 2012 at 14:10
I know that I need a list of int (IDs basically). If the query returns 1 column it will be what I need, but if it returns more than one column, the first column will be what I need. I then pass this list of IDs up to be processed... I don't know the type of results because the query is generated dynamically by other processes (it is basically a form of dynamic filtering). — Tallmaris
– Tallmaris, Commented Jan 4, 2012 at 14:56

Chris · Accepted Answer · 2012-01-04 14:54:19Z

3

You should take a look at the differant result transformers NHibernate provides out of the box (you can write your own as well of course).

What might fit in your case would be the NHibernate.Transform.ToListResultTransformer. AFAIK, it transforms your result to an IList, where each entry contains an IList again.

So:

IList items = session
.CreateSQLQuery(query)
.SetResultTransformer(new NHibernate.Transform.ToListResultTransformer())
.List()

if(((Ilist)items[0]).Count == 1)
// just one columns
else
// more columns or zero

answered Jan 4, 2012 at 14:54

Chris

7,4097 gold badges43 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tallmaris Over a year ago

Exactly what needed, I was actually looking into writing my own but good to know there is a pre-packed one. Thanks!

Collectives™ on Stack Overflow

NHibernate SQL Query mapping on Single Column Result

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related