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

Last Reply 4 months ago By pandeyism

Posted 4 months ago

Im having two Table
In table 1:
Im having Column of (id) which is Unique

In table 2 :
Im Having Column Of UserId in which i'm storing the value of (id)
from table one

Now I want to Know the count of (id) in UserId column from table 2.

And whichever (id) has been assigned less time .
I want to make that Id available.

And whichever (id) has been assigned less time--This i will know by using Count.

I have tried alot ..but im not getting such type of Linq query

can u please help

 

 

Posted 4 months ago

Hi v@run,

Refer below sample.

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