LINQ To SQL - CASE Statements

demo

                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

http://lancefisher.net/blog/archive/2008/05/07/linq-to-sql---case-statements.aspx

                       

demo

                                [url=]Martillo[/url]                                                       
                                                        Very nice! Thanks for this great article.

I wonder what the LINQ code would be to geneate a SQL CASE statement for an update operation, e.g. something like this:

UPDATE Plants
SET Name =
WHEN Edible = 1 THEN Name + ' (Edible)'
WHEN Edible = 0 THEN Name + ' (Inedible)'
ELSE Name + ' (Edibility unknown)'
END

Obviously there's no logical reason to make that particular update, but I've often found it useful to use CASE statements with update operations. I fooled around with some LINQ code for a few minutes but couldn't get it...                       
                                                                                                                                        5/20/2008 1:40 PM                                                                                                                Lance Fisher                                                       
                                                        @Martillo

You're looking at doing mass update with LINQ.  I'm not sure that LINQ works so well for those, but I'll have to check.  I think that LINQ will generate a separate UPDATE command for each record that has been updated.  I'll have to play with it some and find out.                       
               
                                                                                                                        6/10/2008 5:10 PM                                                                                                                Jonathan                                                       
                                                        That use of the Linq to Sql to generate CASE statements is powerful.  Have you seen anything at all regarding making Linq to Sql generate Common Table Expressions?                       
               
                                                                                                                        8/6/2008 1:16 PM                                                                                                                [url=]Peter[/url]                                                       
                                                        Nice article but I am looking for the LINQ equivelant of this...

Variable @ID

IF @ID = 1
Select Name From X
ELSE IF @ID = 2
Select Home From Y
ELSE
Select 'Nothing'
                       
               
                                                                                                                        8/7/2008 11:16 PM                                                                                                                Lance Fisher                                                       
                                                        Hi Peter,

In that case, I would make a method that uses a coupld of LINQ statements.  Maybe something like this would work for you:

        public string GetName(int id)
        {
            var db = new DataClasses1DataContext();

            switch (id)
            {
                case 1:
                    return (from n in db.x select n.Name).SingleOrDefault();
                case 2:
                    return (from h in db.y select h.Home).SingleOrDefault();
                default:
                    return null;
            }

        }

                       
               
                                                                                                                        9/11/2008 1:59 PM                                                                                                                Scott                                                       
                                                        Your articles are great, but 2 things wrong.  Your contact page and ABOUT page don't work.  Help a guy out and finish the rest of your blog so I can see who writes these things.                       
               

juseliao

此贴要火！！留下广告位  要出租的联系我！



怎么丰胸,如何丰胸最有效,断奶后如何丰胸,怎样可以丰胸