c# Get the sum of multiple datagridview columns values with linq query

Question

I'm trying to calculate the sum of multiple columns in a datagridview and then get the average.

This columns contains some values like "3x8" (are for workout training) and I want to calculate the sum of the first value (3) of multiple columns

The only result that I figured out it's to obtain a string sum like. So if I have 3x8 in the first column and 3x8 in the second, the result it's 33. Naturally, I want to obtain 6.

Here my datagridview example:

    Group   Val1 to sum    Val2 to sum
-------------------------------------
group1       3x8              3x8
-------------------------------------
group1       1x5              5x5
-------------------------------------
group3       2x20             2x10
-------------------------------------

Here the code that I'm using:

var Su = dataGridView1.Rows.Cast<DataGridViewRow>()
      .Where(row => row.Cells["Group"].Value != null)
      .Where(row => row.Cells["Val1"].Value != null)
      .GroupBy(row => row.Cells["Group"].Value.ToString())
    .Select(g => new
    {
        Group = g.Key,
        Sum = g.Sum(row => {
            int sum = 0;
            if (row.Cells["week1"].Value.ToString().Contains("x"))
            {
                Debug.WriteLine("true condition 1");
                sum += (Convert.ToInt32(row.Cells[1].Value.ToString().Split('x')[0] + row.Cells[2].Value.ToString().Split('x')[0])) ;
                return sum;
            }
            else {
                Debug.WriteLine("true else");
                return 0; }
        })

There is a way to do this?

Answer 1

You just need to parse the contents of the cells to comply with your 0x0 pattern and then add-up the numbers parsed. Quite a few ways to do that. If it wasn't datagridviewrows, but just an array of data, it could look like this, many variants thinkable:

     var gridsRows = new ITuple[] { // using value tuples
            ("group1", "3x8", "3x8"),
            ("group1", "1x5", "5x5"),
            ("group3",  "2x20", "2x10"),
            ("group4",  "14x4", "-")
        };  
    
     var parser = new Regex (@"^\s*(\d+)x\d+\s*$", RegexOptions.Compiled);

     var groupedSums = new Dictionary<string, int>(gridsRows
                .GroupBy(row => (string)row[0], (key, rows) => new KeyValuePair<string, int>(key, 
                         rows.Sum(r => ValOrZero(1, r) + ValOrZero(2, r)))));
          
      foreach (var sum in groupedSums)
          Console.WriteLine("{0}: {1,4}", sum.Key, sum.Value);

      /* Prints:
        group1:   12
        group3:    4
        group4:   14
      */
        
      int ValOrZero(int columnIndex, ITuple row) {
            return row[columnIndex] is string val
                && parser.Match(val) is { Success: true} m
            ?  int.Parse(m.Groups[1].Value) : 0;
      } 

"Translated" back to the WinForms DataGridView (and C# 7.3 langver):

 var parser = new Regex (@"^\s*(\d+)x\d+\s*$", RegexOptions.Compiled);

 var groupedSums = new Dictionary<string, int>( dataGridView1.Rows.Cast<DataGridViewRow>()
  .Where(row => row.Cells[0].Value != null)
  .Where(row => row.Cells[1].Value != null)
            .GroupBy(row => row.Cells[0].Value.ToString(), (key, rows) => new KeyValuePair<string, int>(key, 
                     rows.Sum(r => ValOrZero(row.Cells[1]) + ValOrZero(row.Cells[2])))));
      
  foreach (var sum in groupedSums)
      Console.WriteLine("{0}: {1}", sum.Key, sum.Value);
    
  int ValOrZero(DataGridViewCell cell) {
        return cell.Value?.ToString() is {} val
            && parser.Match(val) is var m && m.Success
        ?  int.Parse(m.Groups[1].Value) : 0;
  }