NHibernate QueryOver Coalesce a property to another property

Question

Consider this silly domain:

namespace TryHibernate.Example
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class WorkItem
    {
        public int Id { get; set; }
        public string Description { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
    }

    public class Task
    {
        public int Id { get; set; }
        public Employee Assignee { get; set; }
        public WorkItem WorkItem { get; set; }
        public string Details { get; set; }
        public DateTime? StartDateOverride { get; set; }
        public DateTime? EndDateOverride { get; set; }
    }
}

The idea is that each work item may be assigned to multiple employees with different details, potentially overriding start/end dates the of work item itself. If those overrides are null, they should be taken from the work item instead.

Now I'd like to perform a query with restrictions on the effective dates. I've tried this first:

IList<Task> tasks = db.QueryOver<Task>(() => taskAlias)
    .JoinAlias(() => taskAlias.WorkItem, () => wiAlias)
    .Where(() => taskAlias.StartDateOverride.Coalesce(() => wiAlias.StartDate) <= end)
    .And(() => taskAlias.EndDateOverride.Coalesce(() => wiAlias.EndDate) >= start)
    .List();

Unfortunately, it doesn't compile as Coalesce expects a constant, not a property expression.

OK, I've tried this:

    .Where(() => (taskAlias.StartDateOverride == null
                  ? wiAlias.StartDate
                  : taskAlias.StartDateOverride) <= end)
    .And(() => (taskAlias.EndDateOverride == null
                  ? wiAlias.EndDate
                  : taskAlias.EndDateOverride) >= start)

This throws NullReferenceException. Not sure why, but probably either because NHibernate doesn't properly translate that ternary operator (and tries to actually invoke it instead) or because == null isn't exactly the right way to check for nulls. Anyway, I didn't even expect it to work.

Finally, this one works:

IList<Task> tasks = db.QueryOver<Task>(() => taskAlias)
    .JoinAlias(() => taskAlias.WorkItem, () => wiAlias)
    .Where(Restrictions.LeProperty(
        Projections.SqlFunction("COALESCE", NHibernateUtil.DateTime,
            Projections.Property(() => taskAlias.StartDateOverride),
            Projections.Property(() => wiAlias.StartDate)),
        Projections.Constant(end)))
    .And(Restrictions.GeProperty(
        Projections.SqlFunction("COALESCE", NHibernateUtil.DateTime,
            Projections.Property(() => taskAlias.EndDateOverride),
            Projections.Property(() => wiAlias.EndDate)),
        Projections.Constant(start)))
    .List();

But there is no way I can call that clean code. Maybe I can extract certain expressions into separate methods to clean it up a little bit, but it would be much better to use expression syntax rather than these ugly projections. Is there a way to do it? Is there any reason behind NHibernate not supporting property expressions in the Coalesce extension?

One obvious alternative is to select everything and then filter results using Linq or whatever. But it could become a performance problem with large number of total rows.

Here is full code in case someone wants to try it:

using (ISessionFactory sessionFactory = Fluently.Configure()
    .Database(SQLiteConfiguration.Standard.UsingFile("temp.sqlite").ShowSql())
    .Mappings(m => m.AutoMappings.Add(
        AutoMap.AssemblyOf<Employee>(new ExampleConfig())
            .Conventions.Add(DefaultLazy.Never())
            .Conventions.Add(DefaultCascade.All())))
    .ExposeConfiguration(c => new SchemaExport(c).Create(true, true))
    .BuildSessionFactory())
{
    using (ISession db = sessionFactory.OpenSession())
    {
        Employee empl = new Employee() { Name = "Joe" };
        WorkItem wi = new WorkItem()
        {
            Description = "Important work",
            StartDate = new DateTime(2016, 01, 01),
            EndDate = new DateTime(2017, 01, 01)
        };
        Task task1 = new Task()
        {
            Assignee = empl,
            WorkItem = wi,
            Details = "Do this",
        };
        db.Save(task1);
        Task task2 = new Task()
        {
            Assignee = empl,
            WorkItem = wi,
            Details = "Do that",
            StartDateOverride = new DateTime(2016, 7, 1),
            EndDateOverride = new DateTime(2017, 1, 1),
        };
        db.Save(task2);
        Task taskAlias = null;
        WorkItem wiAlias = null;
        DateTime start = new DateTime(2016, 1, 1);
        DateTime end = new DateTime(2016, 6, 30);
        IList<Task> tasks = db.QueryOver<Task>(() => taskAlias)
            .JoinAlias(() => taskAlias.WorkItem, () => wiAlias)
            // This doesn't compile:
            //.Where(() => taskAlias.StartDateOverride.Coalesce(() => wiAlias.StartDate) <= end)
            //.And(() => taskAlias.EndDateOverride.Coalesce(() => wiAlias.EndDate) >= start)
            // This throws NullReferenceException:
            //.Where(() => (taskAlias.StartDateOverride == null ? wiAlias.StartDate : taskAlias.StartDateOverride) <= end)
            //.And(() => (taskAlias.EndDateOverride == null ? wiAlias.EndDate : taskAlias.EndDateOverride) >= start)
            // This works:
            .Where(Restrictions.LeProperty(
                Projections.SqlFunction("COALESCE", NHibernateUtil.DateTime,
                    Projections.Property(() => taskAlias.StartDateOverride),
                    Projections.Property(() => wiAlias.StartDate)),
                Projections.Constant(end)))
            .And(Restrictions.GeProperty(
                Projections.SqlFunction("COALESCE", NHibernateUtil.DateTime,
                    Projections.Property(() => taskAlias.EndDateOverride),
                    Projections.Property(() => wiAlias.EndDate)),
                Projections.Constant(start)))
            .List();
        foreach (Task t in tasks)
            Console.WriteLine("Found task: {0}", t.Details);
    }
}

