How to Create T-SQL CASE Statements With LINQ To SQL

I was recently asked to help with a LINQ To SQL query where the resulting T-SQL query was to have CASE statements. Having CASE statements in T-SQL queries is a common scenario but how do we it in LINQ To SQL? The solution is simple and straight-forward. As you will see below, using C#'s "Immediate If" will convert into T-SQL CASE statements.

I have created a table called CityWeather. This table has  two fields: Name and Temperature. Here is the script if you wish to create the table on your machine.

CREATE TABLE [dbo].[CityWeather](
[Name] [nvarchar](100) NOT NULL,
[Temperature] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

 

My objective is to get LINQ To SQL to produce a T-SQL statement similar to this.

SELECT
Name,
Temperature,
CASE Temperature
WHEN 30 THEN 'Toasted'
WHEN 25 THEN 'I like it'
WHEN 10 THEN 'Just perfect'
WHEN -15 THEN 'Gonna freeze my'
END AS 'Message'
FROM CityWeather

Below is the C# code I wrote for my LINQ To SQL query.

from c in CityWeathers
select new
        {
          c.Name,
          c.Temperature,
          Messaage = c.Temperature == 30 ? "Toasted" : 
          c.Temperature == 25 ? "I like it" :
          c.Temperature == 10 ? "Just perfect" :
          c.Temperature == -15 ? "Gonna freeze my" : ""
        }

My LINQ To SQL query produced the following T-SQL Query

SELECT [t0].[Name], [t0].[Temperature],
(CASE
WHEN [t0].[Temperature] = @p0 THEN CONVERT(NVarChar(15),@p1)
WHEN [t0].[Temperature] = @p2 THEN CONVERT(NVarChar(15),@p3)
WHEN [t0].[Temperature] = @p4 THEN CONVERT(NVarChar(15),@p5)
WHEN [t0].[Temperature] = @p6 THEN @p7
ELSE CONVERT(NVarChar(15),@p8)
END) AS [Messaage]
FROM [CityWeather] AS [t0]
-- @p0: Input Decimal (Size = 0; Prec = 33; Scale = 4) [30]
-- @p1: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Toasted]
-- @p2: Input Decimal (Size = 0; Prec = 33; Scale = 4) [25]
-- @p3: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [I like it]
-- @p4: Input Decimal (Size = 0; Prec = 33; Scale = 4) [10]
-- @p5: Input NVarChar (Size = 12; Prec = 0; Scale = 0) [Just perfect]
-- @p6: Input Decimal (Size = 0; Prec = 33; Scale = 4) [-15]
-- @p7: Input NVarChar (Size = 15; Prec = 0; Scale = 0) [Gonna freeze my]
-- @p8: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729