Aggregating Left Join in NHibernate using QueryOver/ICriteria

[oharab]

I have two simple classes

public class Blog {
   public Blog(){
     Comments=new List<Comment>();
   }
   public virtual Guid Id { get; set; }
   public virtual string Title { get; set; }
   public virtual string Text { get; set; }
   public virtual DateTime CreatedDate { get; set; }
   public virtual IList<Comment> Comments { get; set; }
 }

and

public class Comment {
   public virtual Guid Id { get; set; }
   public virtual string Author { get; set; }
   public virtual string Text { get; set; }
}

mapped using AutoMap and all is great with the world. I can add and save entities no problems.

What I'd like to do is use QueryOver to get the number of comments per blog, but include those blogs where there are no comments, so in SQL:

SELECT b.Title,COUNT(c.ID) AS Comments FROM Blogs b LEFT JOIN Comments c ON b.ID=c.BlogID

and get
Title Comments
Blog 1 0
Blog 2 0
Blog 3 0
Blog 4 4
Blog 5 0

The closest I've got is

var results=session.QueryOver<Blog>()
 .Left.JoinQueryOver<Comment>(b=>b.Comments)
 .TransformUsing(new DistinctRootEntityResultTransformer())
 .List<Blog>()
 .Select(b => new { Id = b.Id, Title = b.Title, Comments=b.Comments.Count });

which gets the right answer, but the SQL runs as

SELECT b.Id,b.Title,c.ID,c.Author,etc... AS Comments FROM Blogs b LEFT JOIN Comments c ON b.ID=c.BlogID

then does the counting at the client end, which doesn't seem the most efficient way of doing it.

Can this be done with QueryOver or ICriteria? I'd rather not use hql if possible.

The entire solution is available at

https://github.com/oharab/NHibernateQueriesSpike

if you want to see all the config etc.

Cheers

B.


----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[oharab]

The answer was the JoinAlias method, with an alias placeholder:

Comment comment=null;
var results=session.QueryOver<Blog>()
    .Left.JoinAlias(b=>b.Comments,()=>comment)
    .SelectList(
        list=>list
        .SelectGroup(b=>b.Id)
        .SelectGroup(b=>b.Title)
        .SelectCount(b=>comment.Id)
    )
    .List<object[]>()
    .Select(b => new {
                Id = (Guid)b[0],
                Title = (string)b[1],
                Comments=(int)b[2]
               });

This does exactly as I expected it to.

B.

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.