How to convert HQL with Group By to QueryOver?

Question

I have a HQL query:

select max(l.Num) from SomeTable l group by l.Type, l.Iteration

How can I translate/convert it to QueryOver?

Following one:

var grouped = session.QueryOver<SomeTable>()
    .SelectList(l => l
      .SelectGroup(x => x.Type)
      .SelectGroup(x => x.Iteration)
      .SelectMax(x => x.Num));

will generate SQL:

SELECT
    MAX(l.Num),
    l.Type,
    l.Iteration
FROM
    SomeTable l
GROUP BY
    l.Type,
    l.Iteration

which is not what I expect – I don’t want to have Type and Iteration in Select.

I'm using that query as subquery for select z from c where z IN (subquery).

Wanonah

AFAIK still not possible with queryover/criteria, see nhibernate.jira.com/browse/NH-1426 – Bartle

Answer 1

try with this statement, I've used Aliases and UnderlyingCriteria

SomeTable someTb = null;

var grouped = session.QueryOver<SomeTable>(() => someTb)
                .SelectList(l => l.SelectMax(() => someTb.lnum))
                .UnderlyingCriteria.SetProjection(
                                   Projections.Group(() => someTb.Type)
                                   ,Projections.Group(() => someTb.Iteration))
                .List();

I hope it's helpful.

Chiasma

@Feber - thanks, but this will not work. UnderlyingCriteria just enables to use criteria query in QueryOver. Additionally, query provided by you do: select type, iteration from SomeTable group by type, iteration - is not event selecting max value. – Wanonah