Grouping and Aggregating When Querying LINQ to SQL - Aggregations in LINQ (like GROUP BY of SQL in LINQ)
(Page 3 of 5 )
In all of the above sections, we simply worked with group (or aggregation) functions. We didn’t really aggregate data based on certain criteria.
Say I would like to have a report of “Customer wise number of orders.” To achieve this, the following is the SQL SELECT:
SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID
The above SELECT with GROUP BY can be written in LINQ (VB.NET) as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into Count() _
Select _
CustomerID, _
NoOfOrders = Count
Me.GridView1.DataSource = q
Me.GridView1.DataBind()
In C# LINQ, it would be as follows:
var q = from p in db.Orders
group p by p.CustomerID
into g
select new { CustomerID = g.Key, NoOfOrders = g.Count() };
this.GridView1.DataSource = q;
this.GridView1.DataBind();
In VB.NET LINQ, we have the flexibility to write the same query in different ways as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into Group _
Select CustomerID, NoOfOrders = Group.Count
Dim q = From p In db.Orders _
Group By p.CustomerID _
Into OrderCount = Count()
I prefer the last (for VB.NET), as it is quite simple and clear.
Next: Aggregations in LINQ (like GROUP BY of SQL in LINQ): continued >>
More .NET Articles
More By Jagadish Chaterjee
