代码之家 › 专栏 › 技术社区 › Martin

是否转换包含大小写值的存储过程?

case stored-procedures linq c#

Martin · 技术社区 · 15 年前

我有一个基于CASE值创建字段的存储过程。我在林肯怎么做?有什么想法吗?

基本上这是旧的存储过程(为了方便而被截断)

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选择了这样一个值

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

我希望得到任何反馈或帮助。

3 回复 | 直到 15 年前

Raymund 15 年前

试试这个

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
}

Quintin Robinson 15 年前

如果我正确地理解了这个问题,那么下面这样的东西应该能满足你的需要。

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

RPM1984 15 年前

像这样的?

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

您可能需要调整sum字段(我不知道它存储在哪个表中),并将条件运算符转换为可能的扩展方法以实现清洁,但这应该会让您走上正确的道路。