我想使用 Linq to Entities 将活动列表总结为上周按天发生的次数。例如,假设我的数据库中有这样的数据:
Id | Date
1 | 2011-09-30 0:00:0
2 | 2011-10-02 0:00:00
3 | 2011-10-02 0:00:00
4 | 2011-10-02 0:00:00
5 | 2011-10-04 0:00:00
6 | 2011-10-04 1:30:30
7 | 2011-10-04 0:00:00
8 | 2011-10-06 0:00:00
假设今天的日期是 2011-10-07
我想生成以下内容:
Date | Count
2011-10-01 | 0
2011-10-02 | 3
2011-10-03 | 0
2011-10-04 | 3
2011-10-05 | 0
2011-10-06 | 1
2011-10-07 | 0
这是一个示例技术,我可以使用它按日期对出现的情况进行分组,但我缺少零。
// Using Linq to Objects for demonstration purpose only
var activities = new List<Activity>();
activities.Add(new Activity { Id = 1, Date = new DateTime(2011, 9, 30)});
activities.Add(new Activity { Id = 2, Date = new DateTime(2011, 10, 2)});
activities.Add(new Activity { Id = 3, Date = new DateTime(2011, 10, 2)});
activities.Add(new Activity { Id = 4, Date = new DateTime(2011, 10, 2)});
activities.Add(new Activity { Id = 5, Date = new DateTime(2011, 10, 4)});
activities.Add(new Activity { Id = 6, Date = new DateTime(2011, 10, 4, 1, 30, 30) });
activities.Add(new Activity { Id = 7, Date = new DateTime(2011, 10, 4)});
activities.Add(new Activity { Id = 8, Date = new DateTime(2011, 10, 6)});
var data = (from a in activities
group a by a.Date.Date into g
where g.Key > DateTime.UtcNow.AddDays(-7)
select new {
Date = g.Key,
Count = g.Count()
}).ToList();
结果如下:
Date | Count
2011-10-02 | 3
2011-10-04 | 3
2011-10-06 | 1
有人知道如何使用 Linq to Entities 包含缺失的零吗?一旦将结果存入内存,我总是可以枚举结果,但直接从数据库获取结果会更好。