Linq-Entities: Fetch data excluding the overlapping data ranges, pick the largest period

I have 2 tables, Imports and Periods.

Imports has the following structure:

AdminID, PeriodID, Some more fields
1,       1
1,       2
1,       6
1,       50

Periods table has the following structure:

PeriodID, PeriodType, StartDate,   EndDate,    Description
1,        1,          2007-01-01,  2007-12-31, Year 2007
2,        2,          2007-01-01,  2007-03-31, Quarter 1 2007
3,        2,          2007-04-01,  2007-06-30, Quarter 2 2007
4,        2,          2007-07-01,  2007-09-30, Quarter 3 2007
5,        2,          2007-10-01,  2007-12-31, Quarter 4 2007
6,        3,          2007-01-01,  2007-01-31, January 2007
.
.
.
50,       2,          2011-01-01,  2011-03-31, Quarter 1 2011

Now, I need to build a linq query to fetch only the largest period(ignoring the smaller overlapping periods) based on the data in Imports table!

When I query for AdminID = 1, I should only get PeriodID 开发者_如何学Python= 1 & 50, ignoring/excluding the PeriodIDs 2 & 6 as they overlap in 1 and 50 as there is no overlapping data yet!

You, can the max help for picking the largest period and while retrieving the values by comparing the PeriodIDs in both tables right.

I'm not sure whether there is a convenient way to do this in the database, but when you pull the data locally, you can do in-memory LINQ queries, if this is appropriate. You need to do this in thee steps.

Step 1: Define a Range class that allows you to do comparisons on periods (see below).

Step 2: Pulling the periods from the database:

var ranges = (
    from period in context.Periods
    where period.Imports.Any(i => i.AdminID == adminId)
    select new Range(period.StartDate, period.EndDate.AddDays(1)))
    .ToArray();

Note the .ToArray() to pull everything locally.

Step 3: Aggregating / merging all the periods into a list of non-overlapping periods:

var mergedPeriods = (
    from range in ranges
    select ranges.Where(p => p.OverlapsWith(range))
        .Aggregate((r1, r2) => r1.Merge(r2)))
    .Distinct();

For this to work you need a specially designed Range type that contains OverlapsWith, Merge and Equals methods. It might look like this:

public class Range : IEquatable<Range>
{
    public Range(DateTime start, DateTime exclusiveEnd)
    {
        if (exclusiveEnd < start)
            throw new ArgumentException();

        this.Start = start; this.End = exclusiveEnd;
    }

    public DateTime Start { get; private set; }
    public DateTime End { get; private set; }
    public TimeSpan Duration { get { return this.End - this.Start; } }

    public Range Merge(Range other)
    {
        if (!this.OverlapsWith(other)) throw new ArgumentException();

        var start = this.Start < other.Start ? this.Start : other.Start;
        var end = this.End > other.End ? this.End : other.End;

        return new Range(start, end);
    }

    public bool Contains(Range other)
    {
        return this.Start <= other.Start && this.End > other.End;
    }

    public bool OverlapsWith(Range other)
    {
        return this.OverlapsOnStartWith(other) ||
            other.OverlapsOnStartWith(this) ||
            this.Contains(other) ||
            other.Contains(this);
    }

    private bool OverlapsOnStartWith(Range other)
    {
        return this.Start >= other.Start && this.Start < other.End;
    }

    public bool Equals(Range other)
    {
        return this.Start == other.Start && this.End == other.End;
    }
}

I hope this helps.

Well, after a long struggle, I did find an answer! With a single query to database! And for everyone's benefit posting the same.

var oImportPeriods = 
    from o in Imports
    where o.Administration.AdminID == 143
    orderby o.Period.PeriodID
    select o.Period;

var oIgnorePeriodList = (
    from oPeriod in oImportPeriods
    from oSearchPeriod in oImportPeriods
        .Where(o => o.PeriodID != oPeriod.PeriodID)
    where oPeriod.StartDate >= oSearchPeriod.StartDate
    where oPeriod.EndDate <= oSearchPeriod.EndDate
    select oPeriod.PeriodID)
    .Distinct();

var oDeletablePeriods = oAdministrationPeriods
    .Where(o => !oIgnorePeriodList.Contains(o.PeriodID));   

foreach(var o in oDeletablePeriods)
    Console.WriteLine(o.Name);