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:
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:
WHEN 30 THEN 'Toasted'
WHEN 25 THEN 'I like it'
WHEN 10 THEN 'Just perfect'
WHEN -15 THEN 'Gonna freeze my'
END AS 'Message'
Below is the C# code I wrote for my LINQ To SQL query:
from c in CityWeathers
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],
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
END) AS [Messaage]
FROM [CityWeather] AS [t0]