myTable.GroupBy(mt => mt.ActivityDate.Date).
OrderBy(mt => mt.Key).
Select(mt => new{Date= mt.Key, Count = mt.Count()}).Take(90).ToList()
This resulted in the following exception:
NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
This was problematic because I need to report on a count of items in the database for each day of the last 90 days.
I found a post on the MSDN forums that gave a workaround for doing the group by that worked great, MSDN. So I updated my original statement to this:
myTable.GroupBy(mt => new {mt.ActivityDate.Year, mt.ActivityDate.Month, mt.ActivityDate.Day}).
OrderBy(mt => mt.Key).
Select(mt => new{Date= mt.Key, Count = mt.Count()}).Take(90).ToList()
This works but then you have this funny anonymous type and displaying it isn't pretty. So my next problem was figuring out how to display this data. I updated my statement to convert the Key into a DateTime type:
myTable.GroupBy(mt => new {mt.ActivityDate.Year, mt.ActivityDate.Month, mt.ActivityDate.Day}).
OrderBy(mt => mt.Key).
Select(mt => new{Date = new DateTime(mt.Key.Year, mt.Key.Month, mt.Key.Day), Count = mt.Count()}).Take(90).ToList()
At first glance you might think this works but it doesn't. Instead you get the following exception at runtime.
NotSupportedException: Only parameterless constructors and initializers are supported in LINQ to Entities.
A link over on Don't Be Iffy took me back to MSDN to find out
The idea of supporting only parameter-less constructor was one of the hard decisions we made as a product team. The main idea with this approach was we shouldn't open up new surface area in LINQ over Entities that is not supported by EDM. EDM in general doesn't allow you to construct in random objects (using the NEW constructor in eSQL). To make it consistent with the whole stack, we decided to implement it like wise. Limiting the constructions only to:
- Parameterless constructors
- Anonymous types (as it’s the only way to do multi-project in LINQ over Entities)
I obviously wasn't going to be able to do what I needed in Linq to Entities so I had to find a work around. I figured once I had the data from the DB that I needed I shouldn't have any problems doing this, especially since I didn't have these same problems in Linq to SQL. Note: I considered going the Linq to SQL route but I wasn't quite ready to give up on Linq to Entities yet.
What I decided to do instead was to grab the data from EF without using deferred execution and then create a new anonymous type locally. Below is the completed solution.
var data = myTable.
GroupBy(ra => new {ra.ActivityDate.Year, ra.ActivityDate.Month, ra.ActivityDate.Day}).
OrderBy(ra => ra.Date).
Select(ra => new{Key = ra.Key, Count = ra.Count()}).
Take(90).ToList();
data.Select(ra => new {Date = new DateTime(ra.Key.Year, ra.Key.Month, ra.Key.Day), Count = ra.Count});
This worked exactly as I expected it to and it only required some minor modification on my part to get it there. Hopefully this helps someone else out there that is having a similar issue.
Posts
