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...

NHibernate 3.3 and medium trust

With the new release of NHibernate 3.3 it seems from my early testing, that the Medium Trust issues have now disappeared and its good to see NHibernate 3.3 works in Medium Trust environments. I need to play with the NH Linq provider more to see what fails but from what I have seen so far I have to congratulate the NH development Team.

As a side note, if anyone is wondering how I test this then please download the modified medium trust file (called policy.config from Rackspace cloud) unzip it and save it to your web root folder.

Next simply add this to your web.config:-

<system.web>
  <securitypolicy>
    <trustlevel name="Custom" policyFile="policy.config" />
  </securityPolicy>
  <trust level="Custom" originUrl="" />
</system.web>

You now have a local development system running under medium trust. For those that do not run Rackspace cloud I would recommend asking your hosting provider for their medium trust policy file.

This is an update from a previous post when using NHibernate 3.2 (September 2011). The JIRA issue can be found here.