NHibernate linq provider datediff

Question

Is where a way to write something like this:

public class Item
{
    public DateTime Start { get; set; }
    public DateTime Finish{ get; set; }
}

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

Now i get an exception

[NotSupportedException: System.DateTime AddHours(Double)]

Answer 1

There is no easy way to make your LINQ query work. The problem with your scenario is that NHibernate doesn't know how to translate DateTime.AddHours(double hours) method. But can you use HQL to write a similar query? Apparently not. There is no standard HQL AddHours function. Therefore you have to register this new function. NHibernate uses dialects to translate between hql and vendor-specific SQL syntax. In order to do this you have to create a new dialect class deriving from an exising one and override RegisterFunctions method. But this solves only the first half of the problem. Next you have to show NHibernate how to use this function in LINQ. You have to "map" between DateTime.AddHours(double hours) method and the previosly registered custom hql function. NHibernate uses a registry for this purpose. You will have to extend the default linq-to-hql registry.

I will show an example that worked with NHibernate 3.3

Create a new dialect class (my example uses the predefined MsSql2008Dialect)

    public class EnhancedMsSql2008Dialect : MsSql2008Dialect
    {
        protected override void RegisterFunctions() {
            base.RegisterFunctions();
            RegisterFunction("add_hours", new SQLFunctionTemplate(NHibernateUtil.DateTime, "dateadd(hour, ?1, ?2)"));
        }
    }

Create a new LINQ-to-HQL generator class that knows how to translate AddHours method

    using NHibernate.Linq.Functions;
    using NHibernate.Linq;
    using NHibernate.Hql.Ast;

    public class DateTimeMethodsHqlGenerator : BaseHqlGeneratorForMethod
    {
        public DateTimeMethodsHqlGenerator() {
            SupportedMethods = new[] {
                ReflectionHelper.GetMethodDefinition((DateTime x) => x.AddHours(1))
            };
        }

        public override HqlTreeNode BuildHql(System.Reflection.MethodInfo method, System.Linq.Expressions.Expression targetObject, System.Collections.ObjectModel.ReadOnlyCollection arguments, HqlTreeBuilder treeBuilder, NHibernate.Linq.Visitors.IHqlExpressionVisitor visitor) {
            return treeBuilder.MethodCall("add_hours", visitor.Visit(arguments[0]).AsExpression(), visitor.Visit(targetObject).AsExpression());
        }
    }

Extend the default LINQ-to-HQL registry class

    public class EnhancedLinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
    {
        public EnhancedLinqToHqlGeneratorsRegistry() : base() {
            //
            RegisterGenerator(ReflectionHelper.GetMethodDefinition((DateTime x) => x.AddHours(1)), new DateTimeMethodsHqlGenerator());
        }
    }

Configure

    cfg.DataBaseIntegration(c => {
        c.Dialect<EnhancedMsSql2008Dialect>();
    });
    cfg.LinqToHqlGeneratorsRegistry<EnhancedLinqToHqlGeneratorsRegistry>();

Answer 2

If you are using NH 3.3 and Loquacious configuration then you can do something like this..

Add the LinqToHqlGeneratorsRegistry to the configuration:-

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

and add the following three classes:-

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()
            );
    }
}

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

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