How can I transform a DataTable in a group-by date DataTable with LINQ?

I have a untyped DataTable that looks like this (srcTbl):

date            col_1   col_2 ...  col_n
1.3.2010 00:00  12.5    0     ...  100 
1.3.2010 01:00  0       0     ...  100 
1.3.2010 22:开发者_如何学编程00  0       0     ...  100 
1.3.2010 23:00  12.5    0     ...  100 
...
31.3.2010 00:00 2       0     ...  100
31.3.2010 01:00 2       0     ...  200

I need to sum up the rows grouped by dates to get a DataTable like that (dstTbl):

date,           col_1   col_2 ...  col_n
1.3.2010        15      0     ...  400
...
31.3.2010       4       0     ...  300

Is this possible by using LINQ and if then how?

Okay, So you can achieve it like this. This is a working version so you shouldn't have much problem.

 


DataTable myDataTable = new DataTable();
            myDataTable.Columns.Add("Date", typeof(DateTime));
            myDataTable.Columns.Add("Col1", typeof(int));
            myDataTable.Columns.Add("Col2", typeof(int));
            myDataTable.Columns.Add("Col3", typeof(int));

        var datarow1 = myDataTable.NewRow();
        datarow1.SetField("Date", DateTime.Parse("01/03/09 00:01:00"));
        datarow1.SetField("Col1", 12);
        datarow1.SetField("Col2", 0);
        datarow1.SetField("Col3", 100);

        var datarow2 = myDataTable.NewRow();
        datarow2.SetField("Date", DateTime.Parse("01/03/09 01:03:00"));
        datarow2.SetField("Col1", 12);
        datarow2.SetField("Col2", 0);
        datarow2.SetField("Col3", 100);

        var datarow3 = myDataTable.NewRow();
        datarow3.SetField("Date", DateTime.Parse("01/03/09 02:05:00"));
        datarow3.SetField("Col1", 0);
        datarow3.SetField("Col2", 0);
        datarow3.SetField("Col3", 100);

        var datarow4 = myDataTable.NewRow();
        datarow4.SetField("Date", DateTime.Parse("10/10/09 00:03:00"));
        datarow4.SetField("Col1", 2);
        datarow4.SetField("Col2", 0);
        datarow4.SetField("Col3", 100);

        var datarow5 = myDataTable.NewRow();
        datarow5.SetField("Date", DateTime.Parse("31/03/09 01:03:00"));
        datarow5.SetField("Col1", 2);
        datarow5.SetField("Col2", 0);
        datarow5.SetField("Col3", 100);

        var datarow6 = myDataTable.NewRow();
        datarow6.SetField("Date", DateTime.Parse("31/03/09 03:04:00"));
        datarow6.SetField("Col1", 2);
        datarow6.SetField("Col2", 0);
        datarow6.SetField("Col3", 100);

        myDataTable.Rows.Add(datarow1);
        myDataTable.Rows.Add(datarow2);
        myDataTable.Rows.Add(datarow3);
        myDataTable.Rows.Add(datarow4);
        myDataTable.Rows.Add(datarow5);
        myDataTable.Rows.Add(datarow6);

        var q = (from t in myDataTable.AsEnumerable()
                 group t by new {t.Field<DateTime>("Date").Day, t.Field<DateTime>("Date").Month, t.Field<DateTime>("Date").Year}
                 into temp
                    select myDataTable.LoadDataRow(
                    new object[]
                        {
                            string.Format("{0}.{1}.{2}", temp.Key.Day, temp.Key.Month, temp.Key.Year),
                            temp.Sum(r => r.Field<int>("Col1")),
                            temp.Sum(r => r.Field<int>("Col2")),
                            temp.Sum(r => r.Field<int>("Col3")),
                        }, LoadOption.PreserveChanges));

        DataTable newTable = q.CopyToDataTable();

I found a way to solve my problem, but I have to handle each column seperate in a for loop. Maybe somebody has an idea how to spare this.

    public static DataTable HoursToDailySums(DataTable src_tbl)
    {
        DataTable dst_tbl = src_tbl.Clone();

        // handle the dates column
        var qry = from row in src_tbl.AsEnumerable()
                  group row by ((DateTime)row[0]).Date into g
                  orderby g.Key
                  select g.Key;

        foreach(DateTime date in qry)
        {
            DataRow new_row = dst_tbl.NewRow();
            new_row[0] = date;
            dst_tbl.Rows.Add(new_row);
        }

        // handle all other columns
        for (int col = 1; col < dst_tbl.Columns.Count; col++)
        {
            var sumQry = from row in src_tbl.AsEnumerable()
                         group row by ((DateTime)row[0]).Date into g
                         orderby g.Key
                         select g.Sum(row => row.Field<double>(col));

            int row_index = 0;
            foreach(double sum in sumQry)
            {
                dst_tbl.Rows[row_index++][col] = sum;
            }
        }
        return dst_tbl;
    }