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]