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

Question

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?

Answer 1

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.

Answer 2

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 }