Linq to SQL calculate average and count with a join

Question

I am trying to calculate the average rating for a product category and the total number of ratings received. In the example below, I would like to get the average rating/count for the shirts category.

Here is the schema:

Product Table

ProdId	CatId	Name
1	Shirts	Cotton Tee
2	Pants	Sweatpants
3	Pants	Jeans

Rating Table

RatingId	ProdId	Rating
1	1	5
2	1	4
3	3	5
4	2	NULL

Here is the code I have tried that throws an error:

var AggregateRating = db.Ratings.Join(db.Products, r => r.ProdId, p  => p.ProdId, (r, p) => new { r, p })
    .Where(x => x.p.CatId == CatId && x.r.Rating != null).GroupBy(x => x.p.CatID).Select(x => new
    {
        rating = x.Average(y => y.r.Rating),
        count = x.Count()
    });

Answer 1

Try following :

    class Program
    {
        static void Main(string[] args)
        {
            Context db = new Context()
            {
                ProdId = new List<ProdTable>() {
                    new ProdTable() { ProdId = 1, CatId = "Shirts", Name = "Cotton Tee"},
                    new ProdTable() { ProdId = 2, CatId = "Pants", Name = "Sweatpants"},
                    new ProdTable() { ProdId = 3, CatId = "Pants", Name = "Jeans"}
                },
                RatingId = new List<RatingTable>() {
                    new RatingTable() { RatingId = 1, ProdId = 1, Rating = 5},
                    new RatingTable() { RatingId = 2, ProdId = 1, Rating = 4},
                    new RatingTable() { RatingId = 3, ProdId = 3, Rating = 5},
                    new RatingTable() { RatingId = 4, ProdId = 2, Rating = null}
                }
            };

            var results = (from p in db.ProdId
                           join r in db.RatingId on p.ProdId equals r.ProdId
                           select new { p = p, r = r })
                           .GroupBy(x => x.p.CatId)
                           .Select(x => new { catId = x.Key, average = x.Average(y => y.r.Rating) })
                           .ToList();
 
        }
    }
 
    public class Context
    {
        public List<ProdTable> ProdId { get; set; }
        public List<RatingTable> RatingId { get; set; }
    }

    public class ProdTable
    {
        public int ProdId { get; set; }
        public string CatId { get; set; }
        public string Name { get; set; }
    }

    public class RatingTable
    {
        public int RatingId { get; set; }
        public int ProdId { get; set; }
        public int? Rating { get; set; }

    }