![Combining GROUP BY and CASE in a Linq Query](/_next/image?url=https%3A%2F%2Fimages.prismic.io%2Fhimynameistim%2Ff1d16af8-7ce6-4ea6-8ee2-30ee19618d22_Wall.jpg%3Fauto%3Dcompress%2Cformat%26fit%3Dcrop%26max-w%3D1093%26max-h%3D400&w=3840&q=75)
Combining GROUP BY and CASE in a Linq Query
With SQL Server you have the ability to make changes to the raw table field data within a GROUP BY clause by using a CASE statement. e.g.
1SELECT [QuestionId]2 ,CASE WHEN Answer LIKE 'Other%' THEN 'Other'3 ELSE Answer END AS [Answer]4 ,count(*) as Count5 FROM [SurveyResults-Q4-2022]6 GROUP BY QuestionId,7 CASE WHEN Answer LIKE 'Other%' THEN 'Other'8 ELSE Answer END
When using EF Core though rather than writing SQL directly we write Linq queries which get translated to the appropriate SQL query.
To achieve a CASE statement in a GROUP BY you can write the following.
1var q = from r in _context.SurveyResultsQ42022s2 group r by new { r.QuestionId, Answer = (r.Answer.StartsWith("Other") ? "Other" : r.Answer) }3 into g4 select new { g.Key.QuestionId, g.Key.Answer, Count = g.Count() };