CODEWITHSUNDEEP
c#asp.net-mvclinq
OrElse
OrElse

Reputation: 9959

Linq to SQL: Get Top 5 Rated products

How can i get the top 5 rated products via via Linq to Sql?

enter image description here

My products class is 

public class Product
{
   [Key]
   [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
   public int ProductID { get; set; }
   public string Name { get; set; }
   public decimal Price { get; set; }
}

and my ProductReviews class is 

public class ProductReview
    {

            [Key]
            [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
            public int ProductReviewID { get; set; }
            public int ProductID { get; set; }
            public int Rating { get; set; }

            public virtual Product Product { get; set; }

    }

Upvotes: 1

Views: 171

Answers (3)

Rob
Rob

Reputation: 45771

Assuming that you want to take a simple average of the Rating from all reviews for each product, the following code (including sample data) should do the trick (I've tested this against LINQ against objects, but it should work with LINQ to SQL also):

var products = new List<Product>
{
    new Product { ProductID = 1, Name= "Product 1", Price = 1.00m },
    new Product { ProductID = 2, Name= "Product 2", Price = 1.00m },
    new Product { ProductID = 3, Name= "Product 3", Price = 1.00m },
    new Product { ProductID = 4, Name= "Product 4", Price = 1.00m },
    new Product { ProductID = 5, Name= "Product 5", Price = 1.00m },
    new Product { ProductID = 6, Name= "Product 6", Price = 1.00m }
};

var productReviews = new List<ProductReview>
{
    new ProductReview { ProductReviewID = 1, ProductID = 1, Rating = 5 },
    new ProductReview { ProductReviewID = 2, ProductID = 1, Rating = 3 },
    new ProductReview { ProductReviewID = 3, ProductID = 2, Rating = 1 },
    new ProductReview { ProductReviewID = 4, ProductID = 3, Rating = 4 },
    new ProductReview { ProductReviewID = 5, ProductID = 4, Rating = 2 },
    new ProductReview { ProductReviewID = 6, ProductID = 5, Rating = 5 },
    new ProductReview { ProductReviewID = 7, ProductID = 6, Rating = 4 },
    new ProductReview { ProductReviewID = 8, ProductID = 6, Rating = 3 }
};

var averageProductRatings = from review in productReviews
                            group review by review.ProductID into product
                            select new
                            {
                                ProductId = product.Key,
                                AverageRating = product.Average(p => p.Rating)
                            };

var top5 = averageProductRatings.OrderByDescending(average => average.AverageRating).Take(5);

The first statement is taking the review data, grouping it by ProductID and calculating the average value of the Rating for each Product.

The second statement is then taking the averages for each product and giving you the 5 products with the highest average ratings.

If you wanted to do something different (like give a higher weighting to more recent reviews) you could pass "product" into a custom function that works out the rating for that product, for example:

var averageProductRatings = from review in productReviews
                            group review by review.ProductID into product
                            select new
                            {
                                ProductId = product.Key,
                                AverageRating = GetProductRatingFromReviews(product)
                            };

private  double GetProductRatingFromReviews(IGrouping<int, ProductReview> productReviews)
{
    // Work out the aggregate rating to give the product here and return it

    foreach (var item in productReviews)
    {

    }
    return -1;
}

Upvotes: 2

Maor Veitsman
Maor Veitsman

Reputation: 1564

I hope I understood your use case properly. products and productReviews are similar to your relevant collections:

productReviews.GroupBy(q => q.ProductID)
            .Select(t => new {ID = t.Key, ReviewAvg = t.Average(q => q.Rating})
            .OrderByDescending(q => q.ReviewAvg).Take(5).Select(t => products.FirstOrDefault(q => q.ProductID == t.ID));

I first group by all reviews by the relevant product id (would be better if grouping si done by the product itself) and then for each product the average rating is being calculated and the top 5 products are chosen.

Upvotes: 1

dlxeon
dlxeon

Reputation: 2000

You need to specify criteria to detect top items. Then you can do something like this

var top5Products = database.Products.Where(product => _your condition_).OrderBy(product => product.SomeField).Take(5).ToList();

Upvotes: 0

Related Questions