c# - NHibernate QueryOver Coalesce a property to another property -

consider 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 each work item may assigned multiple employees different details, potentially overriding start/end dates of work item itself. if overrides null, should taken work item instead.

now i'd perform query restrictions on effective dates. i've tried 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, doesn't compile coalesce expects constant, not 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, either because nhibernate doesn't translate ternary operator (and tries invoke instead) or because == null isn't right way check nulls. anyway, didn't expect work.

finally, 1 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 no way can call clean code. maybe can extract expressions separate methods clean little bit, better use expression syntax rather these ugly projections. there way it? there reason behind nhibernate not supporting property expressions in coalesce extension?

one obvious alternative select , filter results using linq or whatever. become performance problem large number of total rows.

here full code in case wants 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)             // doesn't compile:             //.where(() => taskalias.startdateoverride.coalesce(() => wialias.startdate) <= end)             //.and(() => taskalias.enddateoverride.coalesce(() => wialias.enddate) >= start)             // throws nullreferenceexception:             //.where(() => (taskalias.startdateoverride == null ? wialias.startdate : taskalias.startdateoverride) <= end)             //.and(() => (taskalias.enddateoverride == null ? wialias.enddate : taskalias.enddateoverride) >= start)             // 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 configuration simple:

class exampleconfig : defaultautomappingconfiguration {     public override bool shouldmap(type type)     {         return type.namespace == "tryhibernate.example";     } } 

let start this:

// doesn't compile: //.where(() => taskalias.startdateoverride.coalesce(() => wialias.startdate) <= end) //.and(() => taskalias.enddateoverride.coalesce(() => wialias.enddate) >= start) 

and modify to:

.where(() => taskalias.startdateoverride.coalesce(wialias.startdate) <= end) .and(() => taskalias.enddateoverride.coalesce(wialias.enddate) >= start) 

now compile. @ runtime generates same nullreferenceexception. no good.

it turns out nhibernate indeed tries evaluate coalesce argument. can seen looking @ projectionextensions class implementation. following method handles 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 different handling of first argument (findmemberexpresion) vs second argument (findvalue). well, findvalue tries evaluate expression.

now know causing issue. have no idea why implemented way, concentrate on finding solution.

fortunately, expressionprocessor class public , allows register custom methods via registercustommethodcall / registercustomprojection methods. leads solution:

• create custom extensions methods similar coalesce (let call them ifnull instance)
• register custom processor
• use them instead of coalesce

here 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 used directly - use inside queryover expression");     }      public static t? ifnull<t>(this t? objectproperty, t replacevalueifisnull) t : struct     {         throw new exception("not 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 never called, need ensure custom processor registered calling register method. it's empty method make sure static constructor of class invoked, actual registration happens.

so in example, include @ beginning:

customprojections.register();

then use inside query:

.where(() => taskalias.startdateoverride.ifnull(wialias.startdate) <= end) .and(() => taskalias.enddateoverride.ifnull(wialias.enddate) >= start) 

and work expected.

p.s. above implementation works both constant , expression arguments, it's safe replacement of coalesce.