LINQ的：count行包括0缺失行组 [英] Linq: Count Groups of Rows Including 0 For Missing Rows

问题描述

我想总结的活动清单到事件由日上周的数量，使用LINQ到实体。例如，假设我有数据像这样在我的数据库：

I'd like to summarize a list of activities into the number of occurrences by Day for the last week, using Linq to Entities. For example, let's say I have data like this in my database:

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

and let's say today's date is 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

下面是一个示例技术，我可以使用组发生日期，但我错过了。零

Here's a sample technique I can use to group the occurrences by date, but I'm missing the zeros.

// 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到实体失踪为零？我总能列举的结果，一旦我有它在内存中，但它会很高兴地直接从数据库中获取它。

Anyone know how I can include the missing zeros using Linq to Entities? I could always enumerate the results once I have it in memory, but it would be nice to get it directly from the database.

推荐答案

这可能是一样容易与循环来产生，因为数据不是已经在数据库可用，并使其通过LINQ将是复杂：

It might be just as easy to generate with a loop since the data is not available in the database already, and making it through LINQ will be complicated:

var firstDate = data.Min(d => d.Date).AddDays(-1);
var lastDate = data.Max(d => d.Date).AddDays(1);

for (var date = firstDate; date <= lastDate; date = date.AddDays(1))
    if (!data.Exists(d => d.Date == date))
        data.Add(new { Date = date, Count = 0 });

data = data.OrderBy(d => d.Date);
// do something with data

除非你的数据集是真的，真的大，这内存中的查询和列表的调整将花生相比花费数据库请求的时间。

Unless your data set is really, really large, this in-memory querying and adjustment of the list will be peanuts compared to the time it takes for the database request.

这篇关于LINQ的：count行包括0缺失行组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！