根据数据表中的日期范围集检查日期范围 [英] Checking date range against set of date ranges in a data table

问题描述

我需要对照数据表中的一组开始日期和结束日期检查用户输入的开始日期和结束日期，以确保没有重叠.

I need to check a user-entered start and end date against a set of start and end dates in a data table to make sure there are no overlaps.

用户使用开始日期和结束日期组合请求休息时间.我想确保这个开始日期和结束日期不包含在我从数据库读取到数据表中的一组日期中.

A user requests time off using a start and end date combination. I want to make sure this start and end date is not contained in a set of dates I have read from DB into a data table.

我使用了以下内容，但不确定是否正确.这里的表"包含用户从数据库中获取的现有休假请求，startDate和endDate是他/她正在请求的内容.数据表具有"StartDate"和"EndDate"列.

I have used the following but not sure if this is correct. Here "table" contains user's existing time off requests taken from DB, startDate and endDate is what s/he is requesting. Data table has "StartDate" and "EndDate" columns.

private DataTable FilterTable(DataTable table, DateTime startDate, DateTime endDate)
{
    var filteredRows =
        from row in table.Rows.OfType<DataRow>()
        where (DateTime)row["StartDate"] >= startDate
        where (DateTime)row["StartDate"] <= endDate
        select row;

    var filteredTable = table.Clone();
    filteredRows.ToList().ForEach(r => filteredTable.ImportRow(r));
    return filteredTable;
}

如果返回的数据表没有行，则可以，否则存在重叠.

If the returned data table has no rows, it is OK otherwise there is overlap.

推荐答案

使用扩展方法来检查日期是否在两个之间，

Using an extension method to check if a Date is between two others,

public static class DateTimeExt {
    public static bool Between(this DateTime aDate, DateTime start, DateTime end) => start <= aDate && aDate <= end;
}

您可以编写一个Overlaps方法来确定两个范围是否重叠:

You can write an Overlaps method that determines if two ranges overlap:

public static bool Overlaps(DateTime aPeriodStart, DateTime aPeriodEnd, DateTime bPeriodStart, DateTime bPeriodEnd)
    => aPeriodStart.Between(bPeriodStart, bPeriodEnd) ||
       aPeriodEnd.Between(bPeriodStart, bPeriodEnd) ||
       bPeriodStart.Between(aPeriodStart, aPeriodEnd);

现在有了另一种扩展方法，该方法将 IEnumerable< DataRow> 转换为包含行的 DataTable :

Now with another extension method that converts an IEnumerable<DataRow> to a DataTable containing the rows:

public static class IEnumerableExt {
    public static DataTable ToDataTable(this IEnumerable<DataRow> src) {
        var ans = src.First().Table.Clone();
        foreach (var r in src)
            ans.ImportRow(r);
        return ans;
    }    
}

您的最终方法很简单:

DataTable FilterTable(DataTable timeTable, DateTime startDate, DateTime endDate) =>
    timeTable.AsEnumerable().Where(period => Overlaps(period.Field<DateTime>("StartDate"), period.Field<DateTime>("EndDate"), startDate, endDate)).ToDataTable();

注意:如果不需要任何答案 DataTable ，将 .ToDataTable()替换为 .Any()会更有效.，只是让该方法返回 bool 来指示是否存在任何重叠.

NOTE: If you don't need the answering DataTable for anything, it would be more efficient to replace .ToDataTable() with .Any() and just have the method return a bool indicating if any overlaps exist.

这篇关于根据数据表中的日期范围集检查日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！