LINQ - GroupBy

• Download source - 29.8 KB

Introduction

Many of us face the problem of grouping elements by different attributes from C# code. We are here to solve these problems and provide the best practices when one wants to group different types of data by using the very best features provided by Microsoft .NET technologies, i.e. LINQ.

Background

The Syntax for LINQ - Group By

var result= from p in <any collection> group p by p.<property/attribute> into grps
                 select new 
                 {
                   Key=grps.Key,
                   Value=grps
                 }  

Using the Code

We can test the group by for different data types like XML, ADO.NET DataTable, Customer Objects. Here I will provide the solution for these three types of data now.

LINQ - GroupBy With XML

Create a console application in VS2012 and name it as LINQ-Grouping. Now edit your program.cs file and modify your file as shown below or simply copy paste the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data;
namespace Linq_Grouping
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter your choice.");
            int choice = int.Parse(Console.ReadLine());
            if (choice == 1)
                ExecuteLinqForXML();
            Console.ReadLine();
        }
        static void ExecuteLinqForXML()
        {
            Random rnd = new Random();
            XElement Customers = new XElement("Customers");
            for (int i = 0; i < 25; i++)
            {
                Customers.Add(
                    new XElement("Customer",
                        new XElement("Id", ("cust_id" + (i + 1))),
                        new XElement("Name", "Customer" + (i + 1)),
                        new XElement("Category", "Cat" + ((i + 1)) / 4),
                        new XElement("Sales", (rnd.Next(100, 1000)))
                        )
                    );
            }
            var results = from p in Customers.Descendants("Customer")
                          group p by p.Element("Category").Value into grps
                          select new
                          {
                              Key = grps.Key,
                              Values = grps,
                              TotalSales = grps.Sum
                              		(g => decimal.Parse(g.Element("Sales").Value)),
                              Total = grps.Count(),
                              AvgSales = grps.Average
                              		(g => decimal.Parse(g.Element("Sales").Value)),
                              MaxSales = grps.Max(g => decimal.Parse(g.Element("Sales").Value)),
                              MinSales = grps.Min(g => decimal.Parse(g.Element("Sales").Value))
                          };
            foreach (var result in results)
            {
                Console.WriteLine("Category - " + result.Key);
                Console.WriteLine("Total Sales : " + result.TotalSales + " 
                Average Sales : " + result.AvgSales + " Maximum Sales : " + 
                result.MaxSales + " Minimum Sales : " + result.MinSales);
                Console.WriteLine("ID\t\t\tName\t\t\tCategory\tSales");
                foreach (XElement Customer in result.Values)
                {
                    Console.WriteLine(
                        Customer.Element("Id").Value + "\t\t" +
                        Customer.Element("Name").Value + "\t\t" +
                        Customer.Element("Category").Value + "\t\t" +
                        Customer.Element("Sales").Value + "\t\t"
                        );
                }
            }
        }
    }
}  

LINQ - Group By With XML Explanation

Here I provide a choice to select among three different data types XML, Entities, and DataTable. I start with XML so my choice is 1 and my method for processing here is ExecuteLinqForXML() which will prepare the XML. The XML structure is as below:

<Customers>
  <Customer>
    <Id></Id>
    <Name></Name>
    <Category></Category>
    <Sales></Sales>
  </Customer>
  <Customer>
    <Id></Id>
    <Name></Name>
    <Category></Category> 
    <Sales></Sales> 
  </Customer>
</Customers>  

Now we have the LINQ query to process this XML which will process the XML and return the results like sum, total, maximum, minimum, average kind of results per category. we can group the results based on category.

The final result is as below:

LINQ - Group By With DataTable

The way we did in the previous scenario, we do the same here too we will prepare a table schema and insert 25 records and test the results.

Now add a new method with the name ExecuteLinqWithDataTable() which will prepare and insert data into a new DataTable. And the processing for the DataTable is the same as XML with Linq and results can be processed as shown below:

