LINQ To SQL - CASE Statements

Switch functionality can be accomplished by using "Ternary 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>

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.

Types of LINQ syntax

There are 2 types of LINQ Syntax:

1. 1.Fluent syntax
2. 2.Query syntax

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };
IEnumerable<string> query =
names.Where (name => name.EndsWith ("y"));
query.Dump ("In fluent syntax");

query =
from n in names
where n.EndsWith ("y")
select n;
query.Dump ("In query syntax");

Combining interpreted and local queries.

void Main()
{
// This uses a custom 'Pair' extension method, defined below.

IEnumerable<string> q = Customers
.Select (c => c.Name.ToUpper())
.Pair() // Local from this point on.
.OrderBy (n => n);

q.Dump();
}

public static class MyExtensions
{
public static IEnumerable<string> Pair (this IEnumerable<string> source)
{
string firstHalf = null;
foreach (string element in source)
if (firstHalf == null)
firstHalf = element;
else
{
yield return firstHalf + ", " + element;
firstHalf = null;
}
}
}