In this article I will explain with an example, how to use Group By Clause in LINQ with Entity Framework using C# and VB.Net.
This article will illustrate how to use Group By Clause in LINQ with Entity Framework and then later use it to populate GridView control in ASP.Net with C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Configuring and connecting Entity Framework to database in ASP.Net
Now I will explain the steps to configure and add Entity Framework and also how to connect it with the database in ASP.Net.
You will need to add Entity Data Model to your project by right clicking the Solution Explorer and then click on Add and then New Item option of the Context Menu.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
From the Add New Item window, select ADO.NET Entity Data Model and set its Name as NorthwindModel and then click Add.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
Then the Entity Data Model Wizard will open up where you need to select EF Designer database option.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
Now the wizard will ask you to connect and configure the Connection String to the database.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
You will need to select the
1. SQL Server Instance.
2. Database.
And then click Test Connection to make sure all settings are correct.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
Once the Connection String is generated, click Next button to move to the next step.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
Next you will need to choose the Entity Framework version to be used for connection.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
Now you will need to choose the Tables, you need to connect and work with Entity Framework. Here Customers Table needs to be selected.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
The above was the last step and you should now have the Entity Data Model ready with the Customers Table of the Northwind Database.
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
HTML Markup
The following HTML Markup consists of an ASP.Net GridView control with two BoundField columns.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width = "120" />
<asp:BoundField DataField="Count" HeaderText="Count" ItemStyle-Width = "80"/>
</Columns>
</asp:GridView>
Populating GridView using Group By Clause in LINQ with Entity Framework
Inside the Page Load event, the GridView is populated using the BindGrid method.
The records from the Customers Table is grouped with Country column and the count of Customers in each Country is calculated.
The Grouping is done using LINQ.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
protected void BindGrid()
{
using (NorthwindEntities entities = new NorthwindEntities())
{
var countries = (from customer in entities.Customers
group customer by customer.Country into cGroup
select new
{
Country = cGroup.Key,
Count = cGroup.Count()
}).Take(10);
gvCustomers.DataSource = countries;
gvCustomers.DataBind();
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindGrid()
End If
End Sub
Protected Sub BindGrid()
Using entities As NorthwindEntities = New NorthwindEntities()
Dim countries = (From customer In entities.Customers _
Group customer By customer.Country Into cGroup = Group, Count() _
Select New With { _
.Country = Country,
.Count = cGroup.Count()
}).Take(10)
gvCustomers.DataSource = countries
gvCustomers.DataBind()
End Using
End Sub
Screenshot
![Using Group By Clause in LINQ with Entity Framework using C# and VB.Net]()
Downloads
