c# - 如何在跨越多天的时间安排中获得一天的关闭时间

Question

假设我有下表名为 计时 :

显然，每一行代表特定日期的一个类次。
一天可以有不重叠的多个类次。
如果轮类跨越第二天，它将在午夜拆分，下半场将有上半场的父 ID(如第 24 行和第 31 行所示)
我想查询离我的一天结束(下一个关闭时间)还有多少分钟。
例如，如果我在第一天，我的一天结束于 第 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;
    }
}

Answer 1

我建议离开自己加入你的 table 以获得第二天的关闭时间。我假设 每行有零个或一个子行 .我不使用表而是使用数组，但查询应该是相同的。我在 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;
    }
}

TimingGroupedWithChildren 看起来像这样:

请注意，只有 id = 24 有 nextDay，其他行没有 nextDay。有 8 个项目(显示在左上角)，但只有 Id 23 和 24 详细显示(其他行折叠以节省空间，因为我的屏幕不够大)。
现在很容易获得第二天的关闭时间。第一种方法是这样的。

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

您可以在下面的图片中看到，如果当前行有 child ，我会替换关闭日。但是我不使用实际数据库测试此查询(我使用的是数组)，并且我不喜欢调用 nextDay.Where(x => ...).Count() 两次，因为 LINQ 中的某些方法，例如. Count()，迭代所有行。它是用 Where(x => ...) 过滤的，但除非我看到调用此查询时执行的实际 SQL 语句，否则我什么也说不出来。如果在 SQL Management Studio 中打开 SQL Profiler 或使用 LINQPad SQL 翻译，则可以看到实际语句。该按钮位于图片的顶部(Result 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)。