Adding Custom SQL Functions to NHibernate at Runtime
The classic way to register custom SQL functions on NHibernate consists in subclassing a specific Dialect (for example, MsSql2008Dialect) and making a call to the protected method RegisterFunction somewhere in the constructor. Of course, if you use multiple dialects, you have to subclass them all.
If you want to do it without creating new classes, you have to use a bit of reflection. Here’s what I do:
1: public static class DialectExtensions
2: {
3: private static readonly MethodInfo registerFunctionMethod = typeof(Dialect).GetMethod("RegisterFunction", BindingFlags.Instance | BindingFlags.NonPublic);
4:
5: public static void RegisterFunction(this Dialect dialect, String name, ISQLFunction function)
6: {
7: registerFunctionMethod.Invoke(dialect, new Object[] { name, function });
8: }
9:
10: public static void RegisterFunction(this ISessionFactory factory, String name, ISQLFunction function)
11: {
12: registerFunctionMethod.Invoke(GetDialect(factory), new Object[] { name, function });
13: }
14:
15: public static Dialect GetDialect(this ISessionFactory factory)
16: {
17: return((factory as SessionFactoryImpl).Dialect);
18: }
19: }
And an example, for SQL Server, is:
1: factory.RegisterFunction("last_week", new SQLFunctionTemplate(NHibernateUtil.Date, "DATEADD(day, -7, GETDATE())"));
2:
3: IEnumerable<Order> lastWeekOrders = session.CreateQuery("from Order o where o.Date >= last_week()").List<Order>();
Or, slightly better:
1: public static class DialectExtensions
2: {
3: public static void RegisterFunction<T>(this ISessionFactory factory, String name, String sql)
4: {
5: IType type = NHibernateUtil.GuessType(typeof(T));
6: SQLFunctionTemplate template = new SQLFunctionTemplate(type, sql);
7: RegisterFunction(factory, name, template);
8: }
9:
10: //...
11: }
12:
13: factory.RegisterFunction<DateTime>("last_week", "DATEADD(day, -7, GETDATE())");
