Nhibernate sum/groupby question

CREATE TABLE [dbo].[OrderHistory](
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL, /* FK to user table*/
    [ItemID] [int] NOT NULL, /* FK to Item table */
    [PurchaseDate] [datetime] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [Quantity] [float] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [RecordID] ASC
) ON [PRIMARY]

I have a table to save order history of users. I need to calculate total money spent by a user on yearly basis. If I write SQL query directly, it is very simple and straightforward:

select SUM(UnitPrice * Quantity) as TotalAmount, DATEPART(yyyy, PurchaseDate)
from OrderHistory
where UserId = 1
group by DATEPART(yyyy, PurchaseDate)

Planned object model is:

public class YearlySummary {
        public virtual int Year {
            get;
            set;
        }

        public virtual decimal TotalSpent {
            get;
   开发者_JAVA技巧         set;
        }
    }

But how can I do it in Nhibernate?

Thanks Hardy

You can do this using Linq.

You should be doing something as follows:

from orderHistory in Session.Linq.Where(x=>x.UserId=1) group by orderHistory.PurchaseDate into g select new {TotalAmount=g.Sum(orderHistory.UnitPrice * orderHistory.Quantity)}

If you only have to fetch the (historic) data it's quite easy actually. Use a formula in your mapping file:

<property name="TotalSpent" formula="UnitPrice * Quantity" type="Decimal" />

This will multiply the two values in the query (e.g. select UnitPrice * Quantity from OrderHistory) and put the result in the TotalSpent property. Note that this won't work for transient objects until you persist the data.