[c#]Linq 表达式︰ 使用案例最小最大从 SQL 中的 Linq 表达式

发布时间: 2017/2/26 23:45:25
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我试图从 SQL 查询创建 linq 表达式。

我的大问题是创建这行︰

CASE WHEN MIN() <> MAX() THEN 1 ELSE 0

在 Linq 表达式中。

SQL 查询工作正常︰

SELECT 
    CLD.Id, CLD.Comments,
    CASE 
       WHEN MIN(AnswerComment.IdStatus) <> MAX(AnswerComment.IdStatus) 
       THEN 1 ELSE 0
    END AS statusDifference
FROM 
    CLD 
INNER JOIN 
    AnswerComment on CLD.Id = AnswerComment.IdCLD
INNER JOIN 
    ListDef on CLD.IdListDef = ListDef.Id
WHERE 
    ListDef.IdInspection = 1042
GROUP BY 
    CLD.Id, CLD.Comments  

我试图将此 SQL 查询转换为 C# Linq 表达式。

var sql = (from CLD in db.CLD
                       join AnswerComment in db.AnswerComment
                           on CLD.Id equals AnswerComment.IdCLD
                       join ListDef in db.ListDef
                           on CLD.IdListDef equals ListDef.Id
                       where ListDef.IdInspection == idInspec
                       group CLD by new ComentRespostaLFDModels
                       {
                           IdComment = CLD.Id,
                           Comment = CLD.Comments
                       } into coments
                       select new ComentRespostaLFDModels
                       {
                           IdComment = coments.Key.IdComment,
                           Comment = coments.Key.Comment
                           * Here I need to do the case: "CASE WHEN MIN(AnswerComment.IdStatus) <> MAX(AnswerComment.IdStatus) THEN 1 ELSE 0" or in other place that I don't know
                       }).ToList();

解决方法 1:

您可以更改像 group 子句

var sql = (from CLD in db.CLD
           join AnswerComment in db.AnswerComment on CLD.Id equals AnswerComment.IdCLD
           join ListDef in db.ListDef on CLD.IdListDef equals ListDef.Id
           where ListDef.IdInspection == idInspec
           group new { CLD, AnswerComment } by new ComentRespostaLFDModels
           {
               IdComment = CLD.Id,
               Comment = CLD.Comments
           } into coments
           select new ComentRespostaLFDModels
           {
               IdComment = coments.Key.IdComment,
               Comment = coments.Key.Comment,
               StatusDifference = comments.Min(c=>c.AnswerComment.IdStatus) != comments.Max(c=>c.AnswerComment.IdStatus) ? 1 : 0
           }).ToList();
赞助商