How to create Total column and Total row using LINQ?

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.

What does your datasource look like? It is Entity framework objects? A datatable? A collection of objects? Show some code! — RQDQ, Apr 08 '11 at 14:34

Mark Cidade · Answer 1 · 2011-04-08T14:56:59.640

4

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

edited Apr 08 '11 at 14:56

answered Apr 08 '11 at 14:42

Mark Cidade

98,437
31
224
236

Can the name of the number columns be derived through reflection? I'd like to be able to use this in a more generic way. – O.O Apr 08 '11 at 14:45
In order to use anonymous types, you need to hard-code the property name. – Mark Cidade Apr 08 '11 at 14:56
Eek, that seems very limiting – O.O Apr 08 '11 at 14:58
You could maybe build a new DataRow instead of an anonymous type? – Rup Apr 08 '11 at 15:07
In the end it will have to be a datarow for the total row, and a datacolumn for a total column, anonymous types will not fly with a datatable. – O.O Apr 08 '11 at 15:11

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

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

edited May 23 '17 at 12:22

Community

1
1

answered Aug 11 '14 at 08:34

Ice_Drop

452
1
9
19

score 0 · Answer 3 · answered Apr 08 '11 at 14:38

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.

This could be translated to work with a data table, when answered that detail wasn't there. — Mitchel Sellers, Apr 13 '11 at 12:30

How to create Total column and Total row using LINQ?

3 Answers3

Linked