检查一个表是否包含重叠时间段
我有一个数据表有两列FromDate和ToDate ，它们是字符串格式。 我想检查我的table.ie中是否有重复的记录
From Date To Date
----------------------
9/01/2012 9/16/2012
8/23/2012 8/24/2012
8/25/2012 8/25/2012
8/5/2012 8/6/2012
8/26/2012 8/27/2012
9/15/2012 9/23/2012
该表格包含日期范围映射的重复记录
From Date To Date
----------------------
9/01/2012 9/16/2012
9/15/2012 9/23/2012
它应该返回false。
var query = from row in dt.AsEnumerable()
from row1 in dt.AsEnumerable()
where
(
(
DateTime.Parse(row1.Field<string>("fromDate")) >= DateTime.Parse(row.Field<string>("fromDate")) &&
DateTime.Parse(row1.Field<string>("fromDate")) <= DateTime.Parse(row.Field<string>("toDate"))
)
||
(
DateTime.Parse(row1.Field<string>("toDate")) >= DateTime.Parse(row.Field<string>("fromDate")) &&
DateTime.Parse(row1.Field<string>("toDate")) <= DateTime.Parse(row.Field<string>("toDate"))
)
)
select new
{
fromDate = DateTime.Parse(row1.Field<string>("fromDate")),
toDate = DateTime.Parse(row1.Field<string>("toDate"))
};
//This lst contains the dates which are overlapping
var lst = query.Distinct().ToList();
好，那么一个自我加入会在这里帮助：
我有一个小班TimePeriod，只是为了满足您的需求
public class TimePeriod
{
public int Id;
public DateTime FromDate { get; set; }
public DateTime ToDate { get; set; }
public static DateTime Parse(string date)
{
var dt = DateTime.Parse(date,
CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.RoundtripKind);
return dt;
}
}
那么我有一些TestData
var list = new List（）;
list.Add(new TimePeriod() { Id = 1, FromDate = TimePeriod.Parse("9/01/2012"), ToDate = TimePeriod.Parse("9/16/2012") });
list.Add(new TimePeriod() { Id = 2, FromDate = TimePeriod.Parse("8/23/2012"), ToDate = TimePeriod.Parse("8/24/2012") });
list.Add(new TimePeriod() { Id = 3, FromDate = TimePeriod.Parse("8/25/2012"), ToDate = TimePeriod.Parse("8/25/2012") });
list.Add(new TimePeriod() { Id = 4, FromDate = TimePeriod.Parse("8/5/2012"), ToDate = TimePeriod.Parse("8/6/2012") });
list.Add(new TimePeriod() { Id = 5, FromDate = TimePeriod.Parse("8/26/2012"), ToDate = TimePeriod.Parse("8/27/2012") });
list.Add(new TimePeriod() { Id = 6, FromDate = TimePeriod.Parse("9/15/2012"), ToDate = TimePeriod.Parse("9/23/2012") });
这里是解决方案:(与OraNob的一些灵感，谢谢你）
var overlaps = from current in list
from compare in list
where
(
(compare.FromDate > current.FromDate &&
compare.FromDate < current.ToDate) ||
(compare.ToDate > current.FromDate &&
compare.ToDate < current.ToDate)
)
select new
{
Id1 = current.Id,
Id2 = compare.Id,
};
也许你想省略第二个ID（因为你会在这里重复（1/6）和（6/1）
按ToDate ， FromDate排序（或者在您的DataTable中构建一个有序索引数组）。 从行或数组位置＃2循环到结尾，并查看FromDate <=是否为前一个项目的ToDate。 将重叠的项目放入列表中。 任务完成。
您还可以按FromDate ， ToDate排序并执行类似的逻辑。