Linq to Entity Join table with multiple OR conditions

c# entity-framework linq linq-to-entities

Question

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.

1
12
4/8/2013 7:34:11 PM

Accepted Answer

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();
24
4/8/2013 7:37:42 PM

Popular Answer

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();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow