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();
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[]>();