sql to LINQ that has a CASE and a sub query

问题:

I have been strugging to convert the query below to a linq query, im pretty close but I am not sure how to add in a case statement to LINQ. Googling around got me this close.

Original Working Query:

SELECT *, CASE
    WHEN Recipe_Name IN (SELECT Recipe_Name FROM Recipes WHERE Status = 2) THEN 0
    ELSE 1 
END AS editable
FROM Recipes WHERE Status = 1 ORDER BY Recipe_Name;

My LINQ - Missing the case statement:

var lcrecipes = from r in db.Recipes where r.Status == 2 select r.Recipe_Name;
            var RecipeResults = from rr in db.Recipes where lcrecipes.Contains(rr.Recipe_Name) select rr;

I have also tried:

var RecipeResults = from rr in db.Recipes
                                   where lcrecipes.Contains(rr.Recipe_Name)
                                   select new
                                   {
                                       editable = rr.Status == 2 ? "false" :
                                                   rr.Status == 1 ? "true" : ""
                                   };

How do I incorporate the case statement into LINQ? Any push in the right direction would be greatly appreciated

回答1:

Think of it!

Editable recipes have got status not equal to 2, so below query returns only editable recipes, which meeets your needs ;) You do not need any subquery ;)

var editablerecipes = from r in db.Recipes
    where r.Status != 2
    order r by r.Recipe_Name
    select r.Recipe_Name;

If you would like to add Editable field, use this:

var recipesWithEditableFlag = from r in db.Recipes
    order r by r.Recipe_Name
    select new {RecipeName= r.Recipe_Name, Editable = r.Status==2 ? "no" : "yes"};

Corresponding SQL should looks like:

SELECT Recipe_Name, CASE WHEN Status = 2 THEN 'no' ELSE 'yes' END AS editable
FROM Recipes
ORDER BY Recipe_Name;