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?

1
4
1/17/2014 12:56:15 PM

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.

11
1/17/2014 12:51:43 PM

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);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow