In the previous post I showed a T-SQL solution for finding gaps in a time data table, and in this post I will show the same solution but this time it will be with C# and LINQ to SQL. Here is the code that produces the same result as in the previous post:
var context = new DataClassesDataContext();
var query = from a in context.dtTables
where context.dtTables.Count(date => date.dt == a.dt.AddMinutes(1)) == 0 &&
a.dt < context.dtTables.Max(date => date.dt)
select new
{
startGap = a.dt,
endGap = (from b in context.dtTables
where b.dt > a.dt
select b).Min(c => c.dt)
};
Console.WriteLine("StartGap EndGap");
Console.WriteLine("-------------------------- -------------------------");
foreach (var item in query)
Console.WriteLine(item.startGap + " " + item.endGap);
and here is the result after executing the above code:
Update: This post shows an optimized version of this Linq query.
