CODEWITHSUNDEEP
c#linq
yeljo
yeljo

Reputation: 51

Get rating for products and product category

I have three tables: Products, Categories and Ratings. I am trying to get average ratings both for products and for product categories. This is what I have done but it gives me the same rating which is only for product and not for its category.

 public class Ratings
 {
     public int RatingId { get; set; }
     public int Rating { get; set; }
     public int ProductId { get; set; }
 }

 public class Category
 {
     public int CategoryId { get; set; }
     public string Name { get; set; }
 }

 public class Products
 {
     public int ProductId { get; set; }
     public string Name { get; set; }
     public int CategoryId { get; set; }
 }

These is my queries

var tmparray = db.Rating.Include(n => n.Products)
           .Where(x => x.Products.CategoryId == CategoryId).Take(1)
           .GroupBy(p => p.ProductsId, o => o.Rating)
           .Select(p => new
           {
               ProductsId = p.Key,
               AverageCategory = p.Average()
           });

var tmparray2 = db.Ocjene
          .GroupBy(p => p.ProductsId, o => o.Rating)
          .Select(p => new
          {
              ProductsId = p.Key,
              Average = p.Average()
          });

Upvotes: 1

Views: 237

Answers (1)

Daniel Manta
Daniel Manta

Reputation: 6683

Each Rating knows its Product, each Product knows its Category, so we can enter by category and navigate back till Rating. Then group the result by Category.

var ratingsByCategory = from c in categories
                        join p in products on c.CategoryId equals p.CategoryId
                        join r in ratings on p.ProductId equals r.ProductId
                        group new 
                        { 
                            categoryId = c.CategoryId,
                            rating = r.Rating
                        } by c.CategoryId into g
                        select new
                        {
                            categoryId = g.Key,
                            rating = g.Average(r=>r.rating)
                        };

Sharing running sample here: .Net Fiddle

Upvotes: 1

Related Questions