NHibernate对合并日期的比较约束 [英] NHibernate comparison constraint to a coalesced date
问题描述
在这里提出这个问题有一个很好的问答: NHibernate COALESCE问题
There is a great Q/A to prefacing this question here: NHibernate COALESCE issue
我需要能够将日期对象与内部联接中的日期值进行比较.这里不熟悉的领域是该COALESCE的实现以及日期LT约束
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
这是我当前的SQL查询
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')
我想知道的是如何使用常量代替ExpenseReport.PeriodFrom属性==>左.
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));
然后，当调用限制时
var restriction = Restrictions.LtProperty(left, right);
因此，当我构建QueryOver<>时，可以将此 restriction 对象替换为Where子句之一
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)
最终答案
这需要引入新的扁平化"域"ResultModel"(SiteWithWindowsTimezoneId)，以便我可以从查询中返回更具体的模型，并避免延迟加载当前与Site关联的所有其他内容.这种新的查询样式已将16个以上的sql查询方法变为单个查询.节省是值得的.再次感谢你的帮助.我希望这个要点对将来的某个人有帮助.
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
推荐答案
如果我正确理解，我们需要将left (当前为属性投影)替换为effDate常数投影.然后我们可以这样做
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);
而且，我们应该切换"COALESCE"的(更改顺序)，因为该属性应该排在首位:Coalesce(ProfCtr_Close_Date，'6/6/2079')
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)
右侧也应使用pca
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)
);
这篇关于NHibernate对合并日期的比较约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
