NHibernate - DateTime.DayOfYear LINQ

Question

I am trying to execute the following LINQ query.

var tasks = session.Query<Task>()
    .Where(t => t.StartDate.DayOfYear == DateTime.UtcNow.DayOfYear)
    .ToList();

This doesn't work because DayOfYear isn't supported. However if I say:

var tasks = session.Query<Task>()
    .Where(t => t.StartDate.Day == DateTime.UtcNow.Day)
    .ToList();

It works fine. So I decided to look at the NHibernate source to see how they got it to work. I found the following line in the MsSql2000Dialect.cs (which the MsSql2008Dialect class inherits):

RegisterFunction("day",
    new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(day, ?1)"));

Therefore I created my own custom dialect (which inherits from MsSql2008Dialect) with the following line in the constructor:

RegisterFunction("dayofyear",
    new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(dy, ?1)"));

And then registered the custom dialect in my configuration but I still get the following error:

NHibernate.QueryException: could not resolve property: DayOfYear

I'd appreciate it if someone could show me what I'm doing wrong. Thanks

Answer 1

You've done half of the work.

You've told NHibernate how to convert a HQL dayofyear() method call into SQL Server dialect, but you didn't tell how to convert your LINQ expression into HQL. Basically you need to extend the LINQ provider. Let's do it!

First, we need to implement a new IHqlGeneratorForProperty. Just extend BaseHqlGeneratorForProperty this way:

public class DateTimeDayOfYearPropertyHqlGenerator : NHibernate.Linq.Functions.BaseHqlGeneratorForProperty
{
    public DateTimeDayOfYearPropertyHqlGenerator()
    {
        SupportedProperties = new[]
            {
                ReflectionHelper.GetProperty((DateTime x) => x.DayOfYear)
            };
    }

    public override NHibernate.Hql.Ast.HqlTreeNode BuildHql(MemberInfo member, Expression expression, NHibernate.Hql.Ast.HqlTreeBuilder treeBuilder, NHibernate.Linq.Visitors.IHqlExpressionVisitor visitor)
    {
        return treeBuilder.MethodCall("dayofyear", visitor.Visit(expression).AsExpression());
    }
}

Then we need to register this new generator somewhere. NHibernate uses DefaultLinqToHqlGeneratorsRegistry. Let's extend this class:

public class ExtendedLinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
    public ExtendedLinqToHqlGeneratorsRegistry()
    {
        this.Merge(new DateTimeDayOfYearPropertyHqlGenerator());
    }
}

Finally, we need to tell NHibernate to use the extended registry instead of the default one. If you're using Loquacious config, just do:

config.LinqToHqlGeneratorsRegistry<ExtendedLinqToHqlGeneratorsRegistry>();

Or if you're using FluentNHibernate:

...
.ExposeConfiguration(cfg =>
         cfg.SetProperty(NHibernate.Cfg.Environment.LinqToHqlGeneratorsRegistry,
         typeof(ExtendedLinqToHqlGeneratorsRegistry).AssemblyQualifiedName))

That should work now!