ordering by collection count in nhibernate

Question

Is there a way of ordering a list of objects by a count of a property which is a collection?

For arguments sake let's say I have a question object with a question name property, a property that is a collection of answer objects and another property that is a collection of user objects. The users join the question table via foreign key on question table and answers are joined with middle joining table.

If I want nhibernate to get a list of "question" objects could I order it by Question.Answers.Count?

i've tried the documentation's example using HQL:

 List<Question> list = nhelper.NHibernateSession
        .CreateQuery("Select q from Question q left join q.Answers a group by q,a order by count(a)")
        .List<Question>();

but i get

"column Question.Name is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

I've tried adding all the properties to the group by list but it doesn't work. What happens then is that foreign key userId causes the same error as above but i can't include it in the group by as nhibernate lists it as

Question.Users.UserId

which doesn't solve it if included.

any ideas?

Answer 1

I ran into this issue and I used Linq to get around it.

            var sort = from n in c
                       orderby q.Answers.Count
                       select n;

I BELIEVE that's the syntax, if someone could verify that would be great I haven't used LINQ too much.

Answer 2

i can't use linq.

the way i've solved it (and you may decide it's donkey) is to create a computed column on the database that calculates the count using a UDF. i then marked the column in the hibernate mapping as update="false" and insert="false".

i can now order my queries by this new column as normal. It gives me the added bonus of being able to bind this value in places where it need it throughout my app.

it seems to perform ok, hopefully it isn't gonna cost me too much performance.