Using DateAdd with NHibernate Linq

The goal is to do something like this using NHibernate and Linq:-

Session.Query<Item>.Where(x => x.Start.AddHours(3) > x.Finish );

If you run this code you will get the following error message:-

[NotSupportedException: System.DateTime AddHours(Double)]

So is there a solution?

The answer is yes, NHibernate is very extensible. The solution below is based on NHibernate 3.3 and the Loquacious configuration.

First you need to create a custom dialect and tell NHibernate about the MsSql `dateadd` function:-

public class CustomDialect : MsSql2008Dialect
{
    public CustomDialect()
    {
        RegisterFunction(
             "AddHours",
             new SQLFunctionTemplate(
                  NHibernateUtil.DateTime,
                  "dateadd(hh,?2,?1)"
                  )
             );
    }
}

Now you need to add the following two classes, the first one extends the DefaultLinqToHqlGeneratorsRegistry and then implement our AddHoursGenerator method.

public class MyLinqtoHqlGeneratorsRegistry : 
    DefaultLinqToHqlGeneratorsRegistry
{
    public MyLinqtoHqlGeneratorsRegistry()
    {
        this.Merge(new AddHoursGenerator());
    }
}

public class AddHoursGenerator : BaseHqlGeneratorForMethod
{
    public AddHoursGenerator()
    {
        SupportedMethods = new[] {
        ReflectionHelper.GetMethodDefinition<DateTime?>(d =>      
                d.Value.AddHours((double)0))
          };
    }

    public override HqlTreeNode BuildHql(MethodInfo method,
      System.Linq.Expressions.Expression targetObject,
      ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
      HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
        return treeBuilder.MethodCall("AddHours",
                visitor.Visit(targetObject).AsExpression(),
                visitor.Visit(arguments[0]).AsExpression()
            );
    }
}

Now all you need to do is to add the CustomDialect to your configuration and tell NHibernate about your custom AddHours generator

var configure = new Configuration()
          .DataBaseIntegration(x => {
              x.Dialect<CustomDialect>();
              x.ConnectionStringName = "db";
          })
          .LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry()
          .CurrentSessionContext<WebSessionContext>();

Note: we add .LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry()

I have based this on this blog post by fabio.

You can now use your code as is:-

Session.Query<Item>.Where(x => x.Start.AddHours(3) > x.Finish );

This is also possible in 3.2 but the public override HqlTreeNode BuildHql(..) parameters are slightly different...