title: Linq to Entities - Group by twice - NotSupportedException
body: <p>I have a somewhat complex query with two groups, I want to get the total sale for each customer by date.</p>
  
  <p>In this example I am getting them for today and yesterday<br>
  I am using LinqPad to test my query and also we are currently using VB.NET</p>
  
  <p>This is my query:</p>
  
  <pre><code>dim todayDate = new Date(2016, 7, 25)
  dim yesterdayDate = new Date(2016, 7, 24)
  
  Dim forecast = SalesHeaders _
    .Join(Customers, Function(x) x.CustomerID, Function(c) c.CustomerID, Function(SHeader, Customer) New With {SHeader, Customer}) _
    .Join(SalesDetails, Function(s) s.SHeader.HeaderID, Function(sd) sd.HeaderID, Function(SHeader, SDetail) New With {SHeader, SDetail}) _
    .Join(SalesValues, Function(x) x.SDetail.DetailID, Function(sv) sv.DetailID, Function(x, SValue) New With {x.SHeader, x.SDetail, SValue}) _
                  .Where(Function(x) x.SValue.Date = todayDate.Date OrElse x.SValue.Date = yesterdayDate.Date) _
    .GroupBy(Function(x) New With {x.SFHeader.Customer.AccountCode}) _
    .Select(Function(g) New With
    {
        .CustomerAccountCode = g.Key,
        .ForecastData = g.GroupBy(Function(x) x.SFValue.Date) _
                        .Select(Function(gg) New With
                        {
                            .Sales = gg.Sum(Function(x) x.SDetail.SalesQty),
                            .Date = gg.Key
                        }).ToList()
    }).ToList().dump()
  </code></pre>
  
  <p>I am getting an exception which seems to be coming from the first GroupBy.<br> I am getting <code>NotSupportedException Unable to cast the type 'System.Linq.IGrouping2[[VB$AnonymousType_3 ...] to type 'System.Collections.Generic.IEnumerable1[[VB$AnonymousType_2...]</code>
  LINQ to Entities only supports casting EDM primitive or enumeration types.</p>
  
  <p>I suspect because of the number of joins, the number of Anonymous types is confusing things.<br>
  I was thinking that I might need to do a Select before my first GroupBy, but I don't know what the Select will be.</p>
  
  <p>When I place a <code>AsEnumerable()</code> call straight after the first GroupBy then it works okay, but that I susspect is because the AsEnumerable() is executing the query and so the GroupBys are happening in memory, not on the database.</p>
  
  <p>I'm hoping some bright spark might be able to tell me what is causing the exception and why.<br>
  Hopefully you can come up with a better query them mine too.</p>
  
  <p>Thanks :-)</p>