0

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.

1
  • Try : var results = Session.GroupBy(x => x.PlanId).Max(x => x.PlanNumber).Select(x => new { id = x.Id, plan_id = x.PlanId, plan_Number = x.PlanNumber}).ToList()
    – jdweng
    Commented Jun 14 at 21:27

0

Browse other questions tagged or ask your own question.