Click here to Skip to main content
Click here to Skip to main content

LINQ - GroupBy

, 21 Jan 2013
Rate this:
Please Sign up or sign in to vote.
This tip provides an easy way of dealing with different types of data sources for LINQ Group By taken from my blog http://www.srinetinfo.com/2012/12/linq-group-by.html

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:

Linq With Entities Results

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Srinivasu Pemma
Technical Lead
India India
Working as Technical Lead for last few years and has very good exposure to ms.net technologies
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 Pinmembergoodyboy30-Jul-13 23:15 
GeneralMy vote of 3 Pinmembermbsmbs31-Mar-13 3:08 
GeneralThank you Pinmembergmangsxr75022-Feb-13 4:57 
GeneralMy vote of 5 PinmemberSRIRAM 228-Jan-13 19:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 22 Jan 2013
Article Copyright 2012 by Srinivasu Pemma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid