fluent-nhibernatequeryover

Select data from a sub-select


This is what I want to accomplish using NHibernate QueryOver:

SELECT TOP 10 * from 
            (SELECT aColumn, min(WorkingDay) as d
                FROM   aTable
                GROUP  BY aColumn) as a
    ORDER  BY a.d asc

It is basically a Group By with an Order By around it.

I know I probably are going to need a sub-query like this:

var subquery = QueryOver<aTable>.Of(() => alias)
    .Select(
    Projections.ProjectionList()
    .Add(Projections.Group<aTable>(p => p.aColumn).WithAlias(() => alias.aColumn))
    .Add(Projections.Min(() => alias.WorkingDay).WithAlias(() => alias.WorkingDay))
    );

But I can't figure out how to build a select around that.

Any Help will be appreciated, thank you.

UPDATE:

With help from Firo, this is the final query:

        aTable t = null;
        var min = Projections.Min<aTable>(p => p.WorkingDay).WithAlias(() => t.WorkingDay);
        items = await s.QueryOver<aTable>(() => t)
            .WhereRestrictionOn(f => f.State).IsIn(states)
            .Select(Projections.ProjectionList()
                .Add(Projections.Group<aTable>(p => p.aColumn).WithAlias(() => t.aColumn))
                .Add(min)
                )
            .OrderBy(min).Asc
            .TransformUsing(NHibernate.Transform.Transformers.AliasToBean<aTable>())
            .Take(10)
            .ListAsync();

Solution

  • var min = Projections.Min<aTable>(p => p.WorkingDay);
    var result = session.QueryOver<aTable>()
        .Select(Projections.ProjectionList()
            .Add(Projections.Group<aTable>(p => p.aColumn))
            .Add(min))
        .OrderBy(min).Asc
        .List<object[]>();