Fluent Nhibernate - Join query with group by

Question

I'm having trouble executing a join query in NHibernate. I have the following tables :

BOOKS:
ID, NAME, BOOK_TYPE, AUTHOR_ID

AUTHORS:
ID, FIRST_NAME, LAST_NAME, BIRTH_DATE

And I want to execute the following sql query in Fluent NHibernate:

SELECT AUTHORS.ID, COUNT(BOOKS.ID)
FROM AUTHORS
INNER JOIN BOOKS
ON AUTHORS.ID = BOOKS.AUTHOR_ID
GROUP BY AUTHORS.ID;

Class:

public class Book
{
     public virtual int id{get; set;}
     public virtual string Name{get; set;}
     public virtual int booktype{get; set;} 
     public virtual Author author{get; set;}
}

public class Author
{
     public virtual int id{get; set;}
     public virtual string FirstName{get; set;}
     public virtual string LastName{get; set;}
     public virtual DateTime BirthDate{get; set;}
     public virtual IList<Book> Books{get; set;}
}

Here is what I have tried:

GraphDTO graph = null;
Session.QueryOver<Book>()
.SelectList(list => list
.SelectGroup(x => x.Author.Id).WithAlias(() => graph.Id)
.SelectCount(x => x.Id).WithAlias(() => graph.BooksNum))
.TransformUsing(Transformers.AliasToBean<GraphDTO>())
.List<GraphDTO>();

Please update your question with your class and mapping defintion — kayess, Nov 30 '15 at 12:54

score 2 · Answer 1 · edited May 23 '17 at 11:59

2

Well, we can do it with a join and group by like this

Author author = null;
Book book = null;
var query = session.QueryOver<Contact>(() => author)
    .JoinQueryOver(() => author.Authors, () => book)
    .SelectList(list => list
        .SelectGroup(x => author.ID)
        // we can have more stuff from author table
        .SelectGroup(x => author.LastName)
        .SelectGroup(x => author.FirstName)
        .SelectCount(x => book.ID))
    ;

var result = query.List<object[]>();

But the above SQL in fact would not need JOIN, it could be just as here Fluent Nhibernate - selecting specific column and count query with group by

session.QueryOver<Book>()
    .SelectList(list => list
        .SelectGroup(c => c.Author.ID)
        .SelectCount(c => c.ID))
    .List<object[]>();

edited May 23 '17 at 11:59

Community

1
1

answered Nov 30 '15 at 16:02

Radim Köhler

122,561
47
239
335

The idea is perfect but you cant use the select group for that. See http://stackoverflow.com/questions/27527409/fluent-nhibernate-could-not-resolve-property – Golan Kiviti Dec 01 '15 at 07:44
Have you tried my answer? I just reran that.. and it is working. Select group could be used as suggested. – Radim Köhler Dec 01 '15 at 07:54
Ok, so I have tried that and that works, but I dont want a list of objects, I want list of another object - lets call it GraphDTO. I edited my answer so you can see what I mean – Golan Kiviti Dec 02 '15 at 07:12
That is standard requirement. And it could be easily solved by Transformer: http://stackoverflow.com/a/26901453/1679310 or http://stackoverflow.com/q/16496823/1679310. So you are almost there! great ;) – Radim Köhler Dec 02 '15 at 07:14

Fluent Nhibernate - Join query with group by

1 Answers1