title: Convert a stored procedure than contains CASE values?
body: <p>I have a stored procedure that creates a field bases on a CASE value. How do I do the same in LINQ? Any ideas?</p>
  
  <p>Basically this is the old stored procedure (truncated for ease)</p>
  
  <pre><code>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
                        ) &gt; 0 THEN 'Pending'
              END AS 'Status' ,
  </code></pre>
  
  <p>As you can see from above the case picks a value like so </p>
  
  <pre><code> 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
                        ) &gt; 0 THEN 'Pending'
              END AS 'Status' ,
  </code></pre>
  
  <p>So I have done all my joins and I have this so far and it works.</p>
  
  <pre><code> 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,
              &lt;-- Here is where I need the to display the correct case value--&gt;
          };
  </code></pre>
  
  <p>I would appreciate any feedback or help.</p>