Join on Condition With Linq Methods

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?

Jon Skeet
people
quotationmark

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.

people

See more on this question at Stackoverflow