NHibernate and MySql Keywords

Question

Why Nibernate HQL can not handle the following query:

from Deal D where (D.ApprovalDate + INTERVAL 1 Year) < current_timestamp() <  (D.RenewalDate + INTERVAL -1 Year) 

knowing that INTERVAL and YEAR are keywords in MySQL, so this is kind of mixing Sql within Hql (unless Hql can handle date functions like so and I don't know) . The dialect is MySQLDialect

Its perfectly valid to execute this query

  SELECT '2005-01-01' + INTERVAL 1 Year;

Answer 1

You can pass the Sql directly in the query using Criteria Query. Something like this

Session.CreateCriteria<Deal>()
.Add(Restrictions.Sql(D.ApprovalDate + " INTERVAL 1 Year < current_timestamp() < " + D.RenewalDate + " INTERVAL -1 Year")

The Restrictions.Sql will pass whatever you give it directly to the database as sql.