c# - Linq 到 SQL : Get Top 5 Rated products

Question

如何通过 Linq to Sql 获得评分最高的 5 个产品？

我的产品类是

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

我的 ProductReviews 类是

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; }

    }

Answer 1

假设您想从每个产品的所有评论中获取评分的简单平均值，下面的代码(包括示例数据)应该可以解决问题(我已经针对对象对 LINQ 进行了测试，但 应该也可以与 LINQ to SQL 一起工作):

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);

第一个语句是获取评论数据，按 ProductID 分组并计算每个产品的 Rating 的平均值。

第二个语句然后是对每个产品取平均值，并为您提供平均评分最高的 5 个产品。

如果您想做一些不同的事情(比如给最近的评论更高的权重)，您可以将“产品”传递给计算该产品评级的自定义函数，例如:

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;
}