title: Optimize/rewrite LINQ query with GROUP BY and COUNT
body: <p>I'm trying to get a count of unique Foos and Bars grouped by Name, on the following data set.</p>
  
  <pre><code>Id  |   IsActive    |   Name    |   Foo     |   Bar
  1   |       1       |   A       |   11      |   null
  2   |       1       |   A       |   11      |   null
  3   |       1       |   A       |   null    |   123
  4   |       1       |   B       |   null    |   321
  </code></pre>
  
  <p>I expect the result on the above data to be:</p>
  
  <pre><code>Expected:
  A = 2;
  B = 1;
  </code></pre>
  
  <p>I tried to group by Name,Foo,Bar and then group by Name again with a count to get the "row" count. But that didn't give me the correct result. (or the ToDictionary threw a duplicate key, I played around with this a lot so can't quite remember)</p>
  
  <pre><code>db.MyEntity
      .Where(x =&gt; x.IsActive)
      .GroupBy(x =&gt; new { x.Name, x.Foo, x.Bar })
      .GroupBy(x =&gt; new { x.Key.Name, Count = x.Count() })
      .ToDictionary(x =&gt; x.Key, x =&gt; x.Count);
  </code></pre>
  
  <p>So I came up with this LINQ query. But it's rather slow.</p>
  
  <pre><code>db.MyEntity
      .Where(x =&gt; x.IsActive)
      .GroupBy(x =&gt; x.Name)
      .ToDictionary(x =&gt; x.Key,
          x =&gt;
              x.Where(y =&gt; y.Foo != null).Select(y =&gt; y.Foo).Distinct().Count() +
              x.Where(y =&gt; y.Bar != null).Select(y =&gt; y.Bar).Distinct().Count());
  </code></pre>
  
  <p>How can I optimize it?</p>
  
  <p>Here's the entity for refernece</p>
  
  <pre><code>public class MyEntity
  {
      public int Id { get; set; }
      public bool IsActive { get; set; }
      public string Name { get; set; }
      public int? Foo { get; set; }
      public int? Bar { get; set; }
  }
  </code></pre>
  
  <h2>Edit</h2>
  
  <p>I also tried this query</p>
  
  <pre><code>db.MyEntity
      .Where(x =&gt; x.IsActive)
      .GroupBy(x =&gt; new { x.Name, x.Foo, x.Bar })
      .GroupBy(x =&gt; x.Key.Name)
      .ToDictionary(x =&gt; x.Key, x =&gt; x.Count());
  </code></pre>
  
  <p>But that threw a timeout exception :(</p>