Hi i have a query like this
SELECT Customer.Name, sum([Load].Profit) as Profit FROM Customer INNER JOIN [Load] ON Customer.Id = [Load].CustomerId GROUP BY Customer.Name
I need to execute this query in nhibernate and map it to a Custom domain object which i created as like this
public class CustomerProfit
{
public String Name;
public Decimal Profit;
}
Is it possible to do so ? and how , or is it possible to execute this custom query in HQL ?
public sealed class CustomerProfitQuery : IResultTransformer
{
public static readonly string Sql = "SELECT Customer.Name, sum([Load].Profit) as Profit FROM Customer INNER JOIN [Load] ON Customer.Id = [Load].CustomerId GROUP BY Customer.Name";
public static readonly CustomerProfitQuery Transformer = new CustomerProfitQuery();
// make it singleton
private CustomerProfitQuery()
{ }
public IList TransformList(IList collection)
{
return collection;
}
public object TransformTuple(object[] tuple, string[] aliases)
{
return new CustomerProfit
{
Name = (string)tuple[0],
Profit = (decimal)tuple[1],
};
}
}
// usage
var customerprofits = session.CreateSQLQuery(CustomerProfitQuery.Sql)
.SetResultTransformer(CustomerProfitQuery.Transformer)
.List<CustomerProfit>()
