Linq query to Count record in each Group using C# and VB.Net in ASP.Net

Hi v@run,

Refer below sample and correct your code according to your database.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="gvIdCount" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Count" HeaderText="Count" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using NorthwindModel;

VB.Net

Imports NorthwindModel
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        NorthwindEntities enity = new NorthwindEntities();
        var result = from order in enity.Orders
                        join employee in enity.Employees
                        on order.EmployeeID equals employee.EmployeeID into details
                        group details by order.EmployeeID into grouped
                        select new
                        {
                            EmployeeID = grouped.Key,
                            Count = grouped.Count()
                        };
        DataTable dt = new DataTable();
        dt.Columns.Add("Id");
        dt.Columns.Add("Count");
        foreach (var item in result)
        {
            dt.Rows.Add(item.EmployeeID, item.Count);
        }
        this.gvIdCount.DataSource = dt;
        this.gvIdCount.DataBind();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim enity As NorthwindEntities = New NorthwindEntities()
        Dim result = From order In enity.Orders
                    Join employee In enity.Employees On order.EmployeeID Equals employee.EmployeeID
                        Group order By key = New With {.EmployeeID = order.EmployeeID} Into Group
                        Select New With {.EmployeeID = key.EmployeeID, .Count = Group.Count()}

        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("Id")
        dt.Columns.Add("Count")

        For Each item In result
            dt.Rows.Add(item.EmployeeID, item.Count)
        Next

        Me.gvIdCount.DataSource = dt
        Me.gvIdCount.DataBind()
    End If
End Sub

Screenshot