Find gaps in a time data table (C#, LINQ to SQL)

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.

This entry was posted in C#, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>