PaypalMe

Sunday, 23 December 2012

LINQ GroupBy

Introduction:

Many of us face the problem of grouping elements by different attributes from C# code. Now we are here to solve these problem and provide the best practices when one wants to group different types of data by using the very best features provide microsoft .net technologies. i.e. LINQ.

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
                 }

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 below 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 am providing a choice to select among three different data types XML, Entities, and DataTable. I am starting 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 results is as below.


LINQ TO XML GroupBy Results
Final Results Grouped By Category
This is the way to group xml by different elements. Which is simple and the very best practice to work with xml using LINQ.

Linq - Group By With DataTable

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

Now add a new method to with the name ExecuteLinqWithDataTable(). Which will prepare and insert data into a new DataTable. And the processing for the DataTable is as 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 will be as below
Linq - Gorup By With DataTable Results
Linq - Group By With DataTable Results

Linq - Group By With Entities

   This is also as simple as other two methods. Simply need to add a news 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 - Group By with Entities Results
Linq - Group By with Entities Results
The above all explains how to group the elements from different source of data and aggregate them in different ways like Minimum, Maximum, Average, Total and Sum.

You can download this sample Code here


No comments:

Post a Comment