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";
    }
}

Lanthanum

Another downvote maniac? I've done my research, I've actually come up with a working solution, I've clearly stated my problem and even provided an example. As if -2 rep actually means something! – Lanthanum

I fully agree with the above comment. All I needed was to create a new console project, install two NuGet packages, copy/paste the provided code and start playing with it. No guesses, no typos, everything compiles/does exactly what is explained in the post or commented in the code. It's really exceptional to find such good mcve here for a non trivial issue. – Honorific

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.

Honorific

Awesome! And by removing generics, I can actually name it Coalesce with no name conflicts, and have NHibernateUtil.Date instead of NHibernateUtil.Object (although it works with either). The trade-off is that I have to create overloads for every type I need, but at the time it's only one. – Lanthanum

Interesting idea, I didn't think in that direction. A little bit annoying if you need to do it for many types, but works! Actually I have another solution, but it's a bit hacky. Instead of custom extension methods, just replace the handler for the two Coalesce methods (they are registered the same way). But in order to remove the previous handlers we need to access their private dictionary via reflection. And of course you can request/suggest NHibernate fix (will take time), apply the fix in the source code and use custom NHibernate build (maintenance, updates) etc. A lot of choices :-) – Honorific