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() }

What error are you getting? Can you show the definition of your entity classes? — Yacoub Massad, Nov 27 '15 at 21:47
The problem here is that the column "ratingpicture" has the same content as "rating". Furthermore, I have "ratingpicture" in my "comment" column which makes no sense to me. — Christopher Wilke, Nov 27 '15 at 21:50
If you model the relationships in your entity classes correctly, you wouldn't have to use `join` — Yacoub Massad, Nov 27 '15 at 21:51
This is my model: http://s22.postimg.org/wk0ee8rap/model.png — Christopher Wilke, Nov 27 '15 at 22:00
I meant your model classes. For example, the `User` class and the `Comment` class and the `Rating` class ... — Yacoub Massad, Nov 27 '15 at 22:02
Sure :) Here are my classes http://s22.postimg.org/y88qkdjxt/classes.jpg — Christopher Wilke, Nov 27 '15 at 22:06
Can't you provide the classes as code? It would be a lot easier. — Yacoub Massad, Nov 27 '15 at 22:09

score 1 · Accepted Answer · answered Nov 27 '15 at 22:22

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.

Didn't know that this is possible. I will try this. Many thanks. — Christopher Wilke, Nov 27 '15 at 22:31
One last question: The classes are connected to a SQL server. Do I need to add a [NotMapped] in front of each virtual element? — Christopher Wilke, Nov 27 '15 at 22:41
@user1705950, no you don't. EF understands these properties correctly. — Yacoub Massad, Nov 27 '15 at 22:45

Grouping 4 Tables using Linq

1 Answers1