使用linq组按日期查询填写缺少的日期 [英] Filling in missing dates using a linq group by date query

问题描述

我有一个Linq查询，基本上计算在特定的一天创建了多少个条目，这是通过年份，月份，日期进行分组而完成的。问题是，由于有些日子不会有任何条目，我需要填写那些缺少日历日的条目为0计数。
我的猜测是，这可能是使用联盟或某些东西，或者甚至可以使用一些简单的循环来处理查询后的记录。

以下是查询：

  from l in context.LoginToken 
其中l.CreatedOn> ; =开始&& l.CreatedOn< = finish 
 group l by 
 new {l.CreatedOn.Year，l.CreatedOn.Month，l.CreatedOn.Day}分组为
 orderby groups.Key.Year ，groups.Key.Month，groups.Key.Day 
选择新的StatsDateWithCount {
 Count = groups.Count（），
 Year = groups.Key.Year，
 Month = group.Key.Month，
 Day = groups.Key.Day 
}））; 
  

如果我的数据为12/1 - 12/4/2009像（简化）：

  12/1/2009 20 
 12/2/2009 15 
 12/4/2009 16 
  

我希望通过代码添加 12/3/2009 0 / p>

我知道一般来说，这应该是使用一个非规范化的表来填充数据或加入到日历表中的数据库，但是我的问题是如何在代码中实现？

可以在Linq中完成吗？是否应该在Linq中完成？

解决方案

我从数据库收集完整的数据，然后生成一个样本空表。最后，我使用真实数据对空表进行了外部连接，并使用DefaultIfEmpty（）结构来处理数据库中缺少一行，以使用默认值来填充它。

这是我的代码：

  int days = 30; 
 
 //收集我们在数据库中的数据，这对于图表是不完整的（即缺少的日期/子系统）。 
 var dataQuery = 
来自tr在SourceDataTable 
其中（DateTime.UtcNow  -  tr.CreatedTime）.Days< 30 
通过新的{tr.CreatedTime.Date，tr.Subsystem}到组g 
中的组tr tr，g.Key.Date升序，g.Key.SubSystem升序
选择新的MyResults（）
 {
 Date = g.Key.Date，
 SubSystem = g.Key.SubSystem，
 Count = g.Count（）
}; 
 
 //生成我们想要的子系统列表。 
 var subsystems = new [] {SubSystem.Foo，SubSystem.Bar} .AsQueryable（）; 
 
 //生成我们想要的日期列表。 
 var datetimes = new List< DateTime>（）; （int i = 0; i< days; i ++）
 {
 datetimes.Add（DateTime.UtcNow.AddDays（-i）.Date）; 
 
} 
 
 //生成空表，这是我们想要但没有计数的输出的形状。 
 var emptyTableQuery = 
从数组中的dt 
从子系统中的子系统
选择新的MyResults（）
 {
 Date = dt.Date，
 SubSystem = subsys，
 Count = 0 
}; 
 
 //使用真实数据执行空表的外部连接，并使用魔术DefaultIfEmpty 
 //来处理数据库中没有数据库。 
 var finalQuery = 
 from e in emptyTableQuery 
在dataQuery中连接realData 
 new {e.Date，e.SubSystem}等于
 new {realData.Date，realData .SubSystem}从g.DefaultIfEmpty（）中的realDataJoin到g 
 
选择新的MyResults（）
 {
 Date = e.Date，
 SubSystem = e.SubSystem ，
 Count = realDataJoin == null？ 0：realDataJoin.Count 
}; 
 
 return finalQuery.OrderBy（x => x.Date）.AsEnumerable（）; 
  

I have a Linq query that basically counts how many entries were created on a particular day, which is done by grouping by year, month, day. The problem is that because some days won't have any entries I need to back fill those missing "calendar days" with an entry of 0 count. My guess is that this can probably be done with a Union or something, or maybe even some simple for loop to process the records after the query.

Here is the query:

from l in context.LoginToken
 where l.CreatedOn >= start && l.CreatedOn <= finish
 group l by
 new{l.CreatedOn.Year, l.CreatedOn.Month, l.CreatedOn.Day} into groups
 orderby groups.Key.Year , groups.Key.Month , groups.Key.Day
     select new StatsDateWithCount {
                                    Count = groups.Count(),
                                     Year =  groups.Key.Year,
                                    Month = groups.Key.Month,
                                      Day = groups.Key.Day
                                                                  }));

If I have data for 12/1 - 12/4/2009 like (simplified):

12/1/2009 20
12/2/2009 15
12/4/2009 16

I want an entry with 12/3/2009 0 added by code.

I know that in general this should be done in the DB using a denormalized table that you either populate with data or join to a calendar table, but my question is how would I accomplish this in code?
Can it be done in Linq? Should it be done in Linq?

解决方案

I just did this today. I gathered the complete data from the database and then generated a "sample empty" table. Finally, I did an outer join of the empty table with the real data and used the DefaultIfEmpty() construct to deal with knowing when a row was missing from the database to fill it in with defaults.

Here's my code:

int days = 30;

// Gather the data we have in the database, which will be incomplete for the graph (i.e. missing dates/subsystems).
var dataQuery =
    from tr in SourceDataTable
    where (DateTime.UtcNow - tr.CreatedTime).Days < 30
    group tr by new { tr.CreatedTime.Date, tr.Subsystem } into g
    orderby g.Key.Date ascending, g.Key.SubSystem ascending
    select new MyResults()
    {
        Date = g.Key.Date, 
        SubSystem = g.Key.SubSystem,
        Count = g.Count()
    };

// Generate the list of subsystems we want.
var subsystems = new[] { SubSystem.Foo, SubSystem.Bar }.AsQueryable();

// Generate the list of Dates we want.
var datetimes = new List<DateTime>();
for (int i = 0; i < days; i++)
{
    datetimes.Add(DateTime.UtcNow.AddDays(-i).Date);
}

// Generate the empty table, which is the shape of the output we want but without counts.
var emptyTableQuery =
    from dt in datetimes
    from subsys in subsystems
    select new MyResults()
    {
        Date = dt.Date, 
        SubSystem = subsys,
        Count = 0
    };

// Perform an outer join of the empty table with the real data and use the magic DefaultIfEmpty
// to handle the "there's no data from the database case".
var finalQuery =
    from e in emptyTableQuery
    join realData in dataQuery on 
        new { e.Date, e.SubSystem } equals 
        new { realData.Date, realData.SubSystem } into g
    from realDataJoin in g.DefaultIfEmpty()
    select new MyResults()
    {
        Date = e.Date,
        SubSystem = e.SubSystem,
        Count = realDataJoin == null ? 0 : realDataJoin.Count
    };

return finalQuery.OrderBy(x => x.Date).AsEnumerable();

这篇关于使用linq组按日期查询填写缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！