NHibernate QueryOver with MaxResult, Group By and Order By

Question

I'm trying to convert a SQL query to NHibernate QueryOver syntax, but I don't understand how to sort by the count projection.

This is what the SQL Query looks like:

select top 10 v.intVoteUserID, COUNT(v.intVoteUserID)
from Group_MessageVotes v
where v.dtmVote > :date
group by v.intVoteUserID
order by COUNT(v.intVoteUserID) desc

Any ideas?

Florian Lim · Accepted Answer · 2011-04-20T07:46:09.363

21

You can simply repeat the projection in the OrderBy-clause.

The following query will give you an IList<object[]> where the first element of each item is the id and the second is the count.

var result = session.QueryOver<GroupMessageVotes>()
.Select(
    Projections.Group<GroupMessageVotes>(e => e.intVoteUserID),
    Projections.Count<GroupMessageVotes>(e => e.intVoteUserID)
    )
.OrderBy(Projections.Count<GroupMessageVotes>(e => e.intVoteUserID)).Desc
.Take(10)
.List<object[]>();

edited Apr 20 '11 at 07:46

answered Apr 19 '11 at 09:42

Florian Lim

5,332
2
27
28

Are all these generic arguments required? – Stefan Steinegger Apr 19 '11 at 10:08
1

@Stefan Steinegger I think they are required for the lambda expressions. It is possible to write `Projections.Count("intVoteUserID")` instead, but I prefer the first option. – Florian Lim Apr 19 '11 at 10:26
It should be possible to write `Projections.Count(e => e.intVoteUserID)`. – Stefan Steinegger Apr 19 '11 at 16:47
@Stefan Steinegger Yes, you are right. Funny that I didn't see that before. I fixed my answer to accomodate that. – Florian Lim Apr 20 '11 at 07:33
Hm, the signature doesn't match. It`s `Count(Expression> expression);` instead of `Count(Expression> expression);` – Florian Lim Apr 20 '11 at 07:45

NHibernate QueryOver with MaxResult, Group By and Order By

1 Answers1

Linked