如何获取跨越多天的时间表中每天的结束时间

Question

假设我有以下名为 Timing 的表格：

显然，每一行代表特定日期的班次。

一天可以有多个不重叠的班次。

如果一个班次跨越到下一天，它将在午夜被分割，第二部分将具有第一部分的父ID（如第24和31行所示）。

我想查询距离我的一天结束还有多少分钟（下一个关闭时间）。

例如，如果我在第1天，我的一天在第2天凌晨2:00结束（因为班次从第1天9:00开始，到第2天2:00结束）。

如果有间隔（例如周末等），我必须小心。请注意，没有第3天，因此下一个关闭时间将是第4天23:15（假设您在第3天）。

我主要寻找Linq查询（Timing.Where（x => x.close_time <....等等）。

但我认为这可能非常复杂，所以我可以使用原始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(); // Either it is tomorrow but starts today.

    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;
    }
}

- Nour

---

@Han 没问题，谢谢你的帮助。 - Nour

Answer 1

我建议您使用自连接来获取下一天的关闭时间。我假设每行都有零个或一个子行。我不使用表格，而是使用数组，但查询应该是相同的。我在LINQPad中编写代码。

void Main()
{
    var Timings = new []
    {
        new Timing(22, 0, new DateTime(2021,  9, 12,  9, 45,  0), new DateTime(2021,  9, 12, 11, 15,  0), null),
        new Timing(23, 0, new DateTime(2021,  9, 12, 13,  0,  0), new DateTime(2021,  9, 12, 15,  0,  0), null),
        new Timing(24, 1, new DateTime(2021,  9, 13,  9,  0,  0), new DateTime(2021,  9, 13, 23, 59, 59), null),
        new Timing(31, 2, new DateTime(2021,  9, 14,  0,  0,  0), new DateTime(2021,  9, 14,  2,  0,  0), 24),
        new Timing(25, 2, new DateTime(2021,  9, 14, 10,  0,  0), new DateTime(2021,  9, 14, 12,  0,  0), null),
        new Timing(26, 2, new DateTime(2021,  9, 14, 15,  0,  0), new DateTime(2021,  9, 14, 17,  0,  0), null),
        new Timing(28, 4, new DateTime(2021,  9, 16,  9, 45,  0), new DateTime(2021,  9, 16, 23, 15,  0), null),
        new Timing(29, 5, new DateTime(2021,  9, 17,  9, 45,  0), new DateTime(2021,  9, 17, 23, 15,  0), null),
        new Timing(30, 6, new DateTime(2021,  9, 18,  9, 45,  0), new DateTime(2021,  9, 18, 23, 15,  0), null),
    };
    
    var timingGroupedWithChildren = (
        from t1 in Timings.Where(x => x.ParentId == null) // parent rows only
        join t2 in Timings.Where(x => x.ParentId != null) // childr rows only
            on t1.Id equals t2.ParentId // left join parent's Id with child's ParentId
            into nextDay
        select new {t1, nextDay})
        .Dump() //unremark this line to get show the result in LINQPad
        ;
}

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

计时分组与子项的格式如下： 请注意，只有id = 24具有nextDay，其他行没有nextDay。有8个项目（显示在左上角），但只有23和24号ID显示详细信息（其他行被折叠以节省空间，因为我的屏幕不够大）。

现在很容易得到第二天的闭店时间。第一种方法是这样的。

void Main()
{
    var Timings = new []
    {
        new Timing(22, 0, new DateTime(2021,  9, 12,  9, 45,  0), new DateTime(2021,  9, 12, 11, 15,  0), null),
        new Timing(23, 0, new DateTime(2021,  9, 12, 13,  0,  0), new DateTime(2021,  9, 12, 15,  0,  0), null),
        new Timing(24, 1, new DateTime(2021,  9, 13,  9,  0,  0), new DateTime(2021,  9, 13, 23, 59, 59), null),
        new Timing(31, 2, new DateTime(2021,  9, 14,  0,  0,  0), new DateTime(2021,  9, 14,  2,  0,  0), 24),
        new Timing(25, 2, new DateTime(2021,  9, 14, 10,  0,  0), new DateTime(2021,  9, 14, 12,  0,  0), null),
        new Timing(26, 2, new DateTime(2021,  9, 14, 15,  0,  0), new DateTime(2021,  9, 14, 17,  0,  0), null),
        new Timing(28, 4, new DateTime(2021,  9, 16,  9, 45,  0), new DateTime(2021,  9, 16, 23, 15,  0), null),
        new Timing(29, 5, new DateTime(2021,  9, 17,  9, 45,  0), new DateTime(2021,  9, 17, 23, 15,  0), null),
        new Timing(30, 6, new DateTime(2021,  9, 18,  9, 45,  0), new DateTime(2021,  9, 18, 23, 15,  0), null),
    };
    
    var timingGroupedWithChildren = (
        from t1 in Timings.Where(x => x.ParentId == null) // parent rows only
        join t2 in Timings.Where(x => x.ParentId != null) // childr rows only
            on t1.Id equals t2.ParentId // left join parent's Id with child's ParentId
            into nextDay
        select new {
            t1.Id,
            t1.DayOfWeek,
            t1.OpenTime,
            // if current row's next day is null, then use current row's CloseTime
            // otherwise use next day's CloseTime
            CloseTime = nextDay.Where(x => x.ParentId == t1.Id).Count() == 0 ? t1.CloseTime : nextDay.Where(x => x.ParentId == t1.Id).Single().CloseTime
        })
        //.Dump() //unremark this line to get show the result in LINQPad
        ;
    
    var myShift = timingGroupedWithChildren.Where(x => x.Id == 24).Single();
    var myWorkingHours = (myShift.CloseTime - myShift.OpenTime).TotalHours;
    Console.WriteLine($"Working hours = {myWorkingHours}");
}

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

