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

Question

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?

Answer 1

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;
    }