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?

Brandenburg

Answer 1

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

Expansive

Are all these generic arguments required? – Dissonancy

@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. – Expansive

It should be possible to write Projections.Count(e => e.intVoteUserID). – Dissonancy

@Stefan Steinegger Yes, you are right. Funny that I didn't see that before. I fixed my answer to accomodate that. – Expansive

Hm, the signature doesn't match. Its Count(Expression<Func<object>> expression);` instead of Count<T>(Expression<Func<T, object>> expression); – Expansive