如何查询跨多天的Timing排班表的当日结束时间并计算剩余分钟数

假设我有如下名为Timing的表：

表规则如下：

• 每一行代表特定星期的一个班次
• 单日可存在多个互不重叠的班次
• 若班次跨次日，会在午夜拆分，后半段的ParentId关联前半段的Id（如示例中id=24和id=31的两条记录）

需求说明 #

需要根据当前时间查询到下一个打烊时间的剩余分钟数：

• 例1：当前时间是周一，班次从周一9:00开始跨天到周二凌晨2:00结束，所以当日结束时间是周二凌晨2:00
• 例2：示例数据中没有周三的排班，若当前是周三，下一个打烊时间是周四23:15

优先输出Linq查询语句，如果实现复杂原生SQL也可接受。

已有实现 #

当前已写代码如下：

var localTime = DateTime.Now;
var tomorrowDay = ((int)localTime.DayOfWeek + 7 + 1) % 7;

Timing lastShift = Timings.Where(x =>
          ((int)x.DayOfWeek) == tomorrowDay && x.ParentId != null)
          .SingleOrDefault(); // 要么是次日班次但起始于当日

if (lastShift != null)
{
    return Convert.ToInt32((lastShift.CloseTime - localTime.TimeOfDay).TotalMinutes);
}

lastShift = Timings
          .Where(x => x.DayOfWeek == localTime.DayOfWeek && x.CloseTime >= localTime.TimeOfDay)
          .OrderByDescending(x => x.CloseTime)
          .Take(1).SingleOrDefault();

return Convert.ToInt32((lastShift.CloseTime - localTime.TimeOfDay).TotalMinutes);

测试数据 #

@Han 提供的测试数据如下：

var Timings = new []
{
    new Timing(22, (DayOfWeek)0, new TimeSpan(9,45,0), new TimeSpan(11, 15,  0),null),
    new Timing(23, (DayOfWeek)0, new TimeSpan(13,  0,  0), new TimeSpan( 15,  0,  0), null),
    new Timing(24, (DayOfWeek)1, new TimeSpan( 9,  0,  0), new TimeSpan(23, 59, 59), null),
    new Timing(31, (DayOfWeek)2, new TimeSpan( 0,  0,  0), new TimeSpan( 2,  0,  0), 24),
    new Timing(25, (DayOfWeek)2, new TimeSpan(10,  0,  0), new TimeSpan(12,  0,  0), null),
    new Timing(26, (DayOfWeek)2, new TimeSpan(15,  0,  0), new TimeSpan(17,  0,  0), null),
    new Timing(28, (DayOfWeek)4, new TimeSpan( 9, 45,  0), new TimeSpan(23, 15,  0), null),
    new Timing(29, (DayOfWeek)5, new TimeSpan( 9, 45,  0), new TimeSpan(23, 15,  0), null),
    new Timing(30, (DayOfWeek)6, new TimeSpan( 9, 45,  0), new TimeSpan(23, 15,  0), null),
};

class Timing
{
    public int Id {get; set;}
    public DayOfWeek DayOfWeek {get; set;}
    public TimeSpan OpenTime {get; set;}
    public TimeSpan CloseTime {get; set;}
    public int? ParentId {get; set;}
    
    public Timing(int id, DayOfWeek dow, TimeSpan openTime, TimeSpan closeTime, int? parentId)
    {
        this.Id = id;
        this.DayOfWeek = dow;
        this.OpenTime = openTime;
        this.CloseTime = closeTime;
        this.ParentId = parentId;
    }
}

你现有的代码存在三个问题：

1. 取到次日的跨天班次时，直接用次日的打烊时间减当前时间会得到负数，没有补24小时的时间差
2. 没有处理当天无排班的场景（如示例的周三），会触发空引用异常
3. 没有处理当天所有班次均已结束的场景，找不到对应班次同样会报错

下面是修正后的Linq实现：

public static int GetRemainingClosingMinutes()
{
    var now = DateTime.Now;
    var currentTime = now.TimeOfDay;
    var currentDow = (int)now.DayOfWeek;

    // 先判断是否处于跨天班次的时间段内：次日有ParentId不为空的班次，且当前时间晚于前一天班次的开始时间
    var nextDayDow = (currentDow + 1) % 7;
    var crossShift = Timings.FirstOrDefault(x => (int)x.DayOfWeek == nextDayDow && x.ParentId.HasValue);
    if (crossShift != null)
    {
        var prevShift = Timings.First(x => x.Id == crossShift.ParentId.Value);
        if (currentTime >= prevShift.OpenTime)
        {
            // 跨天班次的剩余时间 = 次日打烊时间 + 24小时 - 当前时间
            return Convert.ToInt32((crossShift.CloseTime.Add(TimeSpan.FromDays(1)) - currentTime).TotalMinutes);
        }
    }

    // 找当天还没结束的班次的最晚打烊时间
    var todayLastShift = Timings
        .Where(x => (int)x.DayOfWeek == currentDow && x.CloseTime >= currentTime)
        .OrderByDescending(x => x.CloseTime)
        .FirstOrDefault();
    if (todayLastShift != null)
    {
        return Convert.ToInt32((todayLastShift.CloseTime - currentTime).TotalMinutes);
    }

    // 当天无可用班次，找后续最近的排班日的最晚打烊时间
    for (int offset = 1; offset <=7; offset++)
    {
        var targetDow = (currentDow + offset) %7;
        var dayShifts = Timings.Where(x => (int)x.DayOfWeek == targetDow).ToList();
        if (!dayShifts.Any()) continue;

        var latestClose = dayShifts.Max(x => x.CloseTime);
        var totalHours = offset * 24 + latestClose.TotalHours - currentTime.TotalHours;
        return Convert.ToInt32(totalHours * 60);
    }

    // 极端情况：无任何排班，返回-1或自定义默认值
    return -1;
}

上述代码覆盖了所有场景：跨天班次、当天有未结束班次、当天无排班找下一个排班日，所有测试用例均符合要求。

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