Linq to Entity Join table with multiple OR conditions
I need to write a Linq-Entity state that can get the below SQL query
SELECT RR.OrderId
FROM dbo.TableOne RR
JOIN dbo.TableTwo M ON RR.OrderedProductId = M.ProductID OR RR.SoldProductId= M.ProductID
WHERE RR.StatusID IN ( 1, 4, 5, 6, 7 )
I am stuck with the below syntax
int[] statusIds = new int[] { 1, 4, 5, 6, 7 };
using (Entities context = new Entities())
{
var query = (from RR in context.TableOne
join M in context.TableTwo on new { RR.OrderedProductId, RR.SoldProductId} equals new { M.ProductID }
where RR.CustomerID == CustomerID
&& statusIds.Any(x => x.Equals(RR.StatusID.Value))
select RR.OrderId).ToArray();
}
this gives me below error
Error 50 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.
How can I do a Multiple condition join for a table.
You don't have to use the join syntax. Adding the predicates in a where
clause has the same effect and you can add more conditions:
var query = (from RR in context.TableOne
from M in context.TableTwo
where RR.OrderedProductId == M.ProductID
|| RR.SoldProductId == M.ProductID // Your join
where RR.CustomerID == CustomerID
&& statusIds.Any(x => x.Equals(RR.StatusID.Value))
select RR.OrderId).ToArray();
Change your query syntax from using join
to using an additional from
clause
var query = (from RR in context.TableOne
from M in context.TableTwo.Where(x => x.ProductID == RR.OrderedProductId || x.ProductID == RR.SoldProductId)
where statusIds.Any(x => x.Equals(RR.StatusID.Value))
select RR.OrderId).ToArray();
Multiple Joins :
var query = (from RR in context.TableOne
join M in context.TableTwo on new { oId = RR.OrderedProductId, sId = RR.SoldProductId} equals new { oId = M.ProductID, sId = M.ProductID }
where RR.CustomerID == CustomerID
&& statusIds.Any(x => x.Equals(RR.StatusID.Value))
select RR.OrderId).ToArray();