Linq to Entity Group by Date

Question

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 rather 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?

I am trying to achieve exactly similar functionality in LINQ to EF but no success yet, did u get it working? — Anand, Aug 11 '12 at 15:52

score 1 · Answer 1 · answered Mar 25 '11 at 22:47

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.

hey, this looks very solid. I tried it and get the inner exception 'Can't group on A1'. My query looks like: 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(); — Luke Belbina, Mar 25 '11 at 23:04

score 0 · Answer 2 · answered Feb 16 '13 at 14:29

0

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

answered Feb 16 '13 at 14:29

HiveHicks

2,294
5
28
42

Yah, problem with this is ToList executes the SQL query and the group by will then be local so it can end up pulling a ton of data. – Luke Belbina Feb 20 '13 at 16:47
1

@nextgenneo Yes, but it's better than nothing :) – HiveHicks Feb 23 '13 at 06:18

score 0 · Answer 3 · edited May 23 '17 at 12:23

0

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.

edited May 23 '17 at 12:23

Community

1
1

answered Mar 25 '11 at 22:43

StriplingWarrior

151,543
27
246
315

Linq to Entity Group by Date

3 Answers3