linq lambda multiple group joins

Question

I am trying to get a list of product along with its rating, comments and views. PID is product ID column without foreign key relationship.

Product -

Id  Name
1   P1
2   P2

Rating -

Id  PID Rating
1   1   5
2   1   4
3   2   3

Comments -

Id  PID Comment
1   1   Good
2   1   Average
3   2   Bad

Views -

Id  PID View
1   1   500
2   1   200
3   2   10

My class would look like this –

Public Class Product{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Rating> Ratings{ get; set; }
    public List<Comments> Comments{ get; set; }
    public List<Views> Views{ get; set; }
}

I am trying to get this information using Linq group join, so that I get child collection.

IEnumerable<Product> _products = _context.Product.GroupJoin(_context.Rating, p=>p.id, r=>r.PID, (Product, Rating) => new Product(){
    //fill fields here
});

But how to group other tables as well into single database query.

Thanks

Answer 1

Instead of a GroupJoin, you can just look up the matches directly to construct the Product object:

IEnumerable<Product> _products = _context.Product.Select(product => new Product() {
    Id = product.id,
    Name = product.name,
    Ratings = _context.Rating.Where(r => r.PID == product.id).ToList(),
    // ... other lists similar
});

As pointed out in comments, the above query could generate three sub-queries for every product.

You can use GroupJoin if you create anonymous objects to hold the intermediate results:

var _products = _context.Product.GroupJoin(_context.Rating, p => p.id, r => r.PID, (p, rs) => new { p, rs })
                                .GroupJoin(_context.Comment, prs => prs.p.id, c => c.PID, (prs, cs) => new { prs.p, prs.rs, cs })
                                .GroupJoin(_context.View, prs => prs.p.id, v => v.PID, (prscs, vs) => new Product() {
                                    Id = prscs.p.id,
                                    Name = prscs.p.name,
                                    Ratings = prscs.rs.ToList(),
                                    Comments = prscs.cs.ToList(),
                                    Views = vs.ToList()
                                });

Answer 2

You can try like this;

        var records = _context.Product
            .GroupJoin(_context.Ratings, p => p.Id, r => r.PID, (p, r) => new { Product = p, Ratings = r})
            .GroupJoin(_context.Comments, p => p.Product.Id, c => c.PID, (p, c) => new { p.Product, p.Ratings, Comments = c})
            .GroupJoin(_context.Views, p => p.Product.Id, v => v.PID, (p, v) => new { p.Product, p.Ratings, p.Comments, Views = v })
            .Select(p => new
            {
                Id = p.Product.Id,
                Name = p.Product.Name,
                Comments = p.Comments,
                Ratings = p.Ratings,
                Views = p.Views
            })
            .ToList().Select(x => new Product
            {
                Id = x.Id,
                Name = x.Name,
                Comments = x.Comments.ToList(),
                Ratings = x.Ratings.ToList(),
                Views = x.Views.ToList()
            }).ToList();