Calling a User Defined TSQL Function using NHibernate Linq

NHibernate Linq supports calling UDFs, but the implementation is not straight-forward.

I previously wrote about adding support for .Net string comparison functions in NHibernate Linq. This strategy uses existing .Net methods (such as methods of the System.String class) and maps them onto Linq tree operations which in turn are translated into SQL by the NHibernate database driver.

This time, we want to access a user defined TSQL function which cannot be interpreted as a “method” of a “data type” because it contains generic parameters, such as integers or date values, and is not specific to a single table, such as this example on SO.

Nevertheless my solution is based on that example.

The function I want to access is called dbo.Display_Record and is used to decide whether a record can be displayed in the UI based on a given date range.

CREATE FUNCTION [dbo].[Display_Record]

  (@ValidFrom DATETIME, @ValidUntil DATETIME)
RETURNS INT AS
BEGIN
  RETURN 0 (false) or 1 (true)
END

Note that the return type of the function should not be boolean, due to the interoperation of NH and SQL Server (see bug report here) – use integer values instead.

First, we need a declaration and dummy implementation in C#

    public static class dbo
    {
        public static int DisplayRecord(DateTime? from, DateTime? until)
        {
            return 0;
        }
    }

Next, an Hql (the SQL-like language NH uses internally) generator registry must be registered for the method call “dbo.DisplayRecord”:

    public class DisplayRecordLinqToHqlGeneratorsRegistry

        : DefaultLinqToHqlGeneratorsRegistry
    {
        public DisplayRecordLinqToHqlGeneratorsRegistry()
            : base()
        {
            RegisterGenerator(ReflectionHelper.GetMethodDefinition(
                () => dbo.DisplayRecord(null, null)), 
                new DisplayRecordMethodsHqlGenerator());
        }
    }

And an Hql generator

    public class DisplayRecordMethodsHqlGenerator

        : BaseHqlGeneratorForMethod
    {
        public DisplayRecordMethodsHqlGenerator()
        {
            SupportedMethods = new[] {
                ReflectionHelper.GetMethodDefinition(

                  () => dbo.DisplayRecord(null, null))
            };
        }

        public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
            ReadOnlyCollection<Expression> arguments, 
            HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            return treeBuilder.MethodCall("dbo.Display_Record",
                visitor.Visit(arguments[0]).AsExpression(),

                visitor.Visit(arguments[1]).AsExpression());
        }
    }

arguments[0] and arguments[1] are the arguments passed to our .Net function, and need to be added to the Linq tree.

Finally, our generator registry needs to be registered using Mapping By Code configuration:

            configuration.LinqToHqlGeneratorsRegistry

                <DisplayRecordLinqToHqlGeneratorsRegistry>();

Now we can write NH Linq queries such as

var records = session.Query<MyTable>
    .Where(record => dbo.DisplayRecord(record.ValidFrom, record.ValidUntil);

and the WHERE condition correctly references our UDF function.