How to use DatePart in NHibernate criteria query

Question

On my site, people can buy auto parts. I want to create a report for a client to find out how much he spent on parts per month on the site and let them filter by date. If I wrote this in SQL, I would write the following:

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

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 want to get from the date ("DateTimeProperty"), and pvDay is the day you compare it to.

Answer 2

Criteria API HQL. . API- Criteria , . , ? API HQL ( ), , .