nHibernate aggregate query

Question

I'm attempting to put the following SQL query into an nHiberate QueryOver statement and I'm failing miserably.

Select top 15 percent
    People.personId
,People.Name
    ,SUM(Marks.Score)
from 
    People
    inner join [sessions] on [sessions].PersonId = People.PersonId
    inner join Marks on Marks.SessionId = [Sessions].SessionId
group by
    People.Name
    ,People.PersonId
order by
    SUM(Marks.Score) asc

My objects are:

Person
Session
Mark

With the obvious FK constraints (people --> Session --> Marks).

Might someone give me some advice?

Many thanks

score 4 · Accepted Answer · edited May 23 '17 at 12:27

joins are easy enough with .Join(). If you've defined those relations in your mappings, then you don't even have to specify how the join is made.
I usually find it more convenient to use JoinAlias. Aggregations are done using Projections (see aliases and projections here).

You'll need a simple sum projection, and a grouping projection, of course.

For the result, you can either use an object [] and cast it (as in the link above), or you can define a custom DTO, and use AliasToBeanResultTransformer. (here is a pretty old blog post; the syntax has now changed to allow for lambda expressions).

Concerning top 15 percent- I'm not sure.
QueryOver().Take() allows for a constant; not sure there's native support for percentages.
You could implement your own projection, though. see here.

Edit: a simple projection example
How lazy can I be? I just copied this from github.
It has joins, aliases, projections.. everything you need. I added a SUM projection just for fun.
Notice how all the other properties have to be grouped-by, of course:

Customer customer = null;
    Employee employee = null;
    OrderHeader orderHeader = null;

    var orderHeaders = Session.QueryOver<Order>()
        .JoinAlias(o => o.Customer, () => customer)
        .JoinAlias(o => o.Employee, () => employee)
        .SelectList(list => list
            .SelectGroup(o => o.OrderedOn).WithAlias(() => orderHeader.OrderedOn)
            .SelectGroup(o => customer.Name).WithAlias(() => orderHeader.Name)
            .SelectGroup(o => employee.FirstName).WithAlias(() => orderHeader.FirstName)
            .SelectGroup(o => employee.LastName).WithAlias(() => orderHeader.LastName)
            .SelectSum(o => employee.Salary).WithAlias(() => orderHeader.JhonnysProjectionExample))
        .TransformUsing(Transformers.AliasToBean<OrderHeader>())
        .List<OrderHeader>();

Any change of an example? Just of the aggregation. Pretty please :-) — Zen-C, Aug 17 '12 at 15:38

score 0 · Answer 2 · answered Aug 17 '12 at 14:25

0

TOP n PERCENT is a nonstandard SQL construct (a very useful one, I might add)

There's no support for that in any NH query method. Just keep it as SQL.

answered Aug 17 '12 at 14:25

Diego Mijelshon

52,548
16
116
154

nHibernate aggregate query

2 Answers2