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)
  RETURN 0 (false) or 1 (true)

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()
                () => dbo.DisplayRecord(null, null)), 
                new DisplayRecordMethodsHqlGenerator());

And an Hql generator

    public class DisplayRecordMethodsHqlGenerator
        : BaseHqlGeneratorForMethod
        public DisplayRecordMethodsHqlGenerator()
            SupportedMethods = new[] {
                  () => 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",

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:


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.

2 Responses to Calling a User Defined TSQL Function using NHibernate Linq

  1. […] used NH generators already once or twice, I wrote an extension […]

  2. […] Of course, the above code for IEnumerable would have no effect on how NHibernate handles the IQueryable.Max() method. This would have to be dealt with using NHibernate extensions. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: