如何在 NH Criteria API 中使用 year() 和 month() 函数? [英] how to user year() and month() functions in NH Criteria API?
问题描述
我需要在 Criteria API 中使用 year() 和 month() 函数来表达业务过滤器约束.像
I need to use year() and month() functions in Criteria API to be able to express a business filter constrain. Expressions like
cri.Add(Expression.Ge("year(Duration.DateFrom)", Year.Value));
cri.Add(Expression.Le("year(Duration.DateTo)", Year.Value));
显然不起作用 - 有什么解决方案可以实现这一目标吗?
obviously do not work - is there any solution how to achieve this?
我知道在 HQL 中完全有可能,但我需要使用标准 API 构建查询,因为还有一些额外的进程处理查询添加排序、分页等.
I know it's entirely possible in HQL, but I need to construct the query using criteria API because there're some additional processes processing the query adding sorting, paging etc..
我想重写为 Criteria API 的示例 HQL 解决方案:
sample HQL solution which I'd like to rewrite to Criteria API:
var ym = year * 100 + month;
var hql = ...(:ym between 100 * year(f.Duration.DateFrom) + month(f.Duration.DateFrom) and 100 * year(f.Duration.DateTo) + month(f.Duration.DateTo)";
推荐答案
使用 Projections.SQLFunction 可以实现这一点.工作解决方案:
It's possible to achieve this using Projections.SQLFunction. Working solution:
ISQLFunction sqlAdd = new VarArgsSQLFunction("(", "+", ")");
ISQLFunction sqlMultiply = new VarArgsSQLFunction("(", "*", ")");
var ym = Year.Value * 100 + Month.Value;
var dateFromMonthProj = Projections.SqlFunction("month", NHibernateUtil.Int32, Projections.Property("PurchaseDuration.DateFrom"));
var dateFromYearProj = Projections.SqlFunction("year", NHibernateUtil.Int32, Projections.Property("PurchaseDuration.DateFrom"));
var dateToMonthProj = Projections.SqlFunction("month", NHibernateUtil.Int32, Projections.Property("PurchaseDuration.DateTo"));
var dateToYearProj = Projections.SqlFunction("year", NHibernateUtil.Int32, Projections.Property("PurchaseDuration.DateTo"));
var calculatedYMFrom = Projections.SqlFunction(sqlAdd, NHibernateUtil.Int32, Projections.SqlFunction(sqlMultiply, NHibernateUtil.Int32, dateFromYearProj, Projections.Constant(100)), dateFromMonthProj);
var calculatedYMTo = Projections.SqlFunction(sqlAdd, NHibernateUtil.Int32, Projections.SqlFunction(sqlMultiply, NHibernateUtil.Int32, dateToYearProj, Projections.Constant(100)), dateToMonthProj);
cri.Add(Restrictions.Le(calculatedYMFrom, ym));
cri.Add(Restrictions.Ge(calculatedYMTo, ym));
这篇关于如何在 NH Criteria API 中使用 year() 和 month() 函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!