您可以在下面的图片中看到，如果当前行有子项，我会替换关闭日期。但是我没有使用实际的数据库测试这个查询（我正在使用一个数组），而且我不喜欢调用nextDay.Where(x => ...) .Count()两次，因为LINQ中的一些方法（例如Count()）会迭代所有行。虽然它被Where(x => ...)过滤了，但是除非我看到调用此查询时执行的实际SQL语句，否则我无法说任何事情。如果您在SQL Management Studio中打开SQL Profiler或使用LINQPad SQL转换，则可以查看实际语句。按钮在图片顶部（结果lambda符号SQL IL树）上。

另一种方法是获取子行后，从SQL中进行Count()操作。

void Main()
{
    var Timings = new []
    {
        new Timing(22, 0, new DateTime(2021,  9, 12,  9, 45,  0), new DateTime(2021,  9, 12, 11, 15,  0), null),
        new Timing(23, 0, new DateTime(2021,  9, 12, 13,  0,  0), new DateTime(2021,  9, 12, 15,  0,  0), null),
        new Timing(24, 1, new DateTime(2021,  9, 13,  9,  0,  0), new DateTime(2021,  9, 13, 23, 59, 59), null),
        new Timing(31, 2, new DateTime(2021,  9, 14,  0,  0,  0), new DateTime(2021,  9, 14,  2,  0,  0), 24),
        new Timing(25, 2, new DateTime(2021,  9, 14, 10,  0,  0), new DateTime(2021,  9, 14, 12,  0,  0), null),
        new Timing(26, 2, new DateTime(2021,  9, 14, 15,  0,  0), new DateTime(2021,  9, 14, 17,  0,  0), null),
        new Timing(28, 4, new DateTime(2021,  9, 16,  9, 45,  0), new DateTime(2021,  9, 16, 23, 15,  0), null),
        new Timing(29, 5, new DateTime(2021,  9, 17,  9, 45,  0), new DateTime(2021,  9, 17, 23, 15,  0), null),
        new Timing(30, 6, new DateTime(2021,  9, 18,  9, 45,  0), new DateTime(2021,  9, 18, 23, 15,  0), null),
    };
    
    var timingGroupedWithChildren = (
        from t1 in Timings.Where(x => x.ParentId == null) // parent rows only
        join t2 in Timings.Where(x => x.ParentId != null) // childr rows only
            on t1.Id equals t2.ParentId // left join parent's Id with child's ParentId
            into nextDay
        select new {
            t1.Id,
            t1.DayOfWeek,
            t1.OpenTime,
            t1.CloseTime,
            NextDay = nextDay
        })
        //.Dump() //unremark this line to get show the result in LINQPad
        ;
        
    var myShift = timingGroupedWithChildren.Where(x => x.Id == 24).Single();
    var myWorkingHours = ((myShift.NextDay.Count() == 0 ? myShift.CloseTime : myShift.NextDay.Single().CloseTime) - myShift.OpenTime).TotalHours;
    Console.WriteLine($"Working hours = {myWorkingHours}");
}

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

您可以看到，只有ID为24的行具有NextDay（如图1所示）。

- Han

---

首先，让我非常感谢您走到这一步，并提供所有这些片段、实验、截图和详细说明来帮助一个陌生人。我觉得说谢谢还不够，所以我要开始一个悬赏并授予给您。 - Nour

其次，我不太确定，但我觉得我们在依赖班次的顺序，以防同一天有多个班次。我认为我可以给一天中的班次添加 OrderBy，并选择最后一个（如果没有孩子出现）。 - Nour

@Nour，我用当前行Id =任意行ParentId离开了自连接。DoW 2有3行，但它指向正确的ParentId。因此，Id 24是Id 31的父项。 - Han

没问题。我需要做什么吗？ - Han

让我们在聊天中继续这个讨论。 - Han