LINQ is so much fun and makes things so much easier to accomplish. It's also gaining quite a bit of acceptance and it's evolving too with things like: LINQ to JavaScript and LINQ to JSON. I've been playing with LINQ quite a bit and recently I needed to generate a CASE statement with LINQ to SQL. It was fairly easy to accomplish since LINQ is so darn cool! Here is what I came up with:
[code:c#]
var gimmeh_gimmeh =
from t in db.sc_Timeslots
where (t.starttime.Minute % 5 == 0) || (t.starttime.Minute % 10 == 0)
select new
{
t.id,
t.starttime,
interval = (t.starttime.Minute % 5 == 0) ? "5 Minutes" : "10 Minutes"
};
[/code]
The line: "interval = ..." generates a CASE statement as shown below.
[code:tsql]
SELECT [t0].[id], [t0].[starttime],
(CASE
WHEN (DATEPART(Minute, [t0].[starttime]) % @p4) = @p5 THEN CONVERT(NVarChar(10),@p6)
ELSE @p7
END) AS [interval]
FROM [dbo].[sc_Timeslot] AS [t0]
WHERE ((DATEPART(Minute, [t0].[starttime]) % @p0) = @p1) OR ((DATEPART(Minute, [t0].[starttime]) % @p2) = @p3)
[/code]
You can have multiple CASE statements or even concat them like so:
[code:c#]
interval = ((t.starttime.Minute % 5 == 0) ? "5 Minutes" : "10 Minutes") + ((t.id == 1) ? " (Master ID)" : "")
[/code]
Which will generate the Case statement below (hey I'm starting to rhyme):
[code:tsql]
(CASE
WHEN (DATEPART(Minute, [t0].[starttime]) % @p4) = @p5 THEN CONVERT(NVarChar(22),@p6)
ELSE @p7 + (
(CASE
WHEN [t0].[id] = @p8 THEN @p9
ELSE CONVERT(NVarChar(12),@p10)
END))
END) AS [interval]
[/code]
You can also nest the CASE statements... but I think by now you're clever enough to figure that out on your own.
Enjoy!
