Linq: GroupBy, Sum and Count

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.

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.

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!

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

Solution 1:

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.

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.

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?

Solution 2:

The following query works. It uses each group to do the select instead of SelectMany. SelectMany works on each element from each collection. For example, in your query you have a result of 2 collections. SelectMany gets all the results, a total of 3, instead of each collection. The following code works on each IGrouping in the select portion to get your aggregate operations working correctly.

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(),
              };

Solution 3:

sometimes you need to select some fields by FirstOrDefault() or singleOrDefault() you can use the below query:

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