Join on Condition With Linq Methods

Question

Say I have a class

Class Record
{
   int Id
   int StartDate
}

Class DBRecord
{
   int Id
   DateTime StartDate
   DateTime EndDate
}

How could I join these using the linq methods syntax with a condition that the start date is between the dbrecords start and end date? I tried like this but no luck:

this.Records().Join(context.DBRecords,
          x=> new { x.Id, x.StartDate},
         (x, y) => { x.Id == y.Id, x.StartDate > y.StartDate && x.startDate < x.endDate  },
         (x,y) => y);

Does anyone know how to do this?

Answer 1

The Join method is for an equijoin - not an arbitrary condition.

You might want to just use SelectMany to get a "full join" and then Where:

this.Records()
    .SelectMany(_ => context.DBRecords, (x, y) => new { x, y })
    .Where(z => { z.x.Id == z.y.Id &&
                  z.x.StartDate > z.y.StartDate && 
                  z.x.StartDate < z.y.EndDate  })
    .Select(z => z.y)

Or you could to an inner join based on just ID, and then filter:

this.Records()
    .Join(context.DBRecords, x => x.Id, y => y.Id, (x, y) => new {x, y})
    .Where(z => { z.x.StartDate > z.y.StartDate && 
                  z.x.StartDate < z.y.EndDate  })
    .Select(z => z.y)

Note that both of these would be easier to understand using query expressions - almost every case where you've got two source sequences (calls to SelectMany and Join) are simpler to understand with query expressions due to transparent identifiers.