Linq:GroupBy、Sum 和 Count [英] Linq: GroupBy, Sum and Count

问题描述

我有一系列产品

public class Product {

   public Product() { }

   public string ProductCode {get; set;}
   public decimal Price {get; set; }
   public string Name {get; set;}
}

现在我想根据产品代码对集合进行分组，并返回一个对象，其中包含每个代码的名称、数量或产品以及每个产品的总价.

Now I want to group the collection based on the product code and return an object containing the name, the number or products for each code and the total price for each product.

public class ResultLine{

   public ResultLine() { }

   public string ProductName {get; set;}
   public string Price {get; set; }
   public string Quantity {get; set;}
}

所以我使用 GroupBy 按 ProductCode 分组，然后计算总和并计算每个产品代码的记录数.

So I use a GroupBy to group by ProductCode, then I calculate the sum and also count the number of records for each product code.

这是我目前所拥有的:

List<Product> Lines = LoadProducts();    
List<ResultLine> result = Lines
                .GroupBy(l => l.ProductCode)
                .SelectMany(cl => cl.Select(
                    csLine => new ResultLine
                    {
                        ProductName =csLine.Name,
                        Quantity = cl.Count().ToString(),
                        Price = cl.Sum(c => c.Price).ToString(),
                    })).ToList<ResultLine>();

由于某种原因，求和正确，但计数始终为 1.

样本数据:

List<CartLine> Lines = new List<CartLine>();
            Lines.Add(new CartLine() { ProductCode = "p1", Price = 6.5M, Name = "Product1" });
            Lines.Add(new CartLine() { ProductCode = "p1", Price = 6.5M, Name = "Product1" });
            Lines.Add(new CartLine() { ProductCode = "p2", Price = 12M, Name = "Product2" });

样本数据的结果:

Product1: count 1   - Price:13 (2x6.5)
Product2: count 1   - Price:12 (1x12)

产品 1 应该有 count = 2！

Product 1 should have count = 2!

我试图在一个简单的控制台应用程序中模拟这个，但我得到了以下结果:

I tried to simulate this in a simple console application but there i got the following result:

Product1: count 2   - Price:13 (2x6.5)
Product1: count 2   - Price:13 (2x6.5)
Product2: count 1   - Price:12 (1x12)

Product1:应该只列出一次...以上代码可以在 pastebin 上找到:http://pastebin.com/cNHTBSie

Product1: should only be listed once... The code for the above can be found on pastebin: http://pastebin.com/cNHTBSie

推荐答案

我不明白第一个带有示例数据的结果"从何而来，但控制台应用程序中的问题是您使用的是 SelectMany 查看每组中的每一项.

I don't understand where the first "result with sample data" is coming from, but the problem in the console app is that you're using SelectMany to look at each item in each group.

我想你只是想要:

List<ResultLine> result = Lines
    .GroupBy(l => l.ProductCode)
    .Select(cl => new ResultLine
            {
                ProductName = cl.First().Name,
                Quantity = cl.Count().ToString(),
                Price = cl.Sum(c => c.Price).ToString(),
            }).ToList();

此处使用 First() 获取产品名称是假设具有相同产品代码的每个产品都具有相同的产品名称.如评论中所述，您可以按产品名称和产品代码分组，如果任何给定代码的名称始终相同，这将产生相同的结果，但显然在 EF 中生成更好的 SQL.

The use of First() here to get the product name assumes that every product with the same product code has the same product name. As noted in comments, you could group by product name as well as product code, which will give the same results if the name is always the same for any given code, but apparently generates better SQL in EF.

我还建议您将 Quantity 和 Price 属性更改为 int 和 decimal类型 - 为什么对明显不是文本的数据使用字符串属性?

I'd also suggest that you should change the Quantity and Price properties to be int and decimal types respectively - why use a string property for data which is clearly not textual?

这篇关于Linq:GroupBy、Sum 和 Count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！