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

venkatg
 
on Dec 31, 2020 11:33 PM
Sample_152022.zip
3460 Views

Hi All,

How to use group by having count in Linq C#

I want the below query in Linq c#.

Can u update?

CREATE TABLE #Rarity(Rarity INT,IsDeleted INT,Id INT)
INSERT INTO #Rarity VALUES(1392,0,16839)
INSERT INTO #Rarity VALUES(1392,1,16840)
INSERT INTO #Rarity VALUES(1392,0,16841)
INSERT INTO #Rarity VALUES(1393,1,16842)
INSERT INTO #Rarity VALUES(1393,1,16843)
INSERT INTO #Rarity VALUES(1393,1,16844)
 
SELECT *
FROM #Rarity
WHERE Rarity =
(
    SELECT Rarity
    FROM #Rarity
    GROUP BY Rarity
    HAVING COUNT(CASE WHEN IsDeleted = 0 THEN 1 END) = 0
)

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 01, 2021 11:20 PM

Hi venkatg,

Refer below sample.

Namespaces

C#

using System.Data;
using System.Linq;

VB.Net

Imports System.Data
Imports System.Linq

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Rarity");
    dt.Columns.Add("IsDeleted");
    dt.Columns.Add("Id");
    dt.Rows.Add(1392, 0, 16839);
    dt.Rows.Add(1392, 1, 16840);
    dt.Rows.Add(1392, 0, 16841);
    dt.Rows.Add(1393, 1, 16842);
    dt.Rows.Add(1393, 1, 16843);
    dt.Rows.Add(1393, 1, 16844);

    var zeroRarity = dt.AsEnumerable()
        .Where(x => x["IsDeleted"].ToString() == "0")
        .Select(x => x["Rarity"]).Distinct();
    var result = dt.AsEnumerable()
        .Where(x => !zeroRarity.Contains(x["Rarity"])).ToList();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dt As DataTable = New DataTable()
    dt.Columns.Add("Rarity")
    dt.Columns.Add("IsDeleted")
    dt.Columns.Add("Id")
    dt.Rows.Add(1392, 0, 16839)
    dt.Rows.Add(1392, 1, 16840)
    dt.Rows.Add(1392, 0, 16841)
    dt.Rows.Add(1393, 1, 16842)
    dt.Rows.Add(1393, 1, 16843)
    dt.Rows.Add(1393, 1, 16844)
    Dim zeroRarity = dt.AsEnumerable() _
                    .Where(Function(x) x("IsDeleted").ToString() = "0") _
                    .Select(Function(x) x("Rarity")).Distinct()
    Dim result = dt.AsEnumerable() _
                .Where(Function(x) Not zeroRarity.Contains(x("Rarity"))).ToList()
End Sub