NHibernate for SQL
time to read 1 min | 149 words
Here is a little known fact, you can ask NHibernate to take any arbitrary result set from the database and turn it into an object.
return session.CreateSqlQuery(@"
select customer.Name as CustomerName,
count( case order.Type when 1 then 1 else 0 end ) as FastOrder,
count( case order.Type when 1 then 2 else 0 end ) as SlowOrder,
count( case order.Type when 1 then 3 else 0 end ) as AirMailOrder,
from Orders order join Customers customer
on order.Customer = customer.Id
group by customer.Name
")
.AddScalar("CustomerName", NHibernateUtil.String)
.AddScalar("FastOrder", NHibernateUtil.Int64)
.AddScalar("SlowOrder", NHibernateUtil.Int64)
.AddScalar("AirMailOrder", NHibernateUtil.Int64)
.SetResultTransformer(new AliasToBeanResultTransformer(typeof (CustomerOrderTypesCount)))
.List();
As you can probably imagine, this will return a list of CustomerOrderTypesCount objects, with the respective properties filled with the values from the result set.
You can also do it in the configuration, and even map full blown entities along side other interesting values.
Comments
If I understand your purpose correctly, shouldn't this be
<<
count( case order.Type when 1 then 1 else 0 end ) as FastOrder,
?
If not, I could use some more explanations about what this code does.
Hm, yes, that one is a typo.
Many thanks! This works like a charm for populating flat objects that need to query across multiple tables but are difficult for NHibernate to map or else not worth the overhead
Exactly what I was looking for. Thanks Ayende!
I needed to modify one of my HQL strings to use a case expression.
I was disappointed to see that these weren't going to be supported in NHibernate 1.2.
Still being new to NHibernate and ActiveRecord, I wasn't sure if I could get to use my projection class under Native SQL.
After applying your solution, I was almost off and running.
One thing that caused me immediate problem was one of my projection class' properties was an entity.
My quick fix was to add another property to the projection that did a FindByProperty.
The analogy to the Customers example would be to have a Customer entity property on the CustomerOrderTypesCount class, instead of a CustomerName.
Comment preview