更新时间:2023-02-02 22:41:48
I would say, that this has solution. We have to use a bit more complex SQL in fact. This approach I've already deeply explained here:
So, below is just a draft based on your subqery draft. What we are doing, is creating two subselects in fact (check the intended SQL here)
PlanVersion planVersion = null;
// the most INNER SELECT
var maxSubquery = QueryOver.Of<PlanVersion>()
.SelectList(l => l
.SelectGroup(item => item.ParentPlan.Id)
.SelectMax(item => item.ActiveFromDate)
)
// WHERE Clause
.Where(item => item.ParentPlan.Id == planVersion.ParentPlan.Id)
// HAVING Clause
.Where(Restrictions.EqProperty(
Projections.Max<PlanVersion>(item => item.ActiveFromDate),
Projections.Property(() => planVersion.ActiveFromDate)
));
// the middle SELECT
var successSubquery = QueryOver.Of<PlanVersion>(() => planVersion )
// the Plan ID
.Select(pv => pv.ParentPlan.Id)
.WithSubquery
.WhereExists(maxSubquery)
;
having this subqueries, we can ask for plan itself:
// the most outer SELECT
var query = session.QueryOver<Plan>()
.WithSubquery
.WhereProperty(p => p.Id)
.In(successSubquery)
.List<Plan>();
There could be some minor typos, but the draft should give you clear answer how to...