title: Entity Framework: Efficiently grouping by month
body: <p>I've done a bit of research on this, and the best I've found so far is to use an Asenumerable on the whole dataset, so that the filtering occurs in linq to objects rather than on the DB.  I'm using the latest EF.</p>
  
  <p>My working (but very slow) code is:</p>
  
  <pre><code>        var trendData = 
              from d in ExpenseItemsViewableDirect.AsEnumerable()
              group d by new {Period = d.Er_Approved_Date.Year.ToString() + "-" + d.Er_Approved_Date.Month.ToString("00") } into g
              select new
              {
                  Period = g.Key.Period,
                  Total = g.Sum(x =&gt; x.Item_Amount),
                  AveragePerTrans = Math.Round(g.Average(x =&gt; x.Item_Amount),2)
              };
  </code></pre>
  
  <p>This gives me months in format YYYY-MM, along with the total amount and average amount.  However it takes several minutes every time.</p>
  
  <p>My other workaround is to do an update query in SQL so I have a YYYYMM field to group natively by.  Changing the DB isn't an easy fix however so any suggestions would be appreciated.</p>
  
  <p>The thread I found the above code idea (http://stackoverflow.com/questions/1059737/group-by-weeks-in-linq-to-entities) mentions 'waiting until .NET 4.0'.  Is there anything recently introduced that helps in this situation?</p>