Combining GROUP BY and CASE in a Linq Query

19 January 2023

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 Count
5  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.SurveyResultsQ42022s
2        group r by new { r.QuestionId, Answer = (r.Answer.StartsWith("Other") ? "Other" : r.Answer) }
3        into g
4        select new { g.Key.QuestionId, g.Key.Answer, Count = g.Count() };