Group by DateTime.Date in Linq to SQL with EF 4.1

Question

I'm trying to work out how to group each item by the day it occurred within a time range in Linq to SQL, but I cannot get it working.

   var graph = from o in DB.Context.Occurances
            where o.Occurred > fromDate && o.Occurred <= toDate
            group o by o.Occurred.Date into g
            orderby g.Key ascending 
            select new
                       {
                           Date = g.Key,
                           Occurances = g.Count()
                       };

Every time I run this I get the error

"The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

Searching I found this question Here that addresses this issue... but when running this using Entity Framework, I get the exception

"The function is not recognized by SQL Server Compact. [ Name of function = TRUNCATETIME,Data type (if known) = ]"

Any ideas would be greatly appreciated. Thanks!

Answer 1

The only solution I know of that will definitely work is to manually select a concatenation of the Year, Month, Day properties using String.Concat or integer arithmetic and group on that.

Source: CLR Method to Canonical Function Mapping