I have two simple classes
and
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:
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
which gets the right answer, but the SQL runs as
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
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; }
}
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 });
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.