Convert a stored procedure than contains CASE values?

I have a stored procedure that creates a field bases on a CASE value. How do I do the same in LINQ? Any ideas?

Basically this is the old stored procedure (truncated for ease)

SELECT  M.Period AS 'Period' ,
        C.Code AS 'Group' ,
        C.ClientCode AS 'Code' ,
        C.ClientName AS 'Name' ,
C开发者_如何学编程ASE 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' ,

As you can see from above the case picks a value like so

 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' ,

So I have done all my joins and I have this so far and it works.

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

I would appreciate any feedback or help.

Try this one out

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
}

If I understand the question correctly then something like below should work for your needs.

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

Something like this?

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

You might need to adjust the sum field (i don't know which table it's stored), and convert the conditional operator to maybe an extension method for cleanliness, but it should get you on the right track.