Ehsan Akbar Ehsan Akbar - 3 months ago 7
C# Question

select after groupby returns same records

I have this query :

_ctx.TestPackageReportDetails.GroupBy(i => new { i.Status, i.TestPackageId })
.Select(m => new { pid = m.Key.TestPackageId, count = m.Count(i => i.Status == "Accept") })


This query returns 185 items with the count of each one :

enter image description here

But i need the number of items with
count=5
so i have this query :

_ctx.TestPackageReportDetails.GroupBy(i => new { i.Status, i.TestPackageId })
.Select(m => new { pid = m.Key.TestPackageId, count = m.Count(i => i.Status == "Accept") }).Select(i => i.count == 5).Count();


But this returns 186 why ?
enter image description here

does have my code any problems?

Answer

You need to filter the grouping results using Where:

    int count = 
            _ctx.TestPackageReportDetails.GroupBy(i => new { i.Status, i.TestPackageId })
                              .Where(m => m.Count(i => i.Status == "Accept") == 5).Count();

When you need to filter, you use .Where and not .Select. Select loops through all elements and return a new form that you specify (Maybe selecting one property out of a complex object, or converting an int into a string for some reason).

But where you need to filter, you use .Where and you pass the condition as a parameter.