Get periods without cover by another periods

Question

I Have Db Entity which looks like:

TimeEntity {
 public DateFrom {get; set;}
 public DateTo {get; set;}
}

There are activities which are one by one but can be situation when will be breaks. How to get periods when there is no activity? How to do it by linq? Do I must get all activities from db and do it myself?

Thank You!

Answer 1

This query should return gaps.

var withoutAdjacent = 
    from p in ctx.Periods
    where !ctx.Periods.Any(p2 => p2.DateFrom == p.DateTo) && ctx.Periods.Any(p2 => p2.DateFrom > p.DateTo)
    select p;

var gapPeriods =
    from p in withoutAdjacent
    select new TimeEntity
    {
        DateFrom = p.DateTo,
        DateTo = ctx.Periods.Where(p2 => p2.DateFrom > p.DateTo).Min(p2 => DateFrom)
    };