title: EF Core 3.0 SumAsync triggers aggregate function exception
body: <p>I am in the process of upgrading to EF Core 3.0 and .NET Core 3.0, but some of my queries stopped working. Here is an example:</p>
  
  <p>I have a table called <code>Bins</code>, I have another table which is called <code>BinItems</code>, now it has, of course, a one to many relationship. <code>BinItems</code> has a property called <code>Qty</code>, and I want to sum up all the <code>Qty</code> from <code>BinItems</code> based on criteria given by the client in a filter.</p>
  
  <p>So here is the code:</p>
  
  <pre><code>var query = _binRepository.Table;
  
  
  if (filter.LastRecountDate != null) {
      query = query.Where(x =&gt; x.LastRecountDate.Date == filter.LastRecountDate.Value.Date);
  }
  
  if (filter.StartRecountDate != null) {
      query = query.Where(x =&gt; x.LastRecountDate.Date &gt;= filter.StartRecountDate.Value.Date);
  }
  
  if (filter.EndRecountDate != null) {
      query = query.Where(x =&gt; x.LastRecountDate.Date &lt;= filter.EndRecountDate.Value.Date);
  }
  
  if (filter.Active != null) {
      query = query.Where(x =&gt; x.Active == filter.Active);
  }
  
  if (!string.IsNullOrEmpty(filter.BinLocation)) {
      query = query.Where(x =&gt; x.BinLocation == filter.BinLocation);
  }
  
  if (!string.IsNullOrEmpty(filter.Gtin)) {
      query = query.Where(x =&gt; x.BinItems.Any(o =&gt; o.UPC == filter.Gtin));
  }
  
  if (filter.WarehouseIds.Count() &gt; 0) {
      query = query.Where(x =&gt; filter.WarehouseIds.Contains(x.Zone.Id));
  }
  
  if (!string.IsNullOrEmpty(filter.Keywords)) {
      query = query.Where(x =&gt; x.BinItems.Select(o =&gt; o.UPC).Contains(filter.Keywords));
  }
  
  query = query.Include(x =&gt; x.BinItems).Include(x =&gt; x.Zone);
  
  if (!string.IsNullOrEmpty(filter.Keywords)) {
      return await query.SumAsync(x =&gt; x.BinItems.Where(p =&gt; p.UPC.Contains(filter.Keywords)).Sum(o =&gt; o.Qty));
  }
  
  return await query.SumAsync(x =&gt; x.BinItems.Sum(o =&gt; o.Qty));
  </code></pre>
  
  <p>I get an exception thrown:</p>
  
  <blockquote>
    <p>Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot perform an
    aggregate function on an expression containing an aggregate or a
    subquery.</p>
  </blockquote>
  
  <p>It worked perfectly OK in .NET Core 2.1 and EF Core 2, but now I keep getting these errors in all my queries that I do this way.</p>
  
  <p>Any idea how I can get this work in .NET Core 3.0/EF Core 2?</p>