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!

http://stackoverflow.com/questions/4188066/linq-to-entities-group-by-failure-using-date has very clean answer — Anand, Aug 11 '11 at 09:21

score 1 · Answer 1 · answered May 14 '15 at 12:29

1

Use DbFunctions.TruncateTime(o.Occurred) to get Date in LINQ to SQL.

answered May 14 '15 at 12:29

Dmitry Bogatykh

485
5
10

score 1 · Accepted Answer · answered Apr 07 '11 at 12:12

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

answered Apr 07 '11 at 12:12

Jon

428,835
81
738
806

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

2 Answers2