And the configuration is really simple:

class ExampleConfig : DefaultAutomappingConfiguration
{
    public override bool ShouldMap(Type type)
    {
        return type.Namespace == "TryHibernate.Example";
    }
}

Answer 1

Let start with this:

// This doesn't compile:
//.Where(() => taskAlias.StartDateOverride.Coalesce(() => wiAlias.StartDate) <= end)
//.And(() => taskAlias.EndDateOverride.Coalesce(() => wiAlias.EndDate) >= start)

and modify it to:

.Where(() => taskAlias.StartDateOverride.Coalesce(wiAlias.StartDate) <= end)
.And(() => taskAlias.EndDateOverride.Coalesce(wiAlias.EndDate) >= start)

now it will compile. But at runtime it generates the same NullReferenceException. No good.

It turns out that NHibernate indeed tries to evaluate the Coalesce argument. This can easily be seen by looking at ProjectionExtensions class implementation. The following method handles the Coalesce translation:

internal static IProjection ProcessCoalesce(MethodCallExpression methodCallExpression)
{
  IProjection projection = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
  object obj = ExpressionProcessor.FindValue(methodCallExpression.Arguments[1]);
  return Projections.SqlFunction("coalesce", (IType) NHibernateUtil.Object, projection, Projections.Constant(obj));
}

Notice the different handling of the first argument (FindMemberExpresion) vs second argument (FindValue). Well, FindValue simply tries to evaluate the expression.

Now we know what is causing the issue. I have no idea why it is implemented that way, so will concentrate on finding a solution.

Fortunately, the ExpressionProcessor class is public and also allows you to register a custom methods via RegisterCustomMethodCall / RegisterCustomProjection methods. Which leads us to the solution:

• Create a custom extensions methods similar to Coalesce (let call them IfNull for instance)
• Register a custom processor
• Use them instead of Coalesce

Here is the implementation:

public static class CustomProjections
{
    static CustomProjections()
    {
        ExpressionProcessor.RegisterCustomProjection(() => IfNull(null, ""), ProcessIfNull);
        ExpressionProcessor.RegisterCustomProjection(() => IfNull(null, 0), ProcessIfNull);
    }

    public static void Register() { }

    public static T IfNull<T>(this T objectProperty, T replaceValueIfIsNull)
    {
        throw new Exception("Not to be used directly - use inside QueryOver expression");
    }

    public static T? IfNull<T>(this T? objectProperty, T replaceValueIfIsNull) where T : struct
    {
        throw new Exception("Not to be used directly - use inside QueryOver expression");
    }

    private static IProjection ProcessIfNull(MethodCallExpression mce)
    {
        var arg0 = ExpressionProcessor.FindMemberProjection(mce.Arguments[0]).AsProjection();
        var arg1 = ExpressionProcessor.FindMemberProjection(mce.Arguments[1]).AsProjection();
        return Projections.SqlFunction("coalesce", NHibernateUtil.Object, arg0, arg1);
    }
}

Since these methods are never called, you need to ensure the custom processor is registered by calling Register method. It's an empty method just to make sure the static constructor of the class is invoked, where the actual registration happens.

So in your example, include at the beginning:

CustomProjections.Register();

then use inside the query:

.Where(() => taskAlias.StartDateOverride.IfNull(wiAlias.StartDate) <= end)
.And(() => taskAlias.EndDateOverride.IfNull(wiAlias.EndDate) >= start)

and it will work as expected.

P.S. The above implementation works for both constant and expression arguments, so it's really a safe replacement of the Coalesce.