Linq to Entity Group by Date

Question

A lot of research is done, but it's hard to handle. Consider a database that has a transaction table "CreatedOn", "Amount", "Type". I need to be able to query an entity to get transactions of a certain type, grouped together by different dates (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 request that would pull out all the deposits, grouped by month, so that he could give:

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

How could I then adapt this query as a day, not a month?

Here is my current block of code, but I get linq inner exception

Unable to group 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 read, maybe this is a bug?

Answer 1

 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 to see if linq-2 entities agree.

Answer 2

Check out the code mentioned in my question: Group by week in LINQ to Entities . It shows how you can group data by day and month. Let me know if you have any other questions.

Answer 3

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