How to join two tables and group by ID and count in LINQ C# method syntax

September 3, 2022

I have two tables:

PractitionerSkill { Id, Title }
PractitionerInSkills { Id, PractitionerSkillId ), where PractitionerSkillId is FK into PractitionerSkill

(there are more columns but that is not really important)

And I’m trying to count number of skills pr practitioner.

Using LINQ method syntax, I am trying to do this:

SELECT 
    S.Id, S.Title, COUNT(*) as [Count] from 
    PractitionerSkills S INNER JOIN 
    PractitionerInskills PIS ON S.ID = PIS.PractitionerSkillId
GROUP BY 
    S.Id, S.Title
ORDER BY
    S.Title

Easy in SQL. Notice that I’m getting the ID, title and count in the result.

My current efforts (which is not even method syntax)

var query = from skill in _context.PractitionerSkills
        join pis in _context.PractitionerInSkills on skill.Id equals pis.PractitionerSkillId into grp
        select new
        {
            Title = skill.Title,
            Count = grp.Count()
        };

which is almost there, but I can’t get more columns out. I need the Skill.Id (or PractitionerInSkills.PractitionerSkillId)

>Solution :

It’s easy in Linq too!

var query = _context.PractitionerSkills.Join(_context.PractitionerInSkills,
                                             ps => new { k1 = ps.Id },
                                             pis => new { k1 = pis.PractitionerSkillId },
                                             (ps, pis) => new { ps.Id, ps.Title })
                                       .GroupBy(r => new { r.Id, r.Title })
                                       .Select(g => new { g.Key.Id, g.Key.Title, Count = g.Count() })
                                       .OrderBy(r => r.Title);