0

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

Drewdin
  • 1,732
  • 5
  • 23
  • 35
  • possible duplicate of [Query with case statement from SQL Server to Linq query c#](http://stackoverflow.com/questions/28878830/query-with-case-statement-from-sql-server-to-linq-query-c-sharp) – MethodMan Apr 07 '15 at 19:15
  • @Drewdin, i don't know who downvoted your question, but i upvoted it ;) Cheers, Maciej – Maciej Los Apr 08 '15 at 17:50

1 Answers1

2

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;
Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • My Mistake, I wanted both editable and uneditable recipes, with just a field that was a flag if there were or not. I am going to try out your query, thanks. – Drewdin Apr 07 '15 at 19:24
  • I appreciate the help, I am getting errors left and right so I am not sure if its working until I sort them out. Ill keep you posted – Drewdin Apr 07 '15 at 20:27
  • What kind of errors? Which line? Try to remove `order r by r.Recipe_Name` and check the results. – Maciej Los Apr 07 '15 at 21:04
  • I appreciate the help, I ended up using a database view of the original query and using LINQ as: from r in db.v_recipes. I accepted your answer since you did help me get 90% of the way – Drewdin Apr 08 '15 at 15:49