title: Relatively simple SQL query to Linq (with grouping)
body: <p>I have an sql statement that needs to be modified to any correct form of Linq (query syntax and/or lambda) -- any thoughts will be greatly appreciated:</p>
  
  <pre><code>SELECT DISTINCT
      CUSTOMER_NUM, CUSTOMER_NAME, 
      COUNT(*) AS 'COUNT',
      SUM(CASE WHEN State = 'State1' THEN 1 ELSE 0 END) AS 'State1Count',
      SUM(CASE WHEN State = 'State2' THEN 1 ELSE 0 END) AS 'State2Count',
      ISNULL(SUM(USD_TOTAL_MRC_AMT), 0) AS 'USD_TOTAL_MRC_AMT_SUM'        
  FROM Table1
  WHERE
   (
      USERID = @UserId   //@UserId is a parameter passed into the stored procedure
  )
  GROUP BY CUSTOMER_NUM, CUSTOMER_NAME
  ORDER BY
      CUSTOMER_NAME
  </code></pre>
  
  <p>Here's along the lines what I have working so far in LinqPad:</p>
  
  <pre><code>Table1.GroupBy(i =&gt; 1).Select(g =&gt; new { 
  Total = g.Count(i =&gt; i.State == "AM Re-Rate Pool" || i.State == "AM Renewal Pool"),
  ReRates = g.Count(i =&gt; i.State == "State1"),
  Renewals = g.Count(i =&gt; i.State == "State2
  });
  </code></pre>
  
  <p>Thanks!</p>