Limiting NHibernate comparison with merged date

There is a great question in this question: imagine this question: NHibernate COALESCE problem

I need to be able to compare a date object with a date value from an inner join. Unfamiliar territory here was the implementation of this COALESCE along with the LT date limit

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')

      

        
        
        
      

    

I would like to know how to use a constant instead of the ExpenseReport.PeriodFrom ==> left property.

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 constraint is called

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

      

        
        
        
      

    

So when I create a QueryOver <> I can replace that constraint object for one of the Where clauses

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

      

        
        
        
      

    

FINAL ANSWER

This required the introduction of a new "flattened" domain "ResultModel" (SiteWithWindowsTimezoneId) so that I could return a more specific model from my request and avoid lazy loading all other things currently related to the site. This new query style turned the 16 + sql query method into a single query. The savings were worth the time. Thanks again for your help. I hope 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;

      

        
        
        
      

    

--- Result request ---

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