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

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.