Get details by joining tables with group by condition in Linq and EF

Question

I have the following 3 table definitions in my SQL Server database:

+----------------------------------+-----------------+------------------------------------------------
|             Product              | Rating                          |         Image                   | 
+----------------------------------+-----------------+-------------------------------------------------
| ProductId                        | Id                              | Id                              |
| ProdctName                       | Rating                          | Image                           |
|                                  | ProductId FK References(Product)| ProductId FK References(Product)|
+----------------------------------+-----------------+---------------+----------------------------------

And these tables contain the following sample data:

+----------------------------------+------------
|Product              | ProductId  |ProductName |
+----------------------------------+------------
|                     | 1          |Prodcuct 1  |
|                     | 2          |Prodcuct 2  |
|                     | 3          |Prodcuct 3  |
|                     | 4          |Prodcuct 4  |
+----------------------------------+------------+

+----------------------------------+----------------------+
|Rating              | Id          |RatingVal   |ProductId |
|+----------------------------------+-----------------------
|                     | 1          |3           |1         |
|                     | 2          |2           |2         | 
|                     | 3          |3           |2         |
|                     | 4          |5           |3         |
|                     | 5          |4           |3         |
+---------------------+------------+------------+----------+

+----------------------------------+----------------------+
|Image                | Id          |ImagePath  |ProductId
+----------------------------------+-----------------------
|                     | 1          |ABC           |1       |
|                     | 2          |XYZ           |2       | 
|                     | 3          |LMN           |3       |
|                     | 4          |PQR           |4       |
+---------------------+------------+------------+----------+

I need to gather information about a product in one place, such that each product contains the details about product ( from products table), related average rating ( from ratings table)m and the image path for the product ( from Image table). In other words I need the following output:

+----------------------------------+--------------------------------+
|Output               | ProductId  |ProductName |Avg(Rating)|ImgPath|
+----------------------------------+--------------------------------+
|                     | 1          |Prodcuct 1  |3          |ABC    |
|                     | 2          |Prodcuct 2  |2.5        |XYZ    |
|                     | 3          |Prodcuct 3  |4.5        |LMN    |
|                     | 4          |Prodcuct 4  |0.0        |PQR    |
+----------------------------------+------------+-----------+-------+

I am using Entity Framework to fetch this data, and entities in context class in my code( shown below).

My question is: How do I produce my desired output for all the products. My code below is not able to get all the data I want. The problem is that the product with id4 is not shown in the result, I assume this is be cause product 4 does not have an entry in the ratings table.

var  trendingProducts = (from ratings in entities.Rating
                         group ratings by new { ratings.productId } into c
                         join products in entities.Products on c.FirstOrDefault().productId equals products.ProductID
                         join images in entities.Images on c.Key.productId equals images.ProductId                                       
                         select new ProductViewModel
                         {
                             ProductId = products.ProductId,
                             ProductName = products.ProductName,
                             RatingVal = c.Average(l => l.RatingVal) == null ? 0 : c.Average(l => l.Rating),
                             ImagePath = images.ImagePath,

                         }).ToList();

Answer 1

So you have a table of Products, a table of Ratings and a table of Images.

Every Product has zero or more Ratings, every Rating belongs to exactly one Product using foreign key ProductId. Similarly, every Product has zero or more Images, every Image belongs to exactly one Image, using foreign key ProductId. Just standard one-to-many relations.

It might be that every Product has zero-or-one Image: in that case you have a zero-or-one-to-one relation. The code will be similar. The main difference is that 'Product' won't have a collection of Images, it will only have one virtual Image.

If you follow the entity framework code first conventions you'll have classes like:

class Product
{
    public int Id {get; set;}

    // every Product has zero or more Ratings:
    public virtual ICollection<Rating> Ratings {get; set;}

    // every product has zero or more Images:
    public virtual ICollection<Image> Images {get; set;}

    ... // other properties
}

class Rating
{
    public int Id {get; set;}

    // every Rating belongs to exactly one Product using foreign key:
    public int ProductId {get; set;}
    public virtual Product Product {get; set;} 

    ...
}

class Image
{
    public int Id {get; set;}

    // every Image belongs to exactly one Product using foreign key:
    public int ProductId {get; set;}
    public virtual Product Product {get; set;} 

    ...
}

This is everything entity framework needs to know to detect your one-to-many relations. It knows which tables / columns you want, and it knows the relations between the tables. It might be that you want different identifiers for your tables or properties. In that case you'll need attributes or fluent API. But that is out of scope of this question.

Note that in entity framework all non-virtual properties will become columns in your tables. All virtual properties represent the relations between the tables.

I need to gather information about a product in one place, such that each product contains the details about product ( from products table), related average rating ( from ratings table) and the image path for the product (from Image table).

Whenever people query for "objects with their sub-object" they tend to create a (group)join. However, if you use entity framework it is much easier to use the ICollections for these queries. If you use an ICollection, Entity Framework will know that a (group)join is needed.

var result = myDbContext.Products            // from the collection of Products
    .Where(product => ...)                   // select only those Products that ...
    .Select(product => new                   // from every remaining Product make one new
    {                                        // object with the following properties:
         // Select only the properties you actually plan to use!
         Id = product.Id,
         Name = product.ProductName,
         ...

         AverageRating = product.Ratings       // from all Ratings of this Product
             .Select(rating => rating.Rating)  // get the Rating value
             .Average(),                       // and Average it.

         Images = product.Images               // from all Images of this product
             .Select(image => image.ImagePath) // select the ImagePath
             .ToList(),

         // or if a Product has only zero or one Image:
         Image = product.Image?.ImagePath // if the product has an Image, get its ImagePath
                                          // or use null if there is no Image
    });

The nice thing about using the ICollections is that the code is simplere and more natural, it looks more similar to the text of your requirement than a (group)join. Besides, if more than two tables are involved (group)joins tend to look horrible.

I won't try to give a solution using a GroupJoin. I'm sure other answers will show this. Compare and see for yourself which solution seems easier to understand.