title: How do you convert a CASE WHEN & SUM in SQL to Linq To SQL
body: <p>Here is the query:</p>
  
  <pre><code>SELECT 
      Group1 = SUM(CASE WHEN Age BETWEEN 10 AND 20 THEN 1 ELSE 0 END),
      Group2 = SUM(CASE WHEN Age BETWEEN 21 AND 30 THEN 1 ELSE 0 END),
      Group3 = SUM(CASE WHEN Age BETWEEN 31 AND 40 THEN 1 ELSE 0 END),
      Group4 = SUM(CASE WHEN Age BETWEEN 41 AND 50 THEN 1 ELSE 0 END),
      Group5 = SUM(CASE WHEN Age BETWEEN 51 AND 60 THEN 1 ELSE 0 END),
      Group6 = SUM(CASE WHEN Age BETWEEN 61 AND 70 THEN 1 ELSE 0 END),
      Group7 = SUM(CASE WHEN Age BETWEEN 71 AND 80 THEN 1 ELSE 0 END),
      Group8 = SUM(CASE WHEN Age BETWEEN 81 AND 90 THEN 1 ELSE 0 END),
      Group9 = SUM(CASE WHEN Age BETWEEN 91 AND 100 THEN 1 ELSE 0 END)
  FROM
      (SELECT Age = DATEDIFF(yy, BirthDate, GETDATE())
      FROM Person
      WHERE IsActive = 1) AgeGroup
  </code></pre>
  
  <p>What is the equivalent Linq To SQL statement?</p>
  
  <p><strong>Edit 1:</strong></p>
  
  <p>The nearest I can come up with is:</p>
  
  <pre><code>var ageGroup = Persons.Select(item =&gt; DateTime.Today.Year - item.BirthDate.Value.Year)
                      .Select(age =&gt; new {
                          Group1 = age &gt;= 10 &amp;&amp; age &lt;= 19 ? 1 : 0,
                          Group2 = age &gt;= 20 &amp;&amp; age &lt;= 29 ? 1 : 0,
                          Group3 = age &gt;= 30 &amp;&amp; age &lt;= 39 ? 1 : 0,
                          Group4 = age &gt;= 40 &amp;&amp; age &lt;= 49 ? 1 : 0,
                          Group5 = age &gt;= 50 &amp;&amp; age &lt;= 59 ? 1 : 0,
                          Group6 = age &gt;= 60 &amp;&amp; age &lt;= 69 ? 1 : 0,
                          Group7 = age &gt;= 70 &amp;&amp; age &lt;= 79 ? 1 : 0,
                          Group8 = age &gt;= 80 &amp;&amp; age &lt;= 89 ? 1 : 0,
                          Group9 = age &gt;= 90 &amp;&amp; age &lt;= 99 ? 1 : 0
                      });
  
  var summary = new {
                  Group1 = ageGroup.Sum(item =&gt; item.Group1),
                  Group2 = ageGroup.Sum(item =&gt; item.Group2),
                  Group3 = ageGroup.Sum(item =&gt; item.Group3),
                  Group4 = ageGroup.Sum(item =&gt; item.Group4),
                  Group5 = ageGroup.Sum(item =&gt; item.Group5),
                  Group6 = ageGroup.Sum(item =&gt; item.Group6),
                  Group7 = ageGroup.Sum(item =&gt; item.Group7),
                  Group8 = ageGroup.Sum(item =&gt; item.Group8),
                  Group9 = ageGroup.Sum(item =&gt; item.Group9)
  };
  </code></pre>
  
  <p>But this creates 9 SQL statements.</p>
  
  <p><strong>Edit 2:</strong></p>
  
  <p>Another way could be:</p>
  
  <pre><code>Persons.Select(item =&gt; DateTime.Today.Year - item.BirthDate.Value.Year)
      .Select(age =&gt; new {
          Group1 = age &gt;= 10 &amp;&amp; age &lt;= 19 ? 1 : 0,
          Group2 = age &gt;= 20 &amp;&amp; age &lt;= 29 ? 1 : 0,
          Group3 = age &gt;= 30 &amp;&amp; age &lt;= 39 ? 1 : 0,
          Group4 = age &gt;= 40 &amp;&amp; age &lt;= 49 ? 1 : 0,
          Group5 = age &gt;= 50 &amp;&amp; age &lt;= 59 ? 1 : 0,
          Group6 = age &gt;= 60 &amp;&amp; age &lt;= 69 ? 1 : 0,
          Group7 = age &gt;= 70 &amp;&amp; age &lt;= 79 ? 1 : 0,
          Group8 = age &gt;= 80 &amp;&amp; age &lt;= 89 ? 1 : 0,
          Group9 = age &gt;= 90 &amp;&amp; age &lt;= 99 ? 1 : 0
      })
      .ToList()
      .Aggregate((previous, next) =&gt; new {
          Group1 = previous.Group1 + next.Group1,
          Group2 = previous.Group2 + next.Group2,
          Group3 = previous.Group3 + next.Group3,
          Group4 = previous.Group4 + next.Group4,
          Group5 = previous.Group5 + next.Group5,
          Group6 = previous.Group6 + next.Group6,
          Group7 = previous.Group7 + next.Group7,
          Group8 = previous.Group8 + next.Group8,
          Group9 = previous.Group9 + next.Group9
      });
  </code></pre>
  
  <p>But this retrieves the entire list to memory then computing the SUM of each column</p>