NHibernate output columns/projections of CreateQuery().list()

Question

Hi, I have simple HQL query:

var list = OpenSession()
              .CreateQuery("SELECT MAX(p.price) as max_price, 
                                   COUNT(p.id) as count_all 
                            FROM Order o left join o.Products p")
              .List();

I would like to output "max_price" and "count_all" columns/projections as easy as possible.

Something like:

Console.WriteLine(list[0]["max_price"]);
Console.WriteLine(list[0]["count_all]);

Any idea?

Answer 1

Not sure about this but I think that you will need to create a class and project into that. This is how I would start by approaching it

class UserStatistics{
 MaxPrice {get; set;}
 CountAll {get; set;}
}

var list = OpenSession()
              .CreateQuery("SELECT MAX(p.price) as max_price, 
                                   COUNT(p.id) as count_all 
                            FROM Order o left join o.Products p")
              .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(UserStatistics)))
              .List<UserStatistics>();

then it should be a matter of

Console.WriteLine(list[0].MaxPrice);
Console.WriteLine(list[0].CountAll);

Great Post explaining better.

Answer 2

You can transform it to Hashtable

.SetResultTransformer(AliasToEntityMap).List<HashTable>()[0]["max_price"];