如何使用LINQ自连接语法统计指定小时块内同时活跃的作业数量

• Jobs表：以JobId为主键的主文件记录表
• PostThr表：用于记录作业的起止调度信息，共包含5个字段：
  • ID：记录主键
  • FK：关联作业的外键，对应单个独立作业
  • ThrDate(DateTime)：调度记录所属日期
  • ThrTime(TimeSpan)：调度记录对应的时间点
  • ThrText(string)：调度记录类型，每个作业固定对应1条CONFIRM START-类型的开始记录、1条CONFIRM END-类型的结束记录，所有作业均不跨日运行，无隔夜执行场景

基于PostThr表的记录，统计同一小时块内同时处于活跃状态的作业数量。

原有查询逻辑存在根本性错误：单条PostThr记录的ThrText字段不可能同时等于CONFIRM START-和CONFIRM END-两个互斥值，因此该查询永远无法匹配到有效数据，返回结果恒为0。

核心逻辑为通过自连接配对同一个作业的开始、结束记录，再筛选运行时间区间与目标小时块存在重叠的作业计数。
时间重叠判定规则：作业开始时间 < 目标小时块结束时间，且作业结束时间 > 目标小时块开始时间，即判定该作业在当前小时块内处于活跃状态。

// 统计参数：指定统计日期、目标小时块起始时间
DateTime givenDate = DateTime.Parse("2022/7/2");
TimeSpan blockStart = TimeSpan.Parse("9:00");
TimeSpan blockEnd = blockStart.Add(TimeSpan.FromHours(1));

int activeJobCount = _context.PostThrs
    // 过滤指定日期的所有作业开始记录
    .Where(s => s.ThrDate == givenDate && s.ThrText == "CONFIRM START-")
    // 自连接匹配同作业、同日期的结束记录
    .Join(
        _context.PostThrs.Where(e => e.ThrDate == givenDate && e.ThrText == "CONFIRM END-"),
        startRec => startRec.FK,
        endRec => endRec.FK,
        (startRec, endRec) => new { Start = startRec.ThrTime, End = endRec.ThrTime }
    )
    // 筛选运行区间与目标小时块重叠的作业
    .Where(job => job.Start < blockEnd && job.End > blockStart)
    .Count();

如果需要统计全天24个小时块的活跃作业数，可先在内存中生成所有小时块的起止时间列表，再基于上述逻辑批量计算，减少数据库查询次数。

内容的提问来源于stack exchange，提问作者Nick Fleetwood