如何在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屋！