Join a table using on multiple columns using a OR syntax [duplicate]

Question

So what I'm trying to do is :-

SELECT * FROM TableA
JOIN TableB ON TableA.OriginPhoneNumber=TableB.Id OR TableA.DestinationPhoneNumber=TableB.Id

Rather strange query I know! But I'm trying to replicate this in EntityFramework/Linq - looking at all the samples I can see a pretty easy way to do it when the join is using an AND (using anonymous types) but does the same result exist for a OR join?

Answer 1

Just do a cross join with a where clause

var results = from a in db.TableA
              from b in db.TableB
              where a.OriginPhonenumber == b.Id 
                    || a.DestinationPhoneNumber == b.Id
              select new { A = a, B = b };

It's doubtful that an or in a join condition would be more efficient than this, but it's likely that either would result in the same execution plan. Performance aside it will give the same results.