NHibernate comparison constraint to a coalesced date

Question

There is a great Q/A to prefacing this question here: NHibernate COALESCE issue

I need to be able to compare a date object to a date value from within an inner join. The unfamiliar territory here has been the implementation of this COALESCE along with the date LT constraint

Here is my current SQL query

SELECT DISTINCT Sites.*
FROM Sites
 INNER JOIN Sites_WF_Info
       ON Site_Key = SiteWFInfo_Site_Key
       AND SiteWFInfo_Effective_Date <= @today
       AND @today <= SiteWFInfo_End_Date
 INNER JOIN Profit_Centers
       ON Site_Key = ProfCtr_Site_Key
       AND ProfCtr_Open_Date <= @today
       AND @today < Coalesce(ProfCtr_Close_Date, '6/6/2079')

What I would like to know is how to go about using a constant in place of the ExpenseReport.PeriodFrom property ==> left.

Ideally, I would like to set left/right

// DateTime effDate is passed in
var left = Projections.Property<DateTime>(effDate);
var right = Projects.SqlFunction("COALESCE",
            NHibernateUtil.DateTime,
            Projections.Constant(DateTime.Parse("6/6/2079").Date, NHibernateUtil.DateTime),
            Projections.Property<ProfitCenter>(pc => pc.CloseDate));

Then, when the restriction is invoked

var restriction = Restrictions.LtProperty(left, right);

So that when I build the QueryOver<> I can substitute this restriction object for one of the Where clauses

var foo = CurrentSession().QueryOver<Site>(() => sa)
    .Inner.JoinQueryOver<ProfitCenter>(() => pca)
    .Where(restriction)

---

THE FINAL ANSWER

This required the introduction of a new 'flattened' domain 'ResultModel' (SiteWithWindowsTimezoneId) so that I could return a more specific model from my query and avoid lazy loading all the other stuff that is currently associated with Site. This new query style has turned a 16+ sql query method down to a single query. The savings was worth the time. Thanks again for your help. I hope that this gist is helpful to someone in the future.

SiteWorkforceInfo swia = null;
SiteWorkforceConfig swcfg = null;
ProfitCenter pca = null;
Site sa = null;
SiteWithWindowsTimezoneId siteResult = null;

var leftProfCloseDate = Projections.Constant(effectiveDate);
var rightProfCloseDate = Projections.SqlFunction("COALESCE", 
    NHibernateUtil.DateTime, 
    Projections.Property<ProfitCenter>(pc => pc.CloseDate),
    Projections.Constant(DateTime.Parse("6/6/2079").Date, NHibernateUtil.DateTime)
);

var profCloseDateRestriction = Restrictions.LtProperty(leftProfCloseDate, rightProfCloseDate);

var activeSites = CurrentSession().QueryOver<SiteWorkforceInfo>(() => swia)
    .Inner.JoinQueryOver<Site>(() => swia.Site, () => sa)
    .Left.JoinQueryOver<SiteWorkforceConfig>(() => sa.SiteWFConfig, () => swcfg)
    .Inner.JoinQueryOver<ProfitCenter>(() => sa.ProfitCenters, () => pca)
    .Where(() => swia.EffectiveDate <= effectiveDate)
    .Where(() => effectiveDate <= swia.EndDate)
    .Where(() => pca.OpenDate <= effectiveDate)
    .Where(profCloseDateRestriction)
    .Where(() => swia.TimeCaptureRule > 0)
    .SelectList(
        list => list
            .Select(() => sa.Key).WithAlias(() => siteResult.Key)
            .Select(() => sa.Id).WithAlias(() => siteResult.Id)
            .Select(() => sa.IdFormatted).WithAlias(() => siteResult.IdFormatted)
            .Select(() => sa.Description).WithAlias(() => siteResult.Description)
            .Select(() => swcfg.WindowsTimezoneId).WithAlias(() => siteResult.WindowsTimezoneId)
                )
    .TransformUsing(Transformers.AliasToBean<SiteWithWindowsTimezoneId>())
    .List<SiteWithWindowsTimezoneId>();

return activeSites;

--- The Resulting query ---

SELECT sa1_.Site_Key as y0_, 
    sa1_.Site_Id as y1_, 
    sa1_.Site_Id_Formatted as y2_, 
    sa1_.Site_Description as y3_, 
    swcfg2_.SiteWFCfg_Windows_Timezone_Id as y4_ 
FROM Sites_WF_Info this_ 
inner join Sites sa1_ 
    on this_.SiteWFInfo_Site_Key=sa1_.Site_Key 
inner join Profit_Centers pca3_ 
    on sa1_.Site_Key=pca3_.ProfCtr_Site_Key 
left outer join Sites_WF_Configuration swcfg2_ 
    on sa1_.Site_Key=swcfg2_.SiteWFCfg_Site_Key 
WHERE this_.SiteWFInfo_Effective_Date <= @p0 
    and @p1 <= this_.SiteWFInfo_End_Date 
    and pca3_.ProfCtr_Open_Date <= @p2 
    and @p3 < coalesce(pca3_.ProfCtr_Close_Date, @p4) 
    and this_.SiteWFInfo_TimeCapRule_Key > @p5

Answer 1

If I do understand correctly, we need to replace the left (currently property projection) with the effDate constant projection. Then we can do it like this

// instead of this
// var left = Projections.Property<DateTime>(effDate);

// we would use this
// DateTime effDate is passed in
var effDate = DateTime.Today.Date; // C#'s today
var left = Projections.Constant(effDate);

And also, we should switch (change the order) of our "COALESCE", because the property should go first: Coalesce(ProfCtr_Close_Date, '6/6/2079')

var right = Projects.SqlFunction("COALESCE",
    NHibernateUtil.DateTime,

    // As DOC of COALESCE says:
    // "Evaluates the arguments in order and returns the current value of 
    //  the first expression that initially does not evaluate to NULL."

    Projections.Property<ProfitCenter>(pc => pc.CloseDate), 
    Projections.Constant(DateTime.Parse("6/6/2079").DateNHibernateUtil.DateTime)
    );

Finally, we should use the same alias for the joined columns. Let's adjust main query a bit:

var foo = CurrentSession().QueryOver<Site>(() => sa)
    .Inner.JoinQueryOver<ProfitCenter>(() =>  sa.ProfitCenter, () => pca)
    .Where(restriction)

And the right side should use pca as well

var right = Projects.SqlFunction("COALESCE",
    NHibernateUtil.DateTime,
    // be sure that column goes to correct table
    // ==> use the same alias
    Projections.Property(() => pca.CloseDate), 
    Projections.Constant(DateTime.Parse("6/6/2079").DateNHibernateUtil.DateTime)
    );