How to create Total column and Total row using LIN

Question

Given a datatable that does not have a total column or row. How can I use LINQ to add a Total Column and Total row to create the table below?

TextColumn, NumberColumn0, NumberColumn1 Total
A                1              4          5
B                2              55         47
C                2.3            DBNULL     2.3
D                4              3          7
Total            9.3            62         61.3

Thanks!

Um, for those who need code. I'm using this currently for total column:

public static void CreateTotalColumnByType(DataTable table, Type valueType)
{
    // create expression
    string expression = string.Empty;

    // do not count the first column (used for string field)
    for (int columnIndex = 1; columnIndex < table.Columns.Count; ++columnIndex)
    {
        if (columnIndex != table.Columns.Count - 1)
        {
            // add +
            expression += String.Format("[{0}] + ", table.Columns[columnIndex].ColumnName);
        }
        else
        {
            // last column so don't add plus
            expression += String.Format("[{0}]", table.Columns[columnIndex].ColumnName);
        }
    }

    // add total column
    DataColumn totalColumn = new DataColumn("Total", valueType, expression);
    table.Columns.Add(totalColumn);
}

but I'd like to replace it with LINQ.

Answer 1

//adds Total column
var q1 = src.Select(x=>new { x.TextColumn
                            ,x.NumberColumn0
                            ,x.NumberColumn1
                            ,Total=x.NumberColumn0+x.NumberColumn1});

//adds Totals row
var q2 = q1.Concat(new[]{new{ TextColumn="Total"
                             ,NumberColumn0 = src.Sum(_=>_.NumberColumn0)
                             ,NumberColumn1 = src.Sum(_=>_.NumberColumn1)
                             ,Total=q1.Sum(_=>_.Total)}});

Answer 2

There are a number of ways of doing this, depending on how you are getting this back you wouldn't even need to use LINQ.

You could do the following if you are using standard classes for data transport.

public class MyDataClass
{
    public string TextColumn {get; set;}
    public int NumberColumn0 {get; set;}
    public int NumberColumn1 {get; set;}
    public int Total
    {
        get { return NumberColumn0 + NumberColumn1; }
    }    
}

This way you have no need for LINQ. If you REALLY want to use LINQ you could use

var results = from x in MyDataList
              select new { TextColumn = x.TextColumn, NumberColumn0 = x.NumberColumn0, 
                           NumberColumn1 = x.NumberColumn1,
                           Total = x.NumberColumn1 + x.NumberColumn0};

If you have a variable number of columns or something similar, you might not have as much luck using LINQ, we would need to see more to get a true idea of what you are doing.

Answer 3

You could try like this one.

For each col As DataGridViewColumn in grid.Columns
    '' You could try adding if here if there are exception columns
    gRID.Item(col.Index, 0).Value = gRID.Rows.Cast(Of DataGridViewRow)().AsEnumerable().Sum(Function(c) Convert.ToDecimal(c.Cells(col.Index).Value)).ToString()
Next

I just enhance p.cambell's solution a bit