NHibernate : Group by and Count

Question

I have a User, Book and UserBooks releation table. UserBooks table like this

|UserID | BookID  | Status    |
   1        34      Read
   1        35      Unread 
   2        34      Read
   2        70      Read
   2        32      Unread  
   ...................

My domain classes are User,Book and UserBook. in NHibernate how can I get top 10 most read books and their read count by users?

Top 10 Read Books

| BookID | ReadCount |
   34         2
   70         1
   ...............

Answer 1

Assuming you are using composite-element to map your UserBook class:

var top10Books = session
    .CreateQuery(@"select b.Book 
                   from User u 
                   join fetch u.Books b 
                   where b.Status = :status")
    .SetParameter("status", "Read")
    .SetMaxResults(10)
    .SetResultTransformer(CriteriaSpecification.DistinctRootEntity)
    .List<Book>();

And here's the link to the complete source code.

EDIT:

I see that you require the number of times a book has been read. Here's the query:

var top = session
    .CreateQuery(@"select b.Book, count(b.Book.Id) 
                   from User u join fetch u.Books b 
                   where b.Status = :status 
                   group by b.Book")
    .SetParameter("status", "Read")
    .SetMaxResults(10)
    .List<object[]>();

    foreach (var item in top)
    {
        var book = (Book)item[0];
        var readCount = (long)item[1];
        Console.WriteLine("book id: {0}, read count: {1}", book.Id, readCount);
    }