CODEWITHSUNDEEP
c#entity-frameworkcountgroup-by
Ehsan Akbar
Ehsan Akbar

Reputation: 7299

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?

Upvotes: 0

Views: 35

Answers (2)

Zein Makki
Zein Makki

Reputation: 30042

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.

Upvotes: 1

Mostafiz
Mostafiz

Reputation: 7352

Set count=5 in where clause

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

Upvotes: 1

Related Questions