How to group by DateTime.Date in EntityFramework

Question

I have a device model:

public class DeviceModel
{
    public DateTime Added { get;set; }
}

And i want to select devices count, grouped by Added date (not Date and Time, but only date). My current implementation is not valid, because linq can't translate DateTime.Date to sql:

var result = (
    from device in DevicesRepository.GetAll()
    group device by new { Date = device.Added.Date } into g
    select new
        {
            Date = g.Key.Date,
            Count = g.Count()
        }
    ).OrderBy(nda => nda.Date);

How to change this query to make it work?

Answer 1

Well, according to this MSDN document, Date property is supported by LINQ to SQL and I'd assume that Entity Framework supports it as well.

Anyway, try this query (notice that I'm using TruncateTime method in order to avoid resolving the date repeatedly):

var result = from device in
                 (
                     from d in DevicesRepository.GetAll()
                     select new 
                     { 
                         Device = d, 
                         AddedDate = EntityFunctions.TruncateTime(d.Added) 
                     }
                 )
             orderby device.AddedDate
             group device by device.AddedDate into g
             select new
             {
                 Date = g.Key,
                 Count = g.Count()
             };

Hope this helps.