×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Aggregating Left Join in NHibernate using QueryOver/ICriteria

Aggregating Left Join in NHibernate using QueryOver/ICriteria

Aggregating Left Join in NHibernate using QueryOver/ICriteria

(OP)
I have two simple classes

CODE --> C#

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 --> C#

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:

CODE --> 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 --> C#

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

CODE --> 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 https://github.com/oharab/NHibernateQueriesSpike if you want to see all the config etc.

Cheers

B.

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

Quote (David W. Fenton)

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

RE: Aggregating Left Join in NHibernate using QueryOver/ICriteria

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

CODE --> C#

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

Quote (David W. Fenton)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close