NHibernate QueryOver equivalent

Question

I would appreciate some help on writing the NHibernate QueryOver equivalent for the below mentioned SQL:

select sum(s.StudentCount),sum(w.TotalEarningsAmount),
avg(w.TotalEarningsAmount),count(w.TotalEarningsAmount) 
from School s inner join s.Earnings w where s.Active = 1"

Owww projections, nasty. I'd have to sit down and figure this one out. — Phill, Dec 04 '12 at 15:29

score 3 · Accepted Answer · answered Dec 17 '12 at 17:40

Earning w = null; //alias variable
Dto dto = null; //make a dto object

var dtoList = Session.QueryOver<School>()
    .JoinAlias(x => x.Earnings, () => w)
    .Where(x => x.isActive)
    .SelectList(list => list
        .SelectSum(x => x.StudentCount).WithAlias(() => dto.StudentCountSum)
        .SelectSum(() => w.TotalEarningsAmount).WithAlias(() => dto.TotalEarningsAmountSum)
        .SelectAvg(() => w.TotalEarningsAmount).WithAlias(() => dto.TotalEarningsAmountAvg)
        .SelectCount(() => w.TotalEarningsAmount).WithAlias(() => dto.TotalEarningsAmountCount))
    .TransformUsing(Transformers.AlaisToBean<Dto>())
    .List<Dto>();

NHibernate QueryOver equivalent

1 Answers1