Can Nhibernate's Queryover Syntax Select Max() Of An Sqlfunction?

I have registered an SQL function in my dialect subclass RegisterFunction('addseconds', new SQLFunctionTemplate(NHibernateUtil.Date, 'dateadd(second, ?1, ?2)')); which can be used

Solution 1:

You need to update the NHUtil to be DateTime:

RegisterFunction("addseconds", newSQLFunctionTemplate(NHibernateUtil.DateTime, "dateadd(second, ?1, ?2)"));

Otherwise you will only be dealing with the Date portion.

Your query is fine, you just need to wrap it in a Projections.Max() like so:

var q = _session.QueryOver<Event>()
                .Select(Projections.Max(Projections.SqlFunction(
                        "addseconds",
                        NHibernateUtil.DateTime,
                        Projections.Property<Event>(y => y.DurationInSeconds),
                        Projections.Property<Event>(y => y.StartTime))))
                .SingleOrDefault<DateTime>();

I just quickly wrote a test, (different naming than above) and it produced the query:

SELECTmax(dateadd(second,
                   this_.DurationInSeconds,
                   this_.SomeDate)) as y0_
FROM   Employee this_