how to get NHibernate aggregate function sum() to return decimal?

Question

Hi, I can't get sum() to return decimal and it always returns int64 truncating the decimals. I have Googled for a whole day but still can't find a real work around. I have a DB table called ProductPurchase with

QtyPurchased(int) and UnitPurchasePrice(money) columns,

these are mapped to a C# POCO object using NHibernate, where QtyPurchase is a int and UnitPurchasePrice is a decimal property.

I have the following HQL query where I want to get the total purchase amount for a given day:

select sum(detail.QtyPurchased * detail.UnitPurchasePrice) from Domain.Entities.ProductPurchase AS detail where datediff("day", detail.PurchaseDate, :trading_date) = 0

Now for what ever reason, the query.UniqueResult always returns an Int64 integer, truncating the decimals, whereas the SQL generated obviously returns the correct number complete with decimals. Can someone shed somelight on how to get this to return decimal?

I have noticed that if I use SQL (i.e. CreateSqlQuery), I can get the decimal back. Is this a bug with Nhibernate?

Thanks heaps

Steven Kuo

Answer 1

Inverting the order of the factors did it for me: (price * qty) instead of (qty * price).

I guess it must be checking just the first parameter type, please fill a JIRA issue if this workaround worked for you.