Extend NHibernate-LINQ for Regex-Matching

The time between Christmas and New Year’s Eve traditionally is a great time to take a look at .NET-Stuff which is (yet) not part of my daily work. This time I wanted to dig a little deeper into the newest NHibernate version 3.0. I was especially interested in its LINQ-Implementation which was rewritten from scratch for the 3.0-GA release.

Today I tried to extend NH-LINQ with Regex-Matching. What first sounds quite demanding is really easy as NH has many extension points, including the LINQ-Provider.

Add RegexMatch-Function to SQL-Server

SQL-Server doesn’t have Regex-Support out-of-the-box, you need to implement it in .NET as a User-Defined-Function (UDF) and then deploy the assembly to SQL-Server. Here is the very basic implementation which is not optimized for performance in any way (there are some ways to precompile regular expressions):

public class UserDefinedFunctions
{
  [SqlFunction(IsDeterministic = true, IsPrecise = true)]
  public static bool RegexMatch(SqlString input, SqlString pattern)
  {
    return Regex.IsMatch(input.ToString(), pattern.ToString());
  }
};

To deploy that function the fastest way is to create a “Visual C# SQL CLR Database Project” which contains the deployment step. I won’t describe it here in details, there are a lot of resources on the net for this topic. Just two remarks: 1) the project must target .NET 3.5 and 2) the CLR-Support in SQL-Server must be activated via

sp_configure ‚clr enabled‘, 1
reconfigure

Implement the Extension-Method

I now add an Extension-Method called “RegexMatch” to the string-Class which has the same implementation as the UDF.

static class RegexExtensions
{
  public static bool RegexMatch(this string s, string pattern)
  {
    return Regex.IsMatch(s, pattern);
  }
}

Now “Mike”.RegexMatch(“Mi.e”) evaluates to true while “Mike”.RegexMatch(“Mo.e”) returns false.

Write HQL-Generator for RegexMatch-Method

I have a small NHibernate-Project with one mapped class called “MyUser”:

class MyUser
{
  public virtual int Id { get; set; }
  public virtual string Name { get; set; }
}

Not very exciting and I think you can guess how the table looks like. Now I want to do this:

session.Query<MyUser>()
   .Where(u => u.Name.RegexMatch("Mi.e"))
   .ToList();

Certainly this compiles because of the available Extension-Method but when I execute it I get the following error from NHibernate:

Unhandled Exception: System.NotSupportedException: Boolean RegexMatch(System.String, System.String)

NHibernate doesn’t know what to do with the RegexMatch-Call. We have to tell NHibernate how to translate the LINQ-Expression to a HQL-Expression for the actual UDF-Call.

First we must implement a new so-called HqlGenerator:

public class RegexMatchGenerator : BaseHqlGeneratorForMethod
{
  public RegexMatchGenerator()
  {
    var methodDefinition =
       ReflectionHelper.GetMethodDefinition(() => 
         RegexExtensions.RegexMatch(null, null));

    SupportedMethods = new[] { methodDefinition };
  }

  public override HqlTreeNode BuildHql(
    MethodInfo method,
    Expression targetObject,
    ReadOnlyCollection<Expression> arguments,
    HqlTreeBuilder treeBuilder,
    IHqlExpressionVisitor visitor
  )
  {
    return treeBuilder.Equality(
      treeBuilder.MethodCall("dbo.RegexMatch", new[]
      {
        visitor.Visit(arguments[0]).AsExpression(),
        visitor.Visit(arguments[1]).AsExpression()
      }),
      treeBuilder.Constant(1)
    );
  }
}

In the constructor our RegexMatch-Method is registered by using this very cool ReflectionHelper-Class (I must add this to my toolbelt).

In the BuildHql-Method now the magic happens. This code basically is the programmatic representation of “dbo.RegexMatch(@p1, @p2) = 1”. The comparison against 1 is necessary as SQL-Server doesn’t seem to handle RegexMatch as a valid boolean function.

Add HQL-Generator to NHibernate-Configuration

OK, the heavy part is implemented, now we need to register the new HQL-Generator somewhere. That’s easy too. All default HQL-Generators are registered in the class DefaultLinqToHqlGeneratorsRegistry. We must only extend this and add our own HQL-Generator to it:

public class MyLinqToHqlGeneratorsRegistry :
  DefaultLinqToHqlGeneratorsRegistry
{
  public MyLinqToHqlGeneratorsRegistry()
  {
    this.Merge(new RegexMatchGenerator());
  }
}

Last but not least we must set our class as the new HQL-Generator-Registry in the NHibernate-Configuration:

cfg.LinqToHqlGeneratorsRegistry<MyLinqToHqlGeneratorsRegistry>();

Now when I run the code once more

session.Query<MyUser>()
   .Where(u => u.Name.RegexMatch("Mi.e"))
   .ToList();

I see this SQL-Query executed:

select myuser0_.Id as Id0_, myuser0_.Name as Name0_
from MyUser myuser0_
where dbo.RegexMatch(myuser0_.Name, @p0)=1;@p0 = ‚Mi.e‘

It works !

Summary

The masterminds behind NHibernate seem to have invested a lot of effort to make it as extensible as possible. Great work, guys !