NHibernate for SQL
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.