CODEWITHSUNDEEP
c#linqlambda
Ray Suelzer
Ray Suelzer

Reputation: 4107

Creating count based upon counts of Group By Statement in LINQ / lambda

What I want to do is group a huge bunch of records together by Employer. Then, I want to return an integer variable which will have a count of only those groups with at least 30 records.

I.E. I have 100 subscribers at Employer A, 20 at Employer B, and 30 at Employer C.

I group the records together and come up with

Employer A - 100
Employer B - 20
Employer C - 30

I want to return the Scalar Variable of 2.

Here is what I currently have:

var Step1 =
    (from y in recordsActivstJoin
     where y.q.Market.Contains(market) && y.x.ActivistCodeID.Equals(activismCode)
     select new {y}).ToList();

            //this groups my previous query
var Step2 = (from z in Step1 group z by z.y.q.Employer into f select new {f}).ToList();

When I watch the locals I can see that it does in fact group down from Step 1 where there are 34 rows to 17 in step 2. Now, I want narrow to only those where the group is >=30 .

Any suggestions?

Upvotes: 2

Views: 893

Answers (2)

user1793607
user1793607

Reputation: 531

As an alternative:

  • Query to group records by employer:

Code:

var groupedRecords = recordsActivstJoin
                     .Where(y => y.q.Market.Contains(market) && y.x.ActivistCodeID.Equals(activismCode))
                     .ToLookup(y => y.q.Employer);
  • Count of the groups with more than 30 entries:

Code:

Int32 count = groupedRecords.Count(g => g.Count() >= 30);

Notes:

ToLookup is used at is most likely avalanche-safe compared to GroupBy which is typically not. It depends on the provider used to query your data e.g. There is no difference on LinqToObject whilst for LinqToSql there is a massive difference on big varied data sets.

ToLookup is immediate execution though, so if you want to deffer execution for the grouping you will need to go down a different path.

Upvotes: 1

JoshVarty
JoshVarty

Reputation: 9426

I'm not the best at writing LINQ blindly, but I'm fairly certain you are looking for something very close to the following:

var Step1 =
                (from y in recordsActivstJoin
                 where y.q.Market.Contains(market) && y.x.ActivistCodeID.Equals(activismCode)
                 select new {y}).ToList();

                        //this groups my previous query
var Step2 = (from i in Step1 group i by i.y.q.Employer into groupedEmployees
select new 
{
    EmployeeCount = groupedEmployees.Count()
}).Where(n=>n.EmployeeCount >= 30).Count();

Patrick pointed out that this could be shortened to: 

var Step2 = Step1.GroupBy(i => i.y.q.Employer).Count(g => g.Count() >= 30);

Step2 should be 2 in your example. Hope this helps!

Upvotes: 5

Related Questions