Grouping in LINQ

Grouping in LINQ provides flexibility and reliability over for loops while manipulating DataTable data.  As the LINQ uses the power of Generics, we can write code that avoid issues related to type conversion.  

I am taking a simple example explain the grouping LINQ, I am loading data from xml file (it is similar to getting data from SQL server).  I have to display the data by monthly and yearly.  My xml file has the content refer end of the post.

If I need to show the data by monthly in a grid (grdSales) on my form - 

DataSet ds = new DataSet();

ds.ReadXml("Sales.xml");

var sales = from r in ds.Tables[0].AsEnumerable()

group r by new { month = r["Month"].ToString(), year = r["Year"].ToString() } into grp                

orderby grp.Key.year, grp.Key.month

     select new SalesDetails

     {

        Year = grp.Key.year,

        Month = grp.Key.month,

        Sales = grp.Sum(s => Convert.ToInt32(s["Price"].ToString()))

     };

List<SalesDetails> lstSales = sales.ToList();

//adding total row
lstSales.Add(
 sales       
      group r
(from r i
n by sales into grpTotal
ails
      {
         Year = 
      select new SalesDe
t"Total",
         Month = "",
 => s.Sales)
      }).ToList()[0]
     );
 
         Sales = grpTotal.Sum(
sgrdSales.DataSource = lstSales;

Sum() is the method from Enumerable class which is implements the the IEnumerable interface. refer the http://msdn.microsoft.com/en-us/library/system.linq.enumerable.aspx for other method of the class like Min, Max and Average.  These methods are overloaded to support datatypes like Int, Decimal etc.  And grp is the object of class which implements IGrouping.

Similarly to show data by year - 
DataSet ds = new DataSet();

ds.ReadXml("Sales.xml");
var sales = from r in ds.Tables[0].AsEnumerable()
    group r by r["Year"].ToString()into grp
    orderby grp.Key
    select new SalesDetails
    {
     Year = grp.Key,
     Month = "1",
     Sales = grp.Sum(s => Convert.ToInt32(s["Price"].ToString()))
    };


List lstSales = sales.ToList();
lstSales.Add(
      (from r in sales
       group r by sales into grpTotal
       select new SalesDetails
       {
        Year = "Total",
        Month = "",
        Sales = grpTotal.Sum(s => s.Sales)
      }).ToList()[0]
   );
grdSales.DataSource = lstSales;

One of the advantages of LINQ is, you can also have a List as one of the columns of your rows. As the following code populates a column with a List of month sales for the particular year.  

public struct MonthlySales
        {
           public string MonthName;
           public int MonthSale;
        }



var sales = from r in ds.Tables[0].AsEnumerable()
           group r by r["Year"].ToString() into grp
           orderby grp.Key
           select new SalesDetails
           {
              Year = grp.Key,
              monthSales =
                 (from rm in ds.Tables[0].AsEnumerable()
                   where rm["Year"].ToString() == grp.Key
                   select new MonthlySales
                   {
                     MonthName = rm["Month"].ToString(),
                     MonthSale = Convert.ToInt32(rm["Price"].ToString())
                   }).ToList(),
             Sales = grp.Sum(s => Convert.ToInt32(s["Price"].ToString()))
            };

The data populates like - 

My xml data from Sales.xml, and loaded data from database also should work.


<?xml version="1.0" encoding="utf-8" ?> <Sales>   <Sale><Item>item1</Item> <Price>2000</Price>  <Month>1</Month> <Year>2010</Year></Sale>   <Sale><Item>item2</Item><Price>3400</Price><Month>5</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>2200</Price><Month>7</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>1600</Price><Month>10</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item>  <Price>1200</Price> <Month>11</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item> <Price>500</Price><Month>12</Month>  <Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>2500</Price><Month>1</Month><Year>2010</Year></Sale>   <Sale><Item>item2</Item><Price>3400</Price><Month>5</Month><Year>2010</Year> </Sale>   <Sale><Item>item1</Item> <Price>2400</Price><Month>7</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>1600</Price><Month>10</Month> <Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>1000</Price> <Month>11</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>200</Price><Month>12</Month><Year>2010</Year></Sale>   <Sale><Item>item1</Item><Price>500</Price><Month>1</Month> <Year>2011</Year></Sale>   <Sale><Item>item2</Item><Price>3100</Price><Month>3</Month><Year>2011</Year></Sale>   <Sale><Item>item1</Item> <Price>2300</Price><Month>8</Month><Year>2011</Year></Sale>   <Sale><Item>item1</Item><Price>3400</Price><Month>11</Month><Year>2011</Year></Sale>   <Sale><Item>item1</Item><Price>2200</Price><Month>8</Month><Year>2011</Year></Sale>   <Sale><Item>item1</Item><Price>1200</Price> <Month>11</Month><Year>2011</Year></Sale>   <Sale> <Item>item1</Item><Price>2990</Price><Month>8</Month> <Year>2011</Year></Sale>   <Sale><Item>item1</Item><Price>1840</Price> <Month>11</Month><Year>2011</Year></Sale>   <Sale><Item>item1</Item><Price>1200</Price><Month>11</Month><Year>2011</Year></Sale>   <Sale><Item>item1</Item> <Price>6340</Price>  <Month>12</Month> <Year>2012</Year></Sale>   <Sale> <Item>item1</Item> <Price>9000</Price><Month>8</Month><Year>2012</Year></Sale>   <Sale> <Item>item1</Item><Price>1600</Price> <Month>9</Month><Year>2012</Year> </Sale>   <Sale> <Item>item1</Item><Price>1100</Price> <Month>7</Month> <Year>2012</Year></Sale>   <Sale><Item>item1</Item><Price>2300</Price><Month>8</Month><Year>2012</Year> </Sale>   <Sale><Item>item1</Item><Price>1800</Price> <Month>9</Month><Year>2012</Year></Sale>   <Sale><Item>item1</Item> <Price>1200</Price><Month>7</Month><Year>2012</Year> </Sale> </Sales>