Grouping 4 Tables using Linq

Question

I have the following requirements:

• One rating can have zero or many RatingPictures
• One Rating can have zero or many Comments
• One Comment belongs to one User

This is what I have so far:

from rating in Ratings
where rating.LocationID == 1007
    join ratingpicture in RatingPictures
    on rating.ID equals ratingpicture.RatingID into j3
    from ratingpicture in j3.DefaultIfEmpty()
        join comment in Comments
        on rating.ID equals comment.RatingID into j1
        from comment in j1.DefaultIfEmpty()
            join user in Users
            on comment.UserID equals user.ID into j2
            from user in j2.DefaultIfEmpty()
group new { ratingpicture, comment, user } by rating into g
select new { rating = g.Key, ratingpicture= g.Key, comment = g.ToList() }

Answer 1

If you model your entity classes like this:

public class Comment
{
    public int CommentId { get; set; }
    public int RatingId { get; set; }
    public virtual Rating Rating { get; set; }

    public int UserId { get; set; }
    public virtual User User { get; set; }
}

public class User
{
    public int UserId { get; set; }

}

public class Rating
{
    public Rating()
    {
        RatingPictures = new HashSet<RatingPicture>();
        Comments = new HashSet<Comment>();
    }

    public int RatingId { get; set; }
    public virtual ICollection<RatingPicture> RatingPictures { get; set; }
    public virtual ICollection<Comment> Comments { get; set; }
    public int LocationID { get; set; }
}

public class RatingPicture
{
    public int RatingPictureId { get; set; }
    public int RatingId { get; set; }
    public virtual Rating Rating { get; set; }

}

Then your query would be as simple as this:

var query = context.Ratings.Where(r => r.LocationID == 1007)
    .Include(r => r.RatingPictures)
    .Include(r => r.Comments.Select(c => c.User));

var result = query.ToList();

Under the hood, this query will be translated into a join query. But this is the beauty of ORMs, we get to work at a more abstract level.

Take a look at this reference for more information about relationships and navigation properties in Entity Framework.