Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Aggregating Left Join in NHibernate using QueryOver/ICriteria

Status
Not open for further replies.

oharab

Programmer
May 21, 2002
2,152
GB
I have two simple classes

Code:
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

Code:
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:
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
Code:
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

SQL:
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 if you want to see all the config etc.

Cheers

B.


----------------------------------------------
Ben O'Hara
David W. Fenton said:
We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.
 
The answer was the JoinAlias method, with an alias placeholder:
Code:
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
David W. Fenton said:
We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top