LINQ - How to query a range of effective dates that only has start dates

Question

I'm using C# 3.5 and EntityFramework. I have a list of items in the database that contain interest rates. Unfortunately this list only contains the Effective Start Date. I need to query this list for all items within a range.

However, I can't see a way to do this without querying the database twice. (Although I'm wondering if delayed execution with EntityFramework is making only one call.) Regardless, I'm wondering if I can do this without using my context twice.

internal IQueryable<Interest> GetInterests(DateTime startDate, DateTime endDate)  {
    var FirstDate = Context.All().Where(x => x.START_DATE < startDate).Max(x => x.START_DATE);
    IQueryable<Interest> listOfItems = Context.All().Where(x => x.START_DATE >= FirstDate && x.START_DATE <= endDate);
    return listOfItems;
}

Answer 1

Hey,

If you could use a LINQ query, you can use let to do this:

(from c in dbContext.Table
let firstdate = dbContext.Table.Max(i => c.StartDate < startDate)
where c.StartDate >= firstdate
and c.StartDate <= enddate
select c)

I'm not sure if the max will work this way, so you may need to alternatively do:

(from c in dbContext.Table
let firstdate = dbContext.Table.Select(i => i.StartDate).Max(i => c.StartDate < i)
where c.StartDate >= firstdate
and c.StartDate <= enddate
select c)

Something like that.

Answer 2

I haven't tried this on EF but on Linq to objects it works fine:

var result = source
  .OrderBy(x => x.start)
  .GroupBy(x => x.start < startDate)
  .SelectMany((x, i) => i == 0 ? new[] {new { value = x.Last().value, start = x.Last().start }} : x.Where(y => y.start < endDate));

The issue is that C# LINQ is missing an operator which gives you access to the previous item in a sequence. F# apparently can handle this. Workarounds involved either a GroupBy or an Aggregate operation. In this case, GroupBy can handle it.

It's not pretty and I wouldn't recommend using it over the two phase approach.