Linq: Date less that x, but next record date is greater that x

I got records in database. Each record has field "Date". For given date=x i need to find records that Date value is less that x, but next record date is greater of x.

Example:

id  date
--------------
1   12.03.2013
2   15.03.2013
3   18.03.2013

now, I got X=16.03.2013, and i need a LINQ that return this record:

 2  15.03.2013

BUT! for the X=15.03.2014 it should return nothing (because there is record with smaller date, but next record has exactly the same date as X)

How can i do this?

Jon Skeet
people
quotationmark

The simplest approach IMO is just to find the record that it would find, and check the date afterwards:

var result = db.Table
               .Where(x => x.Date <= target.Date)
               .OrderByDescending(x => x.Date)
               .FirstOrDefault();
if (result != null && result.Date == target.Date)
{
    result = null;
}

Or you could do it all in the query using a secondary Where clause after filtering to a single result:

var result = db.Table
               .Where(x => x.Date <= target.Date)
               .OrderByDescending(x => x.Date)
               .Take(1)
               .Where(x => x.Date != target.Date)
               .FirstOrDefault();

Note that this doesn't work if all values are less than x (so there's no "next" record at all). I haven't yet worked out a way to handle that.

people

See more on this question at Stackoverflow