C# Linq：GroupBy、Sum 和 Count
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16522645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq: GroupBy, Sum and Count
提问by ThdK
I have a collection of products
我有一系列产品
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;}
}
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.
因此，我使用 GroupBy 按 ProductCode 进行分组，然后计算总和并计算每个产品代码的记录数。
This is what I have so far:
这是我到目前为止：
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>();
For some reason, the sum is done correctly but the count is always 1.
出于某种原因，总和正确完成，但计数始终为 1。
Sampe data:
样本数据：
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" });
Result with sample data:
结果与样本数据：
Product1: count 1 - Price:13 (2x6.5)
Product2: count 1 - Price:12 (1x12)
Product 1 should have count = 2!
产品 1 应该有 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: should only be listed once... The code for the above can be found on pastebin: http://pastebin.com/cNHTBSie
Product1：应该只列出一次...上面的代码可以在pastebin上找到：http: //pastebin.com/cNHTBSie
采纳答案by Jon Skeet
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 SelectManyto look at each item in each group.
我不明白第一个“带有示例数据的结果”来自哪里，但控制台应用程序中的问题是您正在使用SelectMany查看每个组中的每个项目。
I think you just want:
我想你只是想要：
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();
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.
使用First()此处获取产品名称假定具有相同产品代码的每个产品具有相同的产品名称。如评论中所述，您可以按产品名称和产品代码分组，如果任何给定代码的名称始终相同，这将给出相同的结果，但显然在 EF 中生成更好的 SQL。
I'd also suggest that you should change the Quantityand Priceproperties to be intand decimaltypes respectively - why use a string property for data which is clearly not textual?
我还建议您应该将Quantity和Price属性分别更改为int和decimal类型 - 为什么对显然不是文本的数据使用字符串属性？
回答by Charles Lambert
The following query works. It uses each group to do the select instead of SelectMany. SelectManyworks on each element from each collection. For example, in your query you have a result of 2 collections. SelectManygets all the results, a total of 3, instead of each collection. The following code works on each IGroupingin the select portion to get your aggregate operations working correctly.
以下查询有效。它使用每个组来进行选择而不是SelectMany. SelectMany适用于每个集合中的每个元素。例如，在您的查询中，您有 2 个集合的结果。SelectMany获取所有结果，一共3个，而不是每个集合。以下代码适用于IGrouping选择部分中的每一个，以使您的聚合操作正常工作。
var results = from line in Lines
group line by line.ProductCode into g
select new ResultLine {
ProductName = g.First().Name,
Price = g.Sum(pc => pc.Price).ToString(),
Quantity = g.Count().ToString(),
};
回答by Mahdi Jalali
sometimes you need to select some fields by FirstOrDefault()or singleOrDefault()you can use the below query:
有时您需要选择某些字段，FirstOrDefault()或者singleOrDefault()您可以使用以下查询：
List<ResultLine> result = Lines
.GroupBy(l => l.ProductCode)
.Select(cl => new Models.ResultLine
{
ProductName = cl.select(x=>x.Name).FirstOrDefault(),
Quantity = cl.Count().ToString(),
Price = cl.Sum(c => c.Price).ToString(),
}).ToList();
