NHibernate criteria API - how do I add a clause to compare one date to another date minus a value

Question

I'm trying to build a criteria object using NHibernate that will compare date1 to date2 minus a value. In Sql Server I'd do this:

select * from table where date1 < DateAdd(Day, 2, date2)

Can anyone tell how I would do this in NHibernate?

Cheers

EDIT

I should have specified this earlier, I'd like to find out how do do this using the criteria API.

Also date1 and date2 are columns on the table, so I don't know their value before running the query

Answer 1

Given the minimal amount of information, here's my estimate:

TimeSpan timespanToSubtract;
var dateToCompare = new DateTime().Subtract(timespanToSubtract);

// using NHibernate.Linq:
var result = Session.Linq<Table>()
                 .Where(t => t.Date1 < dateToCompare)
                 .ToList();

// using HQL
var hql = "from Table t where t.Date1 < :dateToCompare");
var result = Session.CreateQuery(hql)
                 .SetParameter("dateToCompare", dateToCompare)
                 .List<Table>();

Answer 2

Well, exactly the same way :-)

HQL:

session.CreateQuery("from Entity where Date1 < DateAdd(Day, 2, Date2)")

SQL:

session.CreateSQLQuery(@"select * from table
                         where date1 < DateAdd(Day, 2, date2)")
       .AddEntity(typeof(Entity))

Criteria:

session.CreateCriteria<Entity>()
       .Add(Restrictions.LtProperty(
            Projections.Property("Date1"),
            Projections.SqlFunction(
                        new SQLFunctionTemplate(NHibernateUtil.Date,
                                                "DateAdd(Day, 2, ?1)"),
                        NHibernateUtil.Date,
                        Projections.Property("Date2"))))

In all cases, .List() will execute the query and return a list of objects of the specified type.

Answer 3

In order to do this, you'll likely need to create a custom dialect that registers the dateadd function and then use the SqlFunction projection to perform the critiera query.