How to group by DateTime.Date in EntityFramework

c# entity-framework entity-framework-6 linq

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?

Accepted Answer

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.


Popular Answer

Use EntityFunctions.TruncateTime

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why