Linq with group by having count using C# and VB.Net in ASP.Net

PRA
 
on Jul 26, 2022 02:01 AM
446 Views

Hi! How I can use having in Linq for below script? Can convert it's someone?

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Jul 27, 2022 01:26 AM

Hi PRA

Please refer below sample.

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="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Country" HeaderText="Country" />
        <asp:BoundField DataField="Total" HeaderText="Total" />
    </Columns>
</asp:GridView>

Namespace

C#

using AjaxSamplesModel;

VB.Net

Imports AjaxSamplesModel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    using (AjaxSamplesEntities entities = new AjaxSamplesEntities())
    {
        var result = (from customer in entities.Customers
                        group customer by customer.Country into g
                        where g.Count() > 5
                        select new
                        {
                            Country = g.Key,
                            Total = g.Count()
                        }).ToList();

        gvCustomers.DataSource = result;
        gvCustomers.DataBind();
    }
}

VB.Net

Public Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Using entities As AjaxSamplesEntities = New AjaxSamplesEntities()
        Dim result = (From customer In entities.Customers
                        Group customer By customer.Country Into cGroup = Group, Count()
                        Where cGroup.Count() > 5
                        Select New With
                        {
                            .Country = Country,
                            .Total = cGroup.Count()
                        }).ToList()
        gvCustomers.DataSource = result
        gvCustomers.DataBind()

    End Using
End Sub