c# - LINQ中的SQL语句转换

Question

我有一个Products 表和一个OrdersDetails 表。我想按订单数显示前 5 名的产品。

这是我要在 LINQ 中转换的 SQL 语句:

SELECT TOP 5 P.ProductId, COUNT(OD.OrderId) AS 'Quantity Ordered' FROM Products AS P
INNER JOIN OrdersDetails AS OD
ON P.ProductId = OD.ProductId
GROUP BY P.ProductId
ORDER BY 'Quantity Ordered' DESC

有人可以帮帮我吗？

编辑

我在我的 MVC 项目中使用 LINQ to EF。

产品实体:

public partial class Product
{
    public Product()
    {
        this.OrdersDetails = new HashSet<OrdersDetail>();
        this.ProductHistories = new HashSet<ProductHistory>();
        this.ProductReviews = new HashSet<ProductReview>();
    }

    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public Nullable<float> Discount { get; set; }
    public int Quantity { get; set; }
    public string Size { get; set; }
    public string Description { get; set; }
    public string ProducerName { get; set; }
    public string PaymentMethods { get; set; }
    public int Category { get; set; }
    public bool IsNew { get; set; }
    public Nullable<System.DateTime> CreateDate { get; set; }
    public bool IsEnable { get; set; }

    public virtual Category Category1 { get; set; }
    public virtual ICollection<OrdersDetail> OrdersDetails { get; set; }
    public virtual ICollection<ProductHistory> ProductHistories { get; set; }
    public virtual ICollection<ProductReview> ProductReviews { get; set; }
}

OrdersDetail 实体:

public partial class OrdersDetail
{
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public Nullable<int> Quantity { get; set; }
    public Nullable<decimal> Price { get; set; }
    public Nullable<float> Discount { get; set; }

    public virtual Order Order { get; set; }
    public virtual Product Product { get; set; }
}

这是我的 View 模型类:

public class ProductsViewModel
{
    public int ProductId { get; set; }

    [Required(ErrorMessage = "The Product Name is required.")]
    public string Name { get; set; }

    [Required(ErrorMessage = "The Price is required.")]
    [Range(0.01, int.MaxValue, ErrorMessage = "Price must be a positive number.")]
    public decimal Price { get; set; }

    [Required(ErrorMessage = "The Discount is required.")]
    [Range(0, 99.99F, ErrorMessage = "Discount must be betwen 0 and 99,99.")]
    public float? Discount { get; set; }

    [Required(ErrorMessage = "The Quantity is required.")]
    [Range(0, int.MaxValue, ErrorMessage = "Quantity must be a positive number or zero.")]
    public int Quantity { get; set; }

    public string Size { get; set; }

    [DataType(DataType.MultilineText)]
    public string Description { get; set; }

    public string ProducerName { get; set; }

    public string PaymentMethods { get; set; }

    public bool IsNew { get; set; }

    public bool IsEnable { get; set; }

    public SelectList CategoryList { get; set; }

    public string Category { get; set; }

    [Required(ErrorMessage = "The Category is required.")]
    public int CategoryID { get; set; }

    public string Subcategory { get; set; }

    public int SubcategoryID { get; set; }

    public DateTime? CreateDate { get; set; }

    public string ProductImage
    {
        get { return Name.Replace(" ", string.Empty) + ".jpg"; }
        set { }
    }

    public HttpPostedFileBase UploadedFile { get; set; }

    public IEnumerable<ProductHistory> ProductHistory { get; set; }

    [Required(ErrorMessage = "The Review is required.")]
    [DataType(DataType.MultilineText)]
    public string Review { get; set; }

    [Required(ErrorMessage = "The Rate is required.")]
    [Range(1, 5, ErrorMessage = "Rate must be between 1 and 5")]
    public int RateProduct { get; set; }

    public IEnumerable<ProductReview> ProductReviews { get; set; }
}

现在我尝试这样的事情:

IEnumerable<ProductsViewModel> product = from pro in context.Products
                                                  join order in context.OrdersDetails
                                                      on pro.ProductId equals order.ProductId
                                                  group pro by pro.ProductId into g
                                                  select new ProductsViewModel { //maybe here I try to Count };

我想用我的 View 模型显示排名前 5 位的产品。也许我必须在我的模型中为计数输入一个新属性？

Answer 1

IEnumerable<ProductsViewModel> product = (from pro in context.Products
                                                  join order in context.OrdersDetails
                                                      on pro.ProductId equals order.ProductId
                                                  group pro by pro.ProductId into g
                                                  select new ProductsViewModel).take(5);