Nhibernate LINQ DateTime.AddDay does not work

Question

I need to compare two DateTime properties in a linq query, similar to the one below -

var patients = from c in session.Query<Patient>() where c.DateAdded.AddDays(1) < c.AdmitDate select c;

when I run the query I get this exception: System.NotSupportedException {"System.DateTime AddDays(Double)"}

at NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitMethodCallExpression(MethodCallExpression expression)

I took a look at Fabio's article on http://fabiomaulo.blogspot.com/2010/07/nhibernate-linq-provider-exten... but the treeBuilder doesn't have any functions that are specific to DateTime comparisons.

Here is the code for the sample. To run this , install NuGet packages for FluentNhibernate and SQLite.

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;
using NHibernate.Linq;

namespace ConsoleApplication1
{
    class Program
    {
            private static Configuration _config;

            static void Main(string[] args)
            {
                    var sessionFactory = CreateSessionFactory();
                    using (var session = sessionFactory.OpenSession())
                    {
                            BuildSchema(session);
                            using(var transaction = session.BeginTransaction())
                            {
                                    var foo = new Patient
                                    {
                                            Name = "Foo",
                                            Sex = Gender.Male,
                                            DateAdded = new DateTime(2009, 1, 1),
                                            AdmitDate = new DateTime(2009, 1, 2)
                                    };
                                    var bar = new Patient
                                    {
                                            Name = "Bar",
                                            Sex = Gender.Female,
                                            DateAdded = new DateTime(2009, 1, 1),
                                            AdmitDate = new DateTime(2009, 1, 2)
                                    };
                                    session.SaveOrUpdate(foo);
                                    session.SaveOrUpdate(bar);
                                    transaction.Commit();
                            }
                            session.Flush();

                            using (session.BeginTransaction())
                            {
                                    var cats = from c in session.Query<Patient>() where
c.DateAdded.AddDays(1) < c.AdmitDate select c;
                                    foreach (var cat in cats)
                                    {
                                            Console.WriteLine("patient name {0}, sex    {1}", cat.Name,
cat.Sex);
                                    }
                            }
                    }
                    Console.ReadKey();
            }

            private static ISessionFactory CreateSessionFactory()
            {
                    return Fluently.Configure()
                      .Database(
                            SQLiteConfiguration.Standard.InMemory()
                      )
                      .Mappings(m =>
                            m.FluentMappings.AddFromAssemblyOf<Program>())
                      .ExposeConfiguration(c => _config = c)
                      .BuildSessionFactory();
            }

            private static void BuildSchema(ISession session)
            {
                    new SchemaExport(_config)
                      .Execute(true, true, false, session.Connection, null);
            }
    }

    public class PatientMap : ClassMap<Patient>
    {
            public PatientMap()
            {
                    Id(x => x.Id);
                    Map(x => x.Name)
                      .Length(16)
                      .Not.Nullable();
                    Map(x => x.Sex);
                    Map(x => x.DateAdded);
                    Map(x => x.AdmitDate);
            }
    }

    public class Patient
    {
            public virtual int Id { get; set; }
            public virtual string Name { get; set; }
            public virtual Gender Sex { get; set; }
            public virtual DateTime DateAdded { get; set; }
            public virtual DateTime AdmitDate { get; set; }
    }

    public enum Gender
    {
            Male,
            Female
    } 

Thanks, Vikram

Answer 1

Answer to the above question:

using System;
using System.Linq;
using System.Reflection;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Dialect;
using NHibernate.Dialect.Function;
using NHibernate.Hql.Ast;
using NHibernate.Linq.Functions;
using NHibernate.Tool.hbm2ddl;
using NHibernate.Linq;
using System.Collections.ObjectModel;
using System.Linq.Expressions;
using NHibernate.Linq.Visitors;
using NHibernate.Cfg.Loquacious;

namespace ConsoleApplication1
{
class Program
{
    private static Configuration _config;

    static void Main(string[] args)
    {App_Start.NHibernateProfilerBootstrapper.PreStart();

        var sessionFactory = CreateSessionFactory();
        using (var session = sessionFactory.OpenSession())
        {
            BuildSchema(session);
            using(var transaction = session.BeginTransaction())
            {
                var foo = new Patient
                {
                    Name = "Foo", 
                    Sex = Gender.Male, 
                    DateAdded = new DateTime(2009, 1, 4), 
                    AdmitDate = new DateTime(2009, 1, 6)
                };
                var bar = new Patient
                {
                    Name = "Bar", 
                    Sex = Gender.Female, 
                    DateAdded = new DateTime(2009, 1, 1), 
                    AdmitDate = new DateTime(2009, 1, 2)
                };
                session.SaveOrUpdate(foo);
                session.SaveOrUpdate(bar);
                transaction.Commit();
            }
            session.Flush();

            using (session.BeginTransaction())
            {
                //x.PatientVisit.AdmitDate.Value.Date == x.DateAdded.Date
                var patients = from c in session.Query<Patient>() where c.DateAdded.AddDays(1) < c.AdmitDate.Value select c; 
                foreach (var cat in patients)
                {
                    Console.WriteLine("patient name {0}, sex  {1}", cat.Name, cat.Sex);
                }
            }
        }
        Console.ReadKey();
    }

    private static ISessionFactory CreateSessionFactory()
    {
        return Fluently.Configure()
            .Database(
                MsSqlConfiguration.MsSql2008.Dialect<CustomDialect>()
                    .ConnectionString("Data Source=.;Initial Catalog=testdb;Integrated Security=True;Connection Reset=false")
            )
            .Mappings(m =>
                      m.FluentMappings.AddFromAssemblyOf<Program>())
            .ExposeConfiguration(c =>
                                    {
                                        c.LinqToHqlGeneratorsRegistry<ExtendedLinqtoHqlGeneratorsRegistry>();
                                        _config = c;
                                    })
            .BuildSessionFactory();
    }

    private static void BuildSchema(ISession session)
    {
        new SchemaExport(_config)
          .Execute(true, true, false, session.Connection, null);
    }
}

public class PatientMap : ClassMap<Patient>
{
    public PatientMap()
    {
        Id(x => x.Id);
        Map(x => x.Name)
          .Length(16)
          .Not.Nullable();
        Map(x => x.Sex);
        Map(x => x.DateAdded);
        Map(x => x.AdmitDate);
    }
}

public class Patient
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Gender Sex { get; set; }
    public virtual DateTimeOffset DateAdded { get; set; }
    public virtual DateTime? AdmitDate { get; set; }
}

public enum Gender
{
    Male,
    Female
}

public class ExtendedLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
    public ExtendedLinqtoHqlGeneratorsRegistry()
    {
        this.Merge(new AddDaysGenerator());
    }
}

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

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

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