Group By Sum in C# LINQ



Introduction


Before you get into this article,

If you did enough google to find solution for summing up an item in a group, yes thank you for landing here. This article describes calculating sum of a group in an object model using LINQ C# program.  



Download Source Code   

Final Result

Before you could download or read this article, let's view the result of calculating sum of an item in a group.

This image shows before and after calculating sum of marks of Student ID

Group by sum of marks
.


Download Instructions

Please ignore this step, If you have downloaded source code. If not after clicking above download link redirects to source repository as image below download the zipped file ( C# Visual Studio Project). 

Download Instructions

How do you define your problem here?

Yes that's the right question. 

Let me describe a bit, In a school pick a class of students associated with subjects like (English, Math's, Science, Chemistry, Physics) marks. And noted each student is identified with an unique number called Student ID, now you would like to calculate sum of marks for each students in a class. 

This is simple, apply group by in student class property to get result.

No, this isn't the appropriate answer. 

Now consider you are tricked by there are duplicate Student ID's and Marks of each subject - How to handle duplicates?

This is where LINQ helps complicated problems in a line of code.

Before go into the solution, Let's  design data model for student marks

In below class property ID denotes student Id and property Marks denotes subject marks student has scored.

 

public class StudentMarks
{ public int ID { get; set; } public int Marks { get; set; } }

Add Data to Student Marks?

In this practice, I used DataTable  to add records into it.  Why I used data table and not the latest class properties the reason is in this article below section explains of converting from List to a DataTable. 

Below code section, adding data to data rows of two columns ID and Marks. If you note on the entry ID has duplicated 1 and 2. 

 
public static DataTable GetDataSource()
{ DataTable table = new DataTable(); table.Columns.Add("ID", typeof(int)); table.Columns.Add("Mark", typeof(int)); table.Rows.Add(1, 50); table.Rows.Add(1, 30); table.Rows.Add(2, 0); table.Rows.Add(2, 100); table.Rows.Add(3, 45); return table; }











Let's say this is the input, and you are expecting an output with group by ID sum


What is the Expected Result?

The data grouped by ID sum of marks value 1 which has marks 30 and 50 becomes 80, and similarly ID 2 marks of 0 and 100 becomes 100, then ID 3 mark is 45 remains. 


1  50 + 30  = 80

2  0 + 100 = 100

3  45   = 45


IDMARKS
180
2100
345


How LINQ Group By Applied?

To ensure the below namespace is added in your program  

using System.Linq;

In this code, group by is applied on ID column and sum on the Marks columns, and as the result returned as List object.

As stated in above segment, list object is converted to DataTable by a method PropertiesToDataTable.

 
public static DataTable ApplyGroupBy()
{ 
   var result = (from p in GetDataSource().AsEnumerable()
                          group p by p["ID"] into r
                          select new
                          {
                              ID = r.Key,
                              Mark = r.Sum((s) => decimal.Parse(s["Mark"].ToString()))
                          }).ToList();

   DataTable dtOutput = PropertiesToDataTable(result);
   return dtOutput;
}




How to convert a List to DataTable?

After group by applied the returned result is List of object, use below method to convert to DataTable.


public static DataTable PropertiesToDataTable<T>(this List<T> source)
{
    DataTable dt = new DataTable();
    var props = TypeDescriptor.GetProperties(typeof(T));
    foreach (PropertyDescriptor prop in props)
    {
        DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType);
        dc.Caption = prop.DisplayName;
        dc.ReadOnly = prop.IsReadOnly;
    }
    foreach (T item in source)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyDescriptor prop in props)
        {
            dr[prop.Name] = prop.GetValue(item);
        }
        dt.Rows.Add(dr);
    }
    return dt;
}




The Complete Source Code

In case you feel above is segregated too many; here is the full source code.


using System;
using System.Data; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.ComponentModel; namespace LinqDemo { public static class Filter { public static DataTable GetDataSource() { DataTable table = new DataTable(); table.Columns.Add("ID", typeof(int)); table.Columns.Add("Mark", typeof(int)); table.Rows.Add(1, 50); table.Rows.Add(1, 30); table.Rows.Add(2, 0); table.Rows.Add(2, 100); return table; } public static DataTable ApplyGroupBy() { var result = (from p in GetDataSource().AsEnumerable() group p by p["ID"] into r select new { ID = r.Key, Mark = r.Sum((s) => decimal.Parse(s["Mark"].ToString())) }).ToList(); DataTable dtOutput = PropertiesToDataTable(result); return dtOutput; } /// <summary> /// Convert List to DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="source"></param> /// <returns></returns> public static DataTable PropertiesToDataTable<T>(this List<T> source) { DataTable dt = new DataTable(); var props = TypeDescriptor.GetProperties(typeof(T)); foreach (PropertyDescriptor prop in props) { DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType); dc.Caption = prop.DisplayName; dc.ReadOnly = prop.IsReadOnly; } foreach (T item in source) { DataRow dr = dt.NewRow(); foreach (PropertyDescriptor prop in props) { dr[prop.Name] = prop.GetValue(item); } dt.Rows.Add(dr); } return dt; } } }

Summary

So far we have learned how to apply group by using C# LINQ technique and calculate a sum marks of a student and converting from list to a data table.

And, finally here is the complete C# source code to download. 




Download Source Code