如何通过DateTime.Date在EntityFramework中进行分组 [英] How to group by DateTime.Date in EntityFramework

问题描述

我有一个设备型号：

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

我想选择设备数，按添加日期（不是日期和时间，而只能是日期）。我当前的实现是无效的，因为linq无法将 DateTime.Date 转换为sql：

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?

推荐答案

嗯，根据这个 MSDN文件 Date 属性由LINQ to SQL支持，我会假设Entity Framework也支持它。

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.

无论如何，尝试这个查询（注意我正在使用 TruncateTime 方法，以避免重复地解析日期）：

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

希望这有帮助。

这篇关于如何通过DateTime.Date在EntityFramework中进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！