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.
 |
| 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 - 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 belowpublic 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 |
Comments
Post a Comment