Custom SQL function for NHibernate dialect

Question

I want to be able to call a custom function called "recent_date" as part of my HQL. Like this: [Date] >= recent_date()

I created a new dialect, inheriting from MsSql2000Dialect and specified the dialect for my configuration.

public class NordicMsSql2000Dialect : MsSql2000Dialect
{
    public NordicMsSql2000Dialect()
    {
     RegisterFunction(
      "recent_date",
      new SQLFunctionTemplate(
       NHibernateUtil.Date,
       "dateadd(day, -15, getdate())"
       )
      );
    }
}

var configuration = Fluently.Configure()
.Database(
    MsSqlConfiguration.MsSql2000
    .ConnectionString(c => .... )
    .Cache(c => c.UseQueryCache().ProviderClass<HashtableCacheProvider>())
    .Dialect<NordicMsSql2000Dialect>()
)
.Mappings(m => ....)
.BuildConfiguration();

When calling recent_date() I get the following error: System.Data.SqlClient.SqlException: 'recent_date' is not a recognized function name

I'm using it in a where statement for a HasMany-mapping like below.

HasMany(x => x.RecentValues)
    .Access.CamelCaseField(Prefix.Underscore)
    .Cascade.SaveUpdate()
    .Where("Date >= recent_date()");

What am I missing here?

Answer 1

I think, Where is a SQL statement, not a HQL statement. So it doesn't know the function. It only works for HQL, in queries or filters.

Answer 2

I thought you had to prefix your function with "dbo." whenever you used it. My custom dialect has this:

RegisterFunction("dbo.isbounded", new SQLFunctionTemplate(NHibernateUtil.Double, "dbo.IsBounded(?1, ?2, ?3, ?4, ?5, ?6)"));

It's then called using

Expression.Sql("dbo.IsBounded(...)")