How to use DatePart in an NHibernate Criteria Query

Question

On my site, people can buy parts for vehicles. I want to create a report for a customer to see how much they have spent on parts per month on the site, and allow them to filter by date. If I was writing it in SQL, I would write this:

SELECT
    v.id,
    DATEPART(YEAR, o.order_date), 
    DATEPART(MONTH, o.order_date),
    SUM(i.unit_price * i.quantity)
FROM vehicles v
    join order_items i on v.id = i.truck_id
    join orders o on o.order_id = i.order_id
WHERE v.customer_id = @CustomerId
    AND o.order_date > @StartDate
    AND o.order_date < @EndDate
GROUP BY DATEPART(YEAR, o.order_date), DATEPART(MONTH, o.order_date)

Is this query even possible as a Criteria query in NHibernate?

Answer 1

Some people think of Criteria API as a modern replacement for HQL. This can't be further from truth. The Criteria API is intended to be used in scenarios where query has to be built dynamically. For static queries, why punish yourself? Just use good old HQL API (you can parametrize it), it does support grouping and anything else you throw at it.

Answer 2

Yes there is a way:

CurrentSession.CreateCriteria(typeof(Object)).Add(Expression.Eq(Projections.SqlFunction("day", NHibernateUtil.DateTime, Projections.Property("DateTimeProperty")), pvDay))

Where "day" is the value you'd like to get from the date ("DateTimeProperty") and pvDay is the day you're comparing it to.