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.

SELECT [QuestionId]
    ,CASE WHEN Answer LIKE 'Other%' THEN 'Other'
  		ELSE Answer END AS [Answer]
    ,count(*) as Count
FROM [SurveyResults-Q4-2022]
GROUP BY	QuestionId, 
  	CASE WHEN Answer LIKE 'Other%' THEN 'Other'
  	     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.

var q = from r in _context.SurveyResultsQ42022s
      group r by new { r.QuestionId, Answer = (r.Answer.StartsWith("Other") ? "Other" : r.Answer) }
      into g
      select new { g.Key.QuestionId, g.Key.Answer, Count = g.Count() };