How can I use a compound condition in a join in Linq?

Question

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?

Isn't an INNER JOIN just a different way of writing a WHERE? — leppie
– leppie, Commented Mar 15, 2010 at 13:50

Craig Stuntz · Accepted Answer · 2010-03-15 14:15:34Z

4

It's rarely correct to use join in LINQ to SQL.

Since you want contacts, why not start your selection there? Presuming the association between Customer and Contact is two-way, you should be able to write something like:

IEnumerable<Guid> customerIds = // ... var q = from contact in Context.Contacts where customerIds.Contains(contact.Customer.Id) select contact;

answered Mar 15, 2010 at 14:15

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

leppie Over a year ago

You could do it all in one shot too, if you want to. (+1 for link text, fully agree with you there)

Gary McGill Over a year ago

@Craig: this is yet another case where in trying to simplify my question and take it out of my problem domain, I've lost some of the nuances of my situation. I don't think I can come at it the other way, no. But, I take your point about not using join at all.

leppie · Accepted Answer · 2010-03-15 13:51:56Z

1

Use anonymous classes. EG

new { A.Foo, B.Bar } equals new { Foo = B.Baz, Bar = C.Ork }

answered Mar 15, 2010 at 13:51

leppie

118k18 gold badges201 silver badges300 bronze badges

1 Comment

Gary McGill Over a year ago

Sorry, I think you've missed the point of the question. That's still an equality test - how can I do condition1 OR condition2?

Collectives™ on Stack Overflow

How can I use a compound condition in a join in Linq?

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related