GroupBy columns of DataTable and calculate sum of string column

Question

I have the following DataTable:

var dtTimephasedStatusTemp = new DataTable();
dtTimephasedStatusTemp.Columns.Add("Task Id", typeof(string));
dtTimephasedStatusTemp.Columns.Add("TimeByDay", typeof(string));
dtTimephasedStatusTemp.Columns.Add("TaskActualWork", typeof(string));
dtTimephasedStatusTemp.Columns.Add("TaskWork", typeof(string));

Output of the table is shown below. It may have different Task Ids:

Task Id                                TimeByDay    TaskActualWork  TaskWork
528890be-4858-e811-a74e-b0359f8878e9    1/2/2018    8   8
528890be-4858-e811-a74e-b0359f8878e9    1/3/2018    8   8
528890be-4858-e811-a74e-b0359f8878e9    1/4/2018    8   8
528890be-4858-e811-a74e-b0359f8878e9    1/5/2018    8   8
528890be-4858-e811-a74e-b0359f8878e9    1/8/2018    8   8
528890be-4858-e811-a74e-b0359f8878e9    1/9/2018    2   2
528890be-4858-e811-a74e-b0359f8878e9    2/1/2018    0.8 0.8
528890be-4858-e811-a74e-b0359f8878e9    2/2/2018    0.8 0.8
528890be-4858-e811-a74e-b0359f8878e9    2/5/2018    0.8 0.8
528890be-4858-e811-a74e-b0359f8878e9    2/6/2018    0.8 0.8
528890be-4858-e811-a74e-b0359f8878e9    2/7/2018    5.6 5.6

What I'm trying to do is to group by TaskId and calculate the sum of TaskActualWork and TaskWork using Linq. What I have done to achieve is this:

    DataTable dtTimephasedStatus = dtTimephasedStatusTemp.Clone();
    dtTimephasedStatus.Columns["TaskActualWork"].DataType = typeof(double);
    dtTimephasedStatus.Columns["TaskWork"].DataType = typeof(double);
    foreach (DataRow row in dtTimephasedStatusTemp.Rows)
    {
        dtTimephasedStatus.ImportRow(row);
    }

    var result = dtTimephasedStatus.AsEnumerable().
        GroupBy(x => new { TaskId = x.Field<string>("Task Id") }).
        Select(x => new
        {
            TaskId = x.Key.TaskId,
            TaskActualWorkSum = x.Sum(y => y.Field<double>("TaskActualWork")),
            TaskWorkSum = x.Sum(y => y.Field<double>("TaskWork"))
        });

Is there any other way to achieve the same thing using Linq?

What is wrong with your current approach? are you not getting the expected result?? — sujith karivelil, Aug 07 '18 at 10:30
That additional foreach loop to convert string to double is time taking. I just need an alternate opinion if available. — Sahil Sharma, Aug 07 '18 at 10:32

score 0 · Accepted Answer · answered Aug 07 '18 at 10:46

Obviously, the ideal solution would be to fill the data table with doubles (or, even better: decimals) instead of strings in the first place, but I'll assume that you are working with a legacy system where that is not possible.

Currently, you copy the whole data table just to change the data type of two columns. You can avoid that by converting the values when summing over them:

var result = dtTimephasedStatusTemp.AsEnumerable().
    GroupBy(x => new { TaskId = x.Field<string>("Task Id") }).
    Select(x => new
    {
        TaskId = x.Key.TaskId,
        TaskActualWorkSum = x.Sum(y => Double.Parse(y.Field<string>("TaskActualWork"))),
        TaskWorkSum = x.Sum(y => Double.Parse(y.Field<string>("TaskWork")))
    });

Alternatively, you might want to consider adding a new column to your data table and filling it in a loop. That, also, might be more efficient than copying the complete table.

You are right, this is an old legacy system so any modifications are out of the question. Thanks for the suggestion, it will save me a lot of time. — Sahil Sharma, Aug 07 '18 at 11:24

GroupBy columns of DataTable and calculate sum of string column

1 Answers1