title: LINQ query with distinct count
body: <p>I am trying to construct a LINQ query in C# that will give me a list of distinct values from a column in a dataset with a count for each row.  The results would look like this.</p>
  
  <pre><code>State   Count
  AL       55
  AK       40
  AZ       2
  </code></pre>
  
  <p>Here is the SQL that does that.</p>
  
  <pre><code>SELECT name, COUNT(*) AS count
    FROM architecture arch
    GROUP BY name
    ORDER BY name
  </code></pre>
  
  <p>I've figured out the LINQ to get the DISTINCT values which is.</p>
  
  <pre><code>var query = ds.Tables[0].AsEnumerable()
      .OrderBy(dr1 =&gt; dr1.Field&lt;string&gt;("state"))
      .Select(dr1 =&gt; new {state = dr1.Field&lt;string&gt;("state")})
      .Distinct().ToList();
  </code></pre>
  
  <p>But I can't figure out how to get the COUNT(*) for each distinct value to work in LINQ.  Any idea how I can add that into the LINQ query?</p>