sql到具有一个CASE和一个子查询的LINQ [英] sql to LINQ that has a CASE and a sub query

问题描述

我一直在努力将下面的查询转换为linq查询，非常接近，但是我不确定如何在LINQ中添加case语句.谷歌搜索使我离这很近.

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.

原始工作查询:

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;

我的LINQ-缺少案件陈述:

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;

我也尝试过:

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

如何将案例陈述合并到LINQ中?向正确方向的任何推动将不胜感激

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

推荐答案

考虑一下！

可编辑配方的状态不等于2，因此下面的查询仅返回满足您需求的可编辑配方；)您不需要任何子查询;)

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"};

对应的SQL应该看起来像:

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;

这篇关于sql到具有一个CASE和一个子查询的LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！