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?

Stefan Steinegger · Accepted Answer · 2009-12-04 10:22:05Z

3

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.

answered Dec 4, 2009 at 10:22

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kristoffer Ahl Over a year ago

Sorry, about that. I should have made it clear that "SELECT .... FROM SomeTable WHERE ...." was just an example. We do not use that part in our code.

Rashack Over a year ago

The answers says that the .Where in your mapping expects pure SQL not any form of HQL.

toxaq · Accepted Answer · 2009-12-18 21:45:05Z

1

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(...)")

answered Dec 18, 2009 at 21:45

toxaq

6,8873 gold badges50 silver badges57 bronze badges

5 Comments

Kristoffer Ahl Over a year ago

Thanks. But will that work for a HasMany where-statement like above? According to the link below, where="" can only contain plain old SQL. I had to go for a different solution as I needed to get the feature I was working on done. groups.google.com/group/fluent-nhibernate/browse_thread/thread/…

toxaq Over a year ago

The fact you're getting a SQL exception would indicate that NHibernate is passing it through to the sql engine and therefore it's not a NHibernate problem with recognising the function. As such, I'd say yes. In terms of where clauses only taking raw sql; If that's the case then you shouldn't have to register the function at all and just type straight "dbo.recent_date()" in your where clause.

Kristoffer Ahl Over a year ago

I don't think that will work as SQLite (to my knowledge) does not support custom functions. I need the solution to work with both MsSql and SQLite and I was looking to do this at a dialect level. Any ideas?

toxaq Over a year ago

I'm not sure why you're trying to register a custom SQL function for a database engine that doesn't support them. The point of a Dialect is to match it to an engine's functionality. That's why dialects are per database engine.

Kristoffer Ahl Over a year ago

Well, to tell you the truth I thought that functions registered with RegisterFunction was handled by NHibernate and that NHibernate resolved the SQL behind it before it was sent off to the db. So I guess RegisterFunction is not the way to go at all.

Collectives™ on Stack Overflow

Custom SQL function for NHibernate dialect

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related