static void ExecuteLinqWithDataTable()
        {
            DataTable dtCustomers = new DataTable("Customers");
            dtCustomers.Columns.Add(new DataColumn("ID", typeof(string)));
            dtCustomers.Columns.Add(new DataColumn("Name", typeof(string)));
            dtCustomers.Columns.Add(new DataColumn("Category", typeof(string)));
            dtCustomers.Columns.Add(new DataColumn("Sales", typeof(decimal)));
            Random rnd = new Random();
            for (int i = 0; i < 25; i++)
            {
                DataRow dr = dtCustomers.NewRow();
                dr["ID"] = "Cust_" + (i + 1);
                dr["Name"] = "Customer-" + (i + 1);
                dr["Category"] = "Cat_" + ((i + 1) % 6);
                dr["Sales"] = rnd.Next(500, 1000);
                dtCustomers.Rows.Add(dr);
            }
            var results = from p in dtCustomers.AsEnumerable()
                          group p by p.Field<string>("Category") into grps
                          select new
                          {
                              Key = grps.Key,
                              Values = grps,
                              TotalSales = grps.Sum
                              		(g => g.Field<decimal>("Sales")),
                              Total = grps.Count(),
                              AvgSales = grps.Average
                              		(g => g.Field<decimal>("Sales")),
                              MaxSales = grps.Max(g => g.Field<decimal>("Sales")),
                              MinSales = grps.Min(g => g.Field<decimal>("Sales"))
                          };
            foreach (var result in results)
            {
                Console.WriteLine("Category - " + result.Key);
                Console.WriteLine("Total Sales : " + result.TotalSales + " 
                Average Sales : " + result.AvgSales + " Maximum Sales : " + 
                result.MaxSales + " Minimum Sales : " + result.MinSales);
                Console.WriteLine("ID\t\tName\t\t\tCategory\tSales");
                foreach (DataRow Customer in result.Values)
                {
                    Console.WriteLine(
                        Customer["ID"] + "\t\t" +
                        Customer["Name"] + "\t\t" +
                        Customer["Category"] + "\t\t" +
                        Customer["Sales"] + "\t\t"
                        );
                } 
            } 
        } 

The results are as follows:

LINQ - Group By With Entities

This is also as simple as the other two methods. We simply need to add a new class to our solution with properties as below:

public class Customer 
    { 
        public string ID { get; set; } 
        public string Name { get; set; } 
        public string Category { get; set; }
        public decimal Sales { get; set; } 
    } 

And add a new method to our program class to prepare and process the entities for our example as shown below:

static void ExecuteLinqWithEntities()
        {
            Random rnd = new Random();
            List<Customer> Customers = new List<Customer>();
            for (int i = 0; i < 25; i++)
                Customers.Add(new Customer
                 {
                     ID = "Cust" + (i + 1),
                     Category = "Cat_" + ((i + 1) % 6),
                     Name = "Customer_" + (i + 1),
                     Sales = rnd.Next(500, 1000)
                 });
            var results = from p in Customers
                          group p by p.Category into grps
                          select new
                          {
                              Key = grps.Key,
                              Values = grps,
                              TotalSales = grps.Sum(g => g.Sales),
                              Total = grps.Count(),
                              AvgSales = grps.Average(g => g.Sales),
                              MaxSales = grps.Max(g => g.Sales),
                              MinSales = grps.Min(g => g.Sales),
                          };
            foreach (var result in results)
            {
                Console.WriteLine("Category - " + result.Key);
                Console.WriteLine("Total Sales : " + result.TotalSales + " 
                Average Sales : " + result.AvgSales + " Maximum Sales : " + 
                result.MaxSales + " Minimum Sales : " + result.MinSales);
                Console.WriteLine("ID\t\tName\t\t\tCategory\tSales");
                foreach (Customer Customer in result.Values)
                {
                    Console.WriteLine(
                        Customer.ID + "\t\t" +
                        Customer.Name + "\t\t" +
                        Customer.Category + "\t\t" +
                        Customer.Sales + "\t\t"
                        );
                }
            }
        } 

And the final results are as below:

Points of Interest

All of the above explain how to group the elements from different sources of data and aggregate them in different ways like Minimum, Maximum, Average, Total and Sum.

History

• Initial version of the article