How can I use a compound condition in a join in Linq?
- by Gary McGill
Let's say I have a Customer table which has a PrimaryContactId field and a SecondaryContactId field. Both of these are foreign keys that reference the Contact table. For any given customer, either one or two contacts may be stored. In other words, PrimaryContactId can never be NULL, but SecondaryContactId can be NULL.
If I drop my Customer and Contact tables onto the "Linq to SQL Classes" design surface, the class builder will spot the two FK relationships from the Customer table to the Contact table, and so the generated Customer class will have a Contact field and a Contact1 field (which I can rename to PrimaryContact and SecondaryContact to avoid confusion).
Now suppose that I want to get details of all the contacts for a given set of customers.
If there was always exactly one contact then I could write something like:
from customer in customers
join contact in contacts on customer.PrimaryContactId equals contact.id
select ...
...which would be translated into something like:
SELECT ...
FROM Customer
INNER JOIN Contact
ON Customer.FirstSalesPersonId = Contact.id
But, because I want to join on both the contact fields, I want the SQL to look something like:
SELECT ...
FROM Customer
INNER JOIN Contact
ON Customer.FirstSalesPersonId = Contact.id OR Customer.SecondSalesPersonId = Contact.id
How can I write a Linq expression to do that?
