title: Entity Framework Core Count inside Select
body: <p>I have created a method that filters my dataset. I also created another method that counts the amount of items there are with the filters applied. This method looks like this:</p>
  
  <pre><code>return new Count
          {
              Address = this.Filter(/* paramters to filter */)
                  .GroupBy(person =&gt; person.Address.City)
                  .Select(group =&gt; new Amount&lt;string&gt;
                  {
                      Type = group.Key /* so the city */,
                      Count = group.Count()
                  }),
          };
  </code></pre>
  
  <p>Count has a couple of lists of amount objects. The amount objects holds the name of what it will filter on and a count of how many the filter contains. </p>
  
  <pre><code>this.Filter()
  </code></pre>
  
  <p>Is a private method that will return an IQueryable from the DbContext. </p>
  
  <p>Everything works, however, it is very slow with a large amount of data. The reason for this is that the GroupBy() and the Count() could not be translated and will be evaluated locally. It gives the following warnings:</p>
  
  <pre><code>warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'GroupBy([entity.RequestRating], [entity])' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'Count()' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'Count()' could not be translated and will be evaluated locally.
  </code></pre>
  
  <p>What would be a better approach to this? This is a ASP.NET CORE project</p>