I need to Group by Columns, and concatenate 1 column, and do the Sum for 1 Column.

1. Group by : 1st by Vendor then by Type

2. Concatenate by: Month

3. Sum by :NumberInvoice

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var InputData = new List<Invoice>()
            {
                new Invoice{ Vendor = "Microsoft", NumberInvoice= 10, Type= "PC", Month = "1" },
                new Invoice{ Vendor = "Microsoft", NumberInvoice= 10, Type= "PC", Month = "2" },
                new Invoice{ Vendor = "Microsoft", NumberInvoice= 10, Type= "Surface", Month = "1" },
                new Invoice{ Vendor = "Microsoft", NumberInvoice= 20, Type= "PC", Month = "1" },
                new Invoice{ Vendor = "Microsoft", NumberInvoice= 20, Type= "PC", Month = "2" },
                new Invoice{ Vendor = "Microsoft", NumberInvoice= 30, Type= "Surface", Month = "1" },
                new Invoice{ Vendor = "IBM", NumberInvoice= 50, Type= "Network", Month = "5" },
                new Invoice{ Vendor = "IBM", NumberInvoice= 60, Type= "Graphic Card", Month = "6" }
            };

            var result = (InputData.AsEnumerable() ?? throw new InvalidOperationException()).Select(
                    x => new
                    {
                        x.Vendor,
                        x.NumberInvoice,
                        x.Type,
                        x.Month
                    }
                ).GroupBy(
                    y => new
                    {
                        y.Vendor,
                        y.NumberInvoice,
                        y.Type,
                        y.Month
                    }
                )
                .Select(
                    a => new Invoice
                    {
                        Vendor = a.Key.Vendor,
                        NumberInvoice = a.Key.NumberInvoice,
                        Type = a.Key.Type,
                        Month = a.Key.Month,
                    }
                    ).ToList();
            foreach (var item in result)
            {
                Console.WriteLine(item.Vendor " "  item.NumberInvoice   " "   item.Type   " "   item.Month);
            }
            Console.ReadKey();
        }
        public class Invoice
        {
            public string Vendor { get; set; }
            public decimal NumberInvoice { get; set; }
            public string Type { get; set; }
            public string Month { get; set; }
        }
    }
}

And this is the output

Microsoft 10 PC 1
Microsoft 10 PC 2
Microsoft 10 Surface 1
Microsoft 20 PC 1
Microsoft 20 PC 2
Microsoft 30 Surface 1
IBM 50 Network 5
IBM 60 Graphic Card 6

The desired output should be like this:

Vendor       Type         Month    NumberInvoice
-------------------------------------------------
Microsoft    PC           1,2      60      
Microsoft    Surface      1        40   
IBM          Network      5        50     
IBM          Graphic Card 6        60      

As you see For Microsoft, Month 1 and 2 concatenated, and do the Sum for NumberInvoice. So I can accomplish this output?

CodePudding user response：

1. You should group by Vendor and Typeonly.

2. Perform .Sum() for NumberInvoice. And for Months, .Select() and .Distinct() to add the distinct months to the list.

3. Returns the result as List<GroupedInvoice>. (Create another class to hold the Months as a string list/array.

4. (Optional) The first .Select() to me is unnecessary, would suggest being removed.

var result = (InputData.AsEnumerable() ?? throw new InvalidOperationException())
    .GroupBy(
        y => new
        {
            y.Vendor,
            y.Type
        }
    )
    .Select(
        a => new GroupedInvoice
        {
            Vendor = a.Key.Vendor,
            Type = a.Key.Type,
            NumberInvoice = a.Sum(x => x.NumberInvoice),
            Month = a.Select(x => x.Month).Distinct().ToArray(),
        }
    ).ToList();

public class GroupedInvoice
{
    public string Vendor { get; set; }
    public decimal NumberInvoice { get; set; }
    public string Type { get; set; }
    public string[] Months { get; set; }
}

Demo @ .NET Fiddle