NHibernate QueryOver group by without selecting the grouped by column

Question

Having a query like the following:

var subquery = SessionFactory.GetCurrentSession()
    .QueryOver<SomeEntity>()
    .Where(_ => _.SomeOtherEntity.Id == someId)
    .SelectList(list => list
        .SelectGroup(x => x.SomeGroupByProperty)
        .SelectMax(x => x.MaxPerGroupProperty))
    .List<dynamic>();

The generated sql is selecting both SomeGroupByProperty and maximum of MaxPerGroupProperty. Is it possible to get it to group on SomeGroupByProperty but only select maximum of MaxPerGroupProperty? This is for using the subquery result with a contains in parent query.

Answer 1

It's an open issue in NHibernate jira (criteria query): https://nhibernate.jira.com/browse/NH-1426

You can do it like this though

var subquery =
    QueryOver.Of<SomeEntity>()
        .Where(_ => _.SomeOtherEntity.Id == someId)
        .Select(
            Projections.ProjectionList()
                .Add(Projections.SqlGroupProjection("max(MaxPerGroupProperty) as maxAlias", "SomeGroupByProperty",
                    new string[] { "maxAlias" }, new IType[] { NHibernate.NHibernateUtil.Int32 })));

var parentQuery = session.QueryOver<SomeEntity2>()
    .WithSubquery.WhereProperty(x => x.MaxPerGroupPropertyReference).In(subquery).List();

Not quite as pretty as using the entity properties, but it does work.