title: Linq to Entity Group by Date
body: <p>Done a lot of research but still having a tough one with this. Consider a database which has a transactions table of "CreatedOn", "Amount", "Type". I need to be able to do an entity query to get transactions of a certain type grouped together by different date granularities (month / day etc).</p>
  
  <p>So imagine a table with:</p>
  
  <pre><code>2011/1/22   $300   Deposit
  2011/2/18   $340   Deposit
  2011/3/6   $200    Other
  2011/3/6   $100    Deposit
  2011/3/7   $50     Deposit
  </code></pre>
  
  <p>I could have a query which would pull all deposits grouped by month so it could yield:</p>
  
  <pre><code>2011-1 $300 1deposit
  2011-2 $340 1deposit
  2011-3 $150 2deposits
  </code></pre>
  
  <p>How would I then adapt this query to be by day rather than month?</p>
  
  <p>Here's my current block of code but I get an inner linq exception </p>
  
  <blockquote>
    <p>Can't group on A1</p>
  </blockquote>
  
  <pre><code>var result = context.TransactionEntities.Where(d =&gt; d.Type == "Deposit")                                     
                      .GroupBy(g =&gt; new { g.CreatedOn.Year, g.CreatedOn.Month })
                      .Select(g =&gt; new
                             {
                                   DateKey = g.Key,
                                   TotalDepositAmount = g.Sum(d =&gt; d.Amount),
                                   DepositCount = g.Count()
                              }).ToList();
  </code></pre>
  
  <p>Note: I am currently using the MySql connector and I've read possibly this is a bug?</p>