In today's post we'll see how to make LINQ to SQL generate SQL CASE statements for us.
Even though some people describe the SQL CASE statement as obscure or an edge case, I tend to use it quite a bit. It's true that if you are dealing with a nicely designed database you can probably avoid it, but the sad fact is that I'm often not dealing with a nicely designed database. Maybe you have this headache too. Third-party vendors have a habit of making their databases ugly.
One use for the CASE statement is displaying a description instead of a code when there isn't a lookup table. Suppose there is a yes/no field that stores values as 1/0 in the database. You can use a CASE statement to return yes or no instead of 1 or 0 like this:
That's the easy example. Often I'll find myself writing longer CASE statements to get around more difficult problems like this:
It can get much worse, but let's not get into that.
I've been converting some of my data access over to LINQ in an effort to get rid of some stored procedures. I want to simplify deployment, and I'd rather not put stored procedures in a third-party database. A nice side effect of this work is that the LINQ turns out to be much more readable than the piles of T-SQL.
One problem that I've seen people run into is how to write these CASE statements in LINQ. There is no direct equivalent like "select", "from", and "orderby". However, it is possible using C# language features. In particular the ternary operator. You may know it as the "?:" operator.
The ternary operator takes the "if" statement and turns it into an expression. Here's an example:
The syntax is <condition> ? <true value> : <false value>
For this example we'll make a table in a SQL Server database and add some rows to it. This will be a Plants table that contains rows of various plants and whether or not they are edible. Create the table and data using the following commands:
Before we start writing our LINQ code, go get the LINQ to SQL Debug Visualizer. This will allow you to easily see the SQL that is being generated from your LINQ statement. Scott Guthrie has the download on his blog as well as instructions on how to install and use it.
Create a new project, and add a "LINQ to SQL Classes" dbml file. We'll call it Plants.dbml. From the Server Explorer, navigate to the table you created and drag it over to the dbml designer canvas to create the LINQ to SQL code for that table. (For more on using LINQ to SQL be sure to read through Scott's series)
Now that we have the LINQ classes created, let's write some code to pull data from the table using LINQ. Add a new class to your project. We'll call it PlantsTest.cs. I made this into an MbUnit TestFixture. That allows us to easily run methods from it using TestDriven.net. For this demo, that's all we care to do. There will be no UI, just some code to debug.
After you've created the class and marked it as a test fixture, add a method where we can write the LINQ code. To start off, we'll just get all the data from the Plants table as is. Here is the new class with the initial LINQ query. I'm running it with the debugger using TestDriven.net. You can see the SQL that is generated is pretty straight-forward.
Now, let's add a basic case statement. This will return the text "This is poisonous!" for plants with a 0 in the edible field, and "Okay to eat" otherwise. By looking at the generated SQL using the debug visualizer, we can see that a CASE statement is in fact being generated.
Now the question will come up, "what if I want to have more than just one WHEN and an ELSE". In other words, how do I add more cases. Here's the trick:
By replacing the "if false" value of the ternary expression with another ternary expression we logically create the same effect as a SQL CASE statement. Unlike the switch statement, this is an expression, and can be used on the right had side of the assignment operator. LINQ to SQL is smart about handling this too. It creates additional WHEN clauses inside of one CASE statement. Just like you would if you were writing the SQL yourself. The C# syntax forces you to have an ELSE clause. That's a good thing.
Some people might opt for writing in extra parenthesis to clearly show each ternary expression. I think writing the statements without the parenthesis more clearly shows our intent, that is, making a CASE statement.
Download the source code