c# - .DefaultIfEmpty() 基于日期值

Question

我有一个 Entity Framework 模型 Schedule 映射到表 dbo.Schedule。 Schedule 中的两个字段是 hours(decimal)和 week_ending(DateTime ).

我想像这样将 Schedule 表中的数据输出到 JSON:

{
   "week": [
         "2017-08-11",
         "2017-08-18",
         "2017-08-25",
         "2017-09-01"],
   "hours": [
         40,  
         40,
          0,
         30]
}

换句话说，我想将 week_ending 和 hours 结果连接成两个数组，其中结果按分组周，并且当该周没有记录时，为 hours 插入一个 0 值。

我知道 .DefaultIfEmpty() 可以完成类似于后者的事情，但我不知道如何将“空”定义为“两个查询日期之间相隔超过 7 天” "(即缺少一周)。 week_ending 值始终为星期五，因此它们始终相隔 7 天。

我不确定从哪里开始......我有一个相当基本的 LINQ 查询(省略了不相关的 Where 子句):

var data =
  db.Schedules
    .OrderBy(s => s.week_ending)
    .Select(s => new
    { 
        week = s.week_ending,
        hours = s.hours
    });

在序列化后生成此 JSON:

[
    {
        "week": "2017-08-11",
        "hours": 20
    },
    // I would like for this record to be grouped 
    // with the first into one "hours": 40 record
    {
        "week": "2017-08-11",
        "hours": 20
    },
    {
        "week": "2017-08-18",
        "hours": 40
    },
    // there is no "2017-08-25 record in the DB, 
    // but I would like for one to be printed with "hours": 0
    {
        "week": "2017-09-01",
        "hours": 30
    }
]

Answer 1

您可能会考虑以下方法

1. 枚举符合开始/结束日期之间标准的时间表的聚合
2. 识别并添加缺失的日期，然后排序结果
3. 创建一个 data 对象以匹配所需的输出

代码可能如下所示以所需格式保留 data:

// Simulate linq connection
var db = new
{
    Schedules = new List<Schedule>
    {
        new Schedule() { hours = 40, week_ending = new DateTime(2017,8,11) },
        new Schedule() { hours = 20, week_ending = new DateTime(2017,8,18) }, // Simulating multiple records
        new Schedule() { hours = 20, week_ending = new DateTime(2017,8,18) }, // Simulating multiple records
        // No records for 8/25
        new Schedule() { hours = 30, week_ending = new DateTime(2017,9,1) },
    }
};

// Need a start/end date so you can generate missing weeks
var startDate = new DateTime(2017, 8, 11);
var endDate = new DateTime(2017, 9, 1);

// Enumerate schedules from db
var schedules = db.Schedules // Add any other criteria besides date logic
    .Where(m => m.week_ending >= startDate && m.week_ending <= endDate)
    .GroupBy(m => m.week_ending)
    .Select(m => new Schedule() { week_ending = m.Key, hours = m.Sum(s => s.hours) })
    .AsEnumerable();

// Add missing dates
var results = Enumerable.Range(0, 1 + endDate.Subtract(startDate).Days)
    .Select(m => startDate.AddDays(m))
    .Where(m => m.DayOfWeek == DayOfWeek.Friday) // Only end of week
    .Where(m => schedules.Any(s => s.week_ending == m) == false) // Add missing weeks
    .Select(m => new Schedule() { week_ending = m, hours = 0 })
    .Union(schedules)
    .OrderBy(m => m.week_ending);

// Enumerate the ordered schedules matching your criteria
var data = new
{
    week = results.Select(m => m.week_ending),
    hours = results.Select(m => m.hours)
};