C# LINQ-TO-SQL OR-Statement in JOIN

How can I make the following SQL a Linq query:

SELECT * FROM ORDERS
INNER JOIN ITEMS
ON ORDERS.ID = ITEMS.ORDER_A OR ORDERS.ID = ITEMS.ORDER_B

I would think it would be:

from o in orders
join i in items
on o.ID equals i.OrderA or o.ID equals i.OrderB
select new { Order = o, Item = i }

I'm guessing the compiler wants something else. How do I write this statement?

From stackoverflow

• You have to make two joins

  from o in orders
  join iA in items on o.ID equals iA.OrderA 
  join iB in items on o.ID equals iB.OrderB
  set i = (iA == null ? iB : iA)
  select new { Order = o, Item = i }
  

  Didn't actually try compile this, but basically this is what you have to do, two different joins that you pick before it is being selected out to the array.

  Stan R. : why not iA ?? iB

  Robert Harvey : That would return records for only orders that have a corresponding record in both the OrderA and OrderB tables. You need two left joins for that inline if to work.

  Jonathan.Peppers : I think this works, my final query is pretty complicated, I already have 3 joins and have to outer join on top of that... I wish they had structured Linq more like SQL.

• What you would want to use is http://msdn.microsoft.com/en-us/library/bb549267.aspx, unfortunately I don't think that will translate very well to SQL and can only be used as an extension method.

  Though perhaps you can use a cross-join and filter it in the where condition? I would verify the generated SQL first though before using this method.

  from o in orders
  from i in items
  where o.ID == i.OrderA || o.ID == i.OrderB
  select new { Order = o, Item = i }
  

  Jonathan.Peppers : Your code does not work, I tried that as well. You have to do 2 joins.

  KeeperOfTheSoul : Is it a problem with the SQL or the LINQ? As that ran fine when I tried it in LINQPad against an object collection.