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(
(from r i
n by sales into grpTotal
select new SalesDe
t"Total",
Month = "",
=> s.Sales)
}).ToList()[0]
);
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>
