How can I join conditionally in LINQ queries?

Question

If I have two tables; Drivers keyed by DriverId and Trips with foreign keys DriverId and CoDriverId, and I want to find all trips where a driver was either the driver or co-driver I could code this in Transact-SQL as

select d.DriverId, t.TripId 
from Trips t inner join Drivers d 
on t.DriverId = d.DriverId or t.CoDriverId = d.DriverId

How could this be coded as a LINQ query?

Answer 1

You can't do it with a LINQ join, basically - LINQ only directly supports equijoins.

You can however do:

var query = from trip in db.Trips
            from driver in db.Drivers
            where trip.DriverId == driver.DriverId ||
                  trip.CoDriverId == driver.DriverId
            select new { driver.DriverId, trip.TripId };

That may well end up with the same join in the converted SQL.