CODEWITHSUNDEEP
c#sqllinqgroup-bysql-order-by
Felix Weir
Felix Weir

Reputation: 469

GroupBy and OrderBy in LINQ

I have this SQL query that does what I want it to, but I am confused by how GroupBy() and OrderBy() work exactly...

Here is the SQL statement I have that works fine:

SELECT TOP 5 UserID, COUNT(*) 
    FROM SkillLevelMap
    WHERE SkillID = 183 OR SkillID = 102 OR SKILLID = 103 
    GROUP BY UserID
    ORDER BY COUNT(*) DESC

What the end product ideally is, is a list of UserIDs that satisfy these conditions, ordered by the ocurrence of each SkillID, where the user that has the most SkillIDs matched is at the top, going down to people that have less.

What I have attempted to no avail:

var userList2 = SQEPData.AllSkillLevelMaps.Where(x => skillIDs.Contains(x.SkillID)).GroupBy(g => g.User);

Upvotes: 2

Views: 5798

Answers (1)

Reed Copsey
Reed Copsey

Reputation: 564413

Your query appears to be something similar to:

var userList2 = SQEPData.AllSkillLevelMaps
                   .Where(x => skillIDs.Contains(x.SkillID))
                   .GroupBy(x => x.User)
                   .Select(g => new { User = g.Key, Count = g.Count() })
                   .OrderByDescending(i => i.Count)
                   .Take(5);

Upvotes: 2

Related Questions