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);