Merge Datatable records using LINQ

Question

I have a DataTable as like below,

Company      Manager       Location      Count1      Count2      Count3
------------ ------------- ------------- ----------- ---------- --------
C1           Mgr           LocName       1           0            0  
C1           Mgr           LocName       0           3            0
C1           Mgr           LocName       0           0            7
C2           Mgr2          LocName2      0           0            5

But, I want to display this table as,

Company      Manager       Location      Count1      Count2      Count3
------------ ------------- ------------- ----------- ---------- --------
C1           Mgr           LocName       1           3            7  
C2           Mgr2          LocName2      0           0            5  

Can any one help me... Thanks in adv.

Answer 1

You can use Enumerable.GroupBy + Sum:

var companyGroups = tblCompany.AsEnumerable()
    .GroupBy(row => new {
        Company = row.Field<string>("Company"),
        Manager = row.Field<string>("Manager"),
        Location = row.Field<string>("Location"),
    });
// creates an empty DataTable with the same columns:
var merge = tblCompany.Clone();  
foreach(var companyGroup in  companyGroups)
{
    merge.Rows.Add(
        companyGroup.Key.Company,
        companyGroup.Key.Manager,
        companyGroup.Key.Location,
        companyGroup.Sum(row => row.Field<int>("Count1")),
        companyGroup.Sum(row => row.Field<int>("Count2")),
        companyGroup.Sum(row => row.Field<int>("Count3")));
}

Your sample data yields the desired result:

var tblCompany = new DataTable();
tblCompany.Columns.Add("Company", typeof(string));
tblCompany.Columns.Add("Manager", typeof(string));
tblCompany.Columns.Add("Location", typeof(string));
tblCompany.Columns.Add("Count1", typeof(int));
tblCompany.Columns.Add("Count2", typeof(int));
tblCompany.Columns.Add("Count3", typeof(int));

tblCompany.Rows.Add("C1", "Mgr", "LocName", 1, 0, 0);
tblCompany.Rows.Add("C1", "Mgr", "LocName", 1, 3, 0);
tblCompany.Rows.Add("C1", "Mgr", "LocName", 0, 0, 7);
tblCompany.Rows.Add("C2", "Mgr2", "LocName2", 0, 0, 5);

If you instead want to take the first value != 0 of each group:

foreach (var companyGroup in companyGroups)
{
    merge.Rows.Add(
        companyGroup.Key.Company,
        companyGroup.Key.Manager,
        companyGroup.Key.Location,
        companyGroup
            .Select(row => row.Field<int>("Count1"))
            .Where(i => i != 0)
            .FirstOrDefault(),
        companyGroup
            .Select(row => row.Field<int>("Count2"))
            .Where(i => i != 0)
            .FirstOrDefault(),
        companyGroup
            .Select(row => row.Field<int>("Count3"))
            .Where(i => i != 0)
            .FirstOrDefault());
}

For the sake of completeness, if you need to modify the original table instead of creating a new one:

foreach (var companyGroup in companyGroups)
{
    // modify just the first row of each group, 
    // all others will be removed after the merge
    DataRow first = companyGroup.First();
    first.SetField("Count1", companyGroup.Sum(row => row.Field<int>("Count1")));
    first.SetField("Count2", companyGroup.Sum(row => row.Field<int>("Count2")));
    first.SetField("Count3", companyGroup.Sum(row => row.Field<int>("Count3")));
    foreach (DataRow other in companyGroup.Skip(1))
        tblCompany.Rows.Remove(other);
}

Answer 2

Group rows by three columns (Company, Manager, Location) and calculate sum for Count fields:

var query = from r in table.AsEnumerable()
            group r by new { 
               Company = r.Field<string>("Company"),
               Manager = r.Field<string>("Manager"),
               Location = r.Field<string>("Location")
            } into g
            select new {
               g.Key.Company,
               g.Key.Manager,
               g.Key.Location,
               Count1 = g.Sum(r => r.Field<int>("Count1"),
               Count2 = g.Sum(r => r.Field<int>("Count2"),
               Count3 = g.Sum(r => r.Field<int>("Count3")
            };

And use custom CopyToDataTable() method for custom types to create new DataTable (if you need it to be DataTable) from this query:

DataTable result = query.CopyToDataTable();

---

If it is possible to do grouping on server side with Linq to Sql, then query will look like:

var query = from x in db.TableName
            group r by new { 
               x.Company,
               x.Manager,
               x.Location
            } into g
            select new {
               g.Key.Company,
               g.Key.Manager,
               g.Key.Location,
               Count1 = g.Sum(x => x.Count1),
               Count2 = g.Sum(x => x.Count2),
               Count3 = g.Sum(x => x.Count3)
            };

In that case you will avoid downloading all data and calculations on client.