Create T-SQL CASE Statements With LINQ To SQL
I was recently helping Nosh with a LINQ To SQL query where he wanted the resulting T-SQL query to have CASE statements. Having CASE statements in T-SQL queries is a common scenario but how do we it in LINQ To SQL .After some investigation I found the solution which I am presenting here using an example.
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
Using my trusted LINQPad here is how I wrote 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.1
And here is the output.

One Response to Create T-SQL CASE Statements With LINQ To SQL
Leave a Reply Cancel reply
Top Posts
- LINQ To SQL Tutorial
- LINQ To SQL Join On Multiple Conditions
- Code Sample: Programmatically Download File Using C#
- Windows 7 Control Panel In Classic Mode
- More Details Emerge On Microsoft Master Certification
- Use SqlConnection With LINQ To SQL
- Free Icons And Images With Visual Studio 2008
- Capture XML In WCF Service
- Dynamic Sort With LINQ
- StyleCop Tutorial
Tags
.Net 2010 ADO.NET ASP.NET Azure Blogging Books Browsers C# Certification Cloud Computing Code Snippets Community Data Services Eclipse Entity Framework Google IDE Java LINQ Mac Microsoft Museum NetBeans Office Oracle REST SharePoint Silverlight SQL Server T-SQL Tips Tools Training Visual Studio Visual Studio 2010 WCF Web Windows Windows 7 Windows Forms Windows Live WMI WPF XAML


Very cool, thanks for the tip.