I have a plan table which is having the following structure
id plan_id plan_number
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
for each plan I would like to get the plan_id with the highest plan_number as follow
id plan_id plan_number
3 1 3
5 2 2
I need to have a restriction on the plan_id, only for certain plan_id I would like to get results.
I can do this with the following SQL query
SELECT
plan.*
FROM
plan,
(
SELECT
MAX(plan_number) as plan_number
,plan_id
FROM
plan
WHERE
plan_id in (1, 2)
GROUP BY
plan_id
) as max_plan_number
WHERE plan.plan_id = maxdates.plan_id
AND plan.plan_number = maxdates.plan_number
I would like to convert this to NHibernate but the following is not working.
Plan planAlias = null;
Plan subqueryAlias = null;
var subquery = QueryOver.Of<Plan>(() => subqueryAlias)
.WhereRestrictionOn(() => subqueryAlias.PlanId).IsIn(new[] { 1, 2 })
.SelectList(list => list
.SelectGroup(() => subqueryAlias.PlanId)
.SelectMax(() => subqueryAlias.PlanNumber).WithAlias(() => subqueryAlias.PlanNumber)
)
.Select(Projections.ProjectionList()
.Add(Projections.Group(() => subqueryAlias.PlanId))
.Add(Projections.Max(() => subqueryAlias.PlanNumber))
);
var query = Session.QueryOver<Plan>(() => planAlias)
.WithSubquery.WhereProperty(() => planAlias.PlanId).In(
QueryOver.Of<Plan>(() => subqueryAlias)
.WhereRestrictionOn(() => subqueryAlias.PlanId).IsIn(new[] { 1, 2 })
.Select(Projections.ProjectionList()
.Add(Projections.Group(() => subqueryAlias.PlanId))
.Add(Projections.Max(() => subqueryAlias.PlanNumber))
)
)
.Where(() => planAlias.PlanNumber == subqueryAlias.PlanNumber)
.List<Plan>();
I expect to get the following results
id plan_id plan_number
3 1 3
5 2 2
However NHibernate is currently giving the error
could not resolve property: subqueryAlias of: Model.Pla
Not sure how to link the PlanId and PlanNumber from the subquery within the main query to get the correct results.