Order by relation count in NHibernate

Question

I have a datastructure like this:

public class User
{
    public Guid Id {get;set;}
    public string Name {get;set;}
    public IList<Books> Books {get;set}
}

I have been struggeling with making it possible to sort the users by the count of bookmarks (one-to-many relation).

I have tried various approaches with both linq, criteria and queryover, but with no luck, and therefore hope one of you could help.

I am using paging, since I have quite a few users, so the solution needs to do the query on the SQL and not in memory on the webserver.

Firo · Accepted Answer · 2011-07-29T07:53:12.237

1

var loadedUser = session.Query<User>()
    .Select(u => new { u, u.Books.Count })
    .ToList()
    .OrderBy(anonym => anonym.Count)
    .Select(anonym => anonym.u);

or using HQL

select user
from User as user 
    left join user.Books as books
group by user
order by count(books)

edited Jul 29 '11 at 07:53

answered Jul 27 '11 at 14:04

Firo

30,626
4
55
94

1

I am pretty sure this would be very slow, since you would do the sort in memory instead of on the SQL database. – Dofs Jul 27 '11 at 17:46
1

@Dofs: if you're not paging, why do you think the DB will sort it faster? The DB will do it in memory too; it doesn't have a magic "sorting bucket" that exists outside the known universe... – Diego Mijelshon Jul 27 '11 at 23:57
Hi Diego, I am using paging, but didn't add it to the original question. It is now updated. I also tried your extension here: http://sessionfactory.blogspot.com/2011/02/getting-row-count-with-future-linq.html, but I get the error: NoViableAltException(84@[]) and MismatchedTreeNodeException(70!=3) (Using NH 3.04) – Dofs Jul 28 '11 at 06:36
@DiegoMijelshon It's not that the DB has a more efficient sorting bucket (although it might if the relation is indexed), it's that to do it in the programs' memory means that every entry must be brought across the wire and mapped into an object before it can be ordered. That network and mapping time can be very significant. – heneryville Jul 02 '13 at 17:10
@heneryville ...and? It's either sorted before sending, or after sending. You are just changing the place where that happens. – Diego Mijelshon Jul 02 '13 at 17:13
@DiegoMijelshon Ah, I was assuming paging/truncation/limiting, which you did mention. – heneryville Jul 02 '13 at 17:15

score 0 · Answer 2 · edited May 23 '17 at 09:58

0

See : Order by collection count using ICriteria & NHibernate

select u
from User u
join u.Books b
group by u
order by count(b) asc

Or using LINQ :

users.OrderBy(x => x.Books.Count);

edited May 23 '17 at 09:58

Community

1
1

answered Jul 27 '11 at 14:07

mathieu

30,974
4
64
90

I don't know if I am doing something wrong, but NHProfiler gives me a n+1 problem. – Dofs Jul 27 '11 at 18:36
yes because all books will be fetched one time for each user. You should fetch them when you query for users. – mathieu Jul 27 '11 at 19:54

Order by relation count in NHibernate

2 Answers2

Linked