Find duplicate and merge record into single datatable c#

c#, datatable, group-by, linq, sum

Solution

var result = table.AsEnumerable()
            .GroupBy(r => r.Field<string>("Version"))
            .Select(g =>
                {
                    var row = table.NewRow();
                    row.ItemArray = new object[]
                        {
                            g.Key, 
                            g.Sum(r => r.Field<int>("Value"))
                        };
                    return row;
                }).CopyToDataTable();

Edit:

If you want to keep other field, try below:

var result = table.AsEnumerable()
            .GroupBy(r => new
                {
                     Version = r.Field<String>("Version"),
                     Col1 =  r.Field<String>("Col1"),
                     Col2 =  r.Field<String>("Col2")
                })
            .Select(g =>
                {
                    var row = g.First();
                    row.SetField("Value", g.Sum(r => r.Field<int>("Value")));
                    return row;
                }).CopyToDataTable();

Problem

I am able to find the duplicates out of DataTable rows. Like following: ``` var groups = table.AsEnumerable() .GroupBy(r => new { c1 = r.Field<String>("Version"), }); var tblDuplicates = groups .Where(grp => grp.Count() > 1) .SelectMany(grp => grp) .CopyToDataTable(); ``` Now, I want to merge all the duplicate records in to single and sum it's Value column value. Pretty much like following: DataTable with Duplicates: ``` Version Value 1 2 2 2 2 1 1 3 2 1 3 2 ``` DataTable with no duplicates and Value summed.: ``` Version Value 1 5 2 4 3 2 ``` I am aware about this link which does this with the help of reflection. http://forums.asp.net/t/1570562.aspx/1 Anyother way to do it? Edit: However, if I have more than two columns, like five columns and I still want to do the sum on Value column and also need other columns data in resulatant summed datatable. How to do it? Here I get the Version and Value in my result DataTable. I want other columns with values also. Like following: Version col1 col2 Value ``` 1 A A 2 2 B B 2 2 B B 1 1 A A 3 2 B B 1 3 C C 2 ```

Original source