Linq to Entity Group by Date

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).

So imagine a table with:

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

I could have a query which would pull all deposits grouped by month so it could yield:

2011-1 $300 1deposit
2011-2 $340 1deposit
2011-3 $150 2deposits

How would I then adapt this query to be by day rath开发者_如何学Goer than month?

Here's my current block of code but I get an inner linq exception

Can't group on A1

var result = context.TransactionEntities.Where(d => d.Type == "Deposit")                                     
                    .GroupBy(g => new { g.CreatedOn.Year, g.CreatedOn.Month })
                    .Select(g => new
                           {
                                 DateKey = g.Key,
                                 TotalDepositAmount = g.Sum(d => d.Amount),
                                 DepositCount = g.Count()
                            }).ToList();

Note: I am currently using the MySql connector and I've read possibly this is a bug?

---

Func<DateTime, object> groupByClause;
if (groupByDay) groupByClause = date => date.Date;
else if (groupByMonth) groupByClause = date => new { date.Year, date.Month};
else throw new NotSupportedException("Some option should be chosen");

var result = data.Where(d => d.Type == "Deposit")
                 .GroupBy(groupByClause)
                 .Select(g => new { DateKey = g.Key,
                                    TotalDepositAmount = g.Sum(d => d.Amount),
                                    DepositCount = g.Count(),
                 });

Of course this should be checked whether linq-2-entities will accept it.

---

Check the code mentioned in my question: Group by Weeks in LINQ to Entities. It shows how you can group your data by days and months. Let me know if you have any other questions.

---

This is probably a bug in MySQL connector. My solution for that was to place .ToList() just before .GroupBy().