如何在NHibernate条件查询中使用DatePart [英] How to use DatePart in an NHibernate Criteria Query

问题描述

在我的网站上，人们可以购买车辆零件.我想为客户创建报告，以查看他们每月在网站上花了多少零件，并允许他们按日期进行过滤.如果我是用SQL编写的，我会这样写:

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)

该查询甚至可以作为NHibernate中的Criteria查询吗?

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

推荐答案

是的，有一种方法:

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

"day"是您要从日期中获取的值("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.

这篇关于如何在NHibernate条件查询中使用DatePart的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！