Categories
c# entity-framework linq sql

Problems trying to use GroupBy with multiple properties using the LINQ Method Syntax

I’ve got the following data:

Type (enum)   Date (DateTime)       Count (int)
Red 2014-07-27 11:00:00 1
Red 2014-07-27 10:00:00 1
Red 2014-07-27 09:00:00 1
Blue 2014-07-27 11:00:00 1
Blue 2014-07-27 10:00:00 1
Blue 2014-07-27 09:00:00 1

I would like to first group by the Type and then Sum the Count for every day.

My desired output would be:

Type (enum)   Date (DateTime)       Count (int)
Red 2014-07-27 3
Blue 2014-07-27 3

The following code will group by day like I want it to but I have no idea how to combine both grouping by Type and by Date:

_entityContext.Statistics.
.GroupBy(s => new { s.DateTime.Year, s.DateTime.Month, s.DateTime.Day})

I’ve been struggling this for a bit now and I end up with complex IGrouping structures and I’m kind of stuck now. Google steers me towards Query Syntax structures but I’m really wondering if this is possible using the Method Syntax. Last I knew Query Syntax gets translated to Method Syntax so it should be possible?

Anyone can steer me in the right direction?

Note:
LINQ TO Entities doesn’t support accessing the ‘Date’ property on ‘DateTime’. The exception you will get is: “The specified type member ‘Date’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported”