NHIBERNATE - CUSTOMIZE THE LINQ PROVIDER TO CALL YOUR USER DEFINED SQL FUNCTIONS

Let’s see how you can extend the new Linq to NHibernate provider to call your custom defined SQL functions.

First off let’s start defining a custom function we want to call:

CREATE FUNCTION [dbo].[CustomGetMonth] 
(
	-- Add the parameters for the function here
	@date datetime
)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
	-- Return the result of the function
	IF @date IS NULL RETURN 0
	RETURN Datepart(mm,@date)

END

This is not really a useful function, we’ll just it for illustrative purposes.

Your next step it have NHibernate be aware of this function, you do that by inheriting your own custom Dialect and registering the function:

public class MyDialect : MsSql2008Dialect
{
	public MyDialect()
	{
		RegisterFunction("dbo.customgetmonth", new StandardSQLFunction("dbo.customgetmonth", NHibernateUtil.Int32));
	}
}

The first parameter of the RegisterFunction() is the internal name NHibernate will use to refer to our custom function, the second parameter is a StandardSQLFunction object that contains the actual name of the function prefixed by the schema (it’s extremely important that you provide the schema information too otherwise NHibernate will not call the function) and the type returned by the function.

Tree things are really important here: provide the schema information, use all lowercase letters when defining the function internal name and specify the correct type used as return value.

It’s time to implement the Linq extension method that mimic this function (to have the same functionality with Linq to Objects):

public static class CustomGetMonthExtension
{
	public static int CustomGetMonth(this DateTime date)
	{
		return date.Month;
	}
}

To customize the Linq provider we can now follow the step provided in these posts:

And write the Method Generator and the Generator Registry as follows:

public class CustomGetMonthGenerator : BaseHqlGeneratorForMethod
{
	public CustomGetMonthGenerator()
	{
		SupportedMethods = new[] { ReflectionHelper.GetMethodDefinition(() => CustomGetMonthExtension.CustomGetMonth(DateTime.Now)) };
	}

	public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject,
		ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
	{
		IEnumerable<HqlExpression> args = arguments.Select(a => visitor.Visit(a))
			.Cast<HqlExpression>();

		return treeBuilder.MethodCall("dbo.customgetmonth", args);
	}
}

public class AllLinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
	public AllLinqToHqlGeneratorsRegistry()
	{
		this.Merge(new CustomGetMonthGenerator());
	}
}

In the CustomGetMonthGenerator’s constructor we provide the signature of the method; the translation to the ‘Hql Expression Tree’ is done in the BuildHql function.

The HqlTreeBuilder.MethodCall() allows us to call any SQL function (predefined or custom) passing in the name of the registered function as the first argument and a collection of parameters (in the proper order expected by the sql function) expressed as a list of HqlExpression.

To make the things a little bit clear:

targetObject - the expression that represents the instance of the object that ‘owns’ the function we are calling (null in our specific case, because we are using an extension method, if we had provided a method like: x => x.Equals(y), then targetObject would have been the expression representing ‘x’ object).
arguments - a list of expressions representing the arguments passed to the function (in the same order).

Using this knowledge we can easily build the expression tree that represent our Linq to Object call.

As the final step let’s see what sql query NHibernate generates using the NUnit console and a simple query like this:

session.Query<Adult>().Select(a => a.BirthDate.CustomGetMonth2()).ToList()

I’m really impressed how much it’s easy to extend the provider once you got the grasp on it.

PrimordialCode

NHibernate - Customize the Linq provider to call your user defined SQL functions

Related Content

Alessandro Giorgetti