how to user year() and month() functions in NH Criteria API?

Question

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?

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..

---

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)";

Answer 1

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));

Answer 2

Would something like this work for you?

cri.Add(Expression.Ge("Duration.DateFrom", new Date(fromYear, 1, 1));
cri.Add(Expression.Le("Duration.DateTo", new Date(toYear, 12, 31));

Note that I changed your expression order -- I'm assuming you made a typo and you want to query for dates between DateFrom and DateTo. If the dates contain time data, the second expression would change to:

cri.Add(Expression.Lt("Duration.DateTo", new Date(toYear + 1, 1, 1));

In response to comment:

cri.Add(Expression.Ge("Duration.DateFrom", new Date(fromYear, fromMonth, 1));
// Actual code needs to get last day of to month since it will not always be 31
cri.Add(Expression.Le("Duration.DateTo", new Date(toYear, toMonth, 31));

Is your user input in the form "YYMM"? If that's the case, then you just have to parse out year and month from that string to create fromYear, fromMonth, etc.

Edit: my 3rd and final attempt:

// First parse the input, e.g: september 2009 into 9 (inMonth) and 2009 (inYear)
var fromDate = new DateTime(inYear, inMonth, 1);
var toDate = fromDate.AddMonths(1).AddDays(-1);

cri.Add(Expression.Ge("Duration.DateFrom", fromDate));
cri.Add(Expression.Le("Duration.DateTo", toDate));

Answer 3

I'm not sure I understod what you mean with your question but I had a similar question, and I solved the problem with:

crit.Add(Expression.Sql("(YEAR({alias}.ObsDatum) = ?)", year, NHibernateUtil.String))
crit.Add(Expression.Sql("(MONTH({alias}.ObsDatum) = ?)", manad, NHibernateUtil.Int32))