转换包含 CASE 值的存储过程？

Question

我有一个基于 CASE 值创建字段的存储过程。我如何在 LINQ 中执行相同的操作？有什么想法吗？

基本上这是旧的存储过程（为了方便起见被截断）

SELECT  M.Period AS 'Period' ,
        C.Code AS 'Group' ,
        C.ClientCode AS 'Code' ,
        C.ClientName AS 'Name' ,
CASE WHEN ( SELECT  SUM(Amount) AS Expr1
                        FROM    M
                        WHERE   ( ClientCode = C.ClientCode )
                        GROUP BY ClientCode
                      ) = 0 THEN 'Balanced'
                 WHEN ( SELECT  SUM(Amount) AS Expr1
                        FROM    M
                        WHERE   ( ClientCode = C.ClientCode )
                        GROUP BY ClientCode
                      ) > 0 THEN 'Pending'
            END AS 'Status' ,

正如您从上面看到的，案例选择了一个像这样的值

 CASE WHEN ( SELECT  SUM(Amount) AS Expr1
                        FROM    M
                        WHERE   ( ClientCode = C.ClientCode )
                        GROUP BY ClientCode
                      ) = 0 THEN 'Balanced'
                 WHEN ( SELECT  SUM(Amount) AS Expr1
                        FROM    M
                        WHERE   ( ClientCode = C.ClientCode )
                        GROUP BY ClientCode
                      ) > 0 THEN 'Pending'
            END AS 'Status' ,

所以我已经完成了所有的连接，到目前为止我已经做到了并且它有效。

 var test = from c in C join h in H on c.Code 
        equals h.Code join m in M on c.ClientCode   
        equals m.ClientCode 
        select new 
        { 
            Period=m.Period,
            Group=c.Code,
            Code= c.ClientCode,
            Name= c.ClientName,
            <-- Here is where I need the to display the correct case value-->
        };

我将不胜感激任何反馈或帮助。

Answer 1

试试这个

var test = from c in db.C
select new {
  Period = c.M.Period,
  Group = c.Code,
  Code = c.ClientCode,
  Name = c.ClientName,
  Status = 
      ((from m0 in db.M
    where
      m0.ClientCode == c.ClientCode
    group m0 by new {
      m0.ClientCode
    } into g
    select new {
      SumOfAmount = (System.Int32)g.Sum(p => p.Amount)
    }).First().SumOfAmount) == 0 ? "Balanced" : 
      ((from m0 in db.M
    where
      m0.ClientCode == c.ClientCode
    group m0 by new {
      m0.ClientCode
    } into g
    select new {
      SumOfAmount = (System.Int32)g.Sum(p => p.Amount)
    }).First().SumOfAmount) > 0 ? "Pending" : null
}

Answer 2

如果我正确理解了这个问题，那么下面的内容应该可以满足您的需求。

var test = from c in C join h in H on c.Code 
    equals h.Code join m in M on c.ClientCode   
    equals m.ClientCode 
    select new 
    { 
        Period=m.Period,
        Group=c.Code,
        Code= c.ClientCode,
        Name= c.ClientName,
        Status = M.Where(x => x.ClientCode == c.ClientCode).Sum(x => x.Amount) > 0 ? "Pending" : "Balanced"
    };

Answer 3

像这样的东西吗？

var test = from c in C join h in H on c.Code 
        equals h.Code join m in M on c.ClientCode   
        equals m.ClientCode 
        select new 
        { 
            Period=m.Period,
            Group=c.Code,
            Code= c.ClientCode,
            Name= c.ClientName,
            CaseValue = c.Where(x => x.ClientCode == c.ClientCode)
                           .Sum(x => x.Amount) == 0 
                               ? "Balanced" : "Pending"
        };

您可能需要调整总和字段（我不知道它存储在哪个表中），并将条件运算符转换为可能的清洁扩展方法，但它应该让您走上正确的轨道。