LEFT JOIN with multiple conditions that use OR

[ivanlemus]

Does LINQ allow a LEFT JOIN with multiple conditions that use OR. Something like this (see 2nd left join):

sql:

select Distinct
Product_Name, cast(op.Product_Id as bigint) Product_Id,
cast(Opportunity__Product_Id as bigint) Opportunity__Product_Id,
case when od1.Order__Detail_Id is null then 'false' else 'true' end OrderDetail
from vCrm_Opportunity_Product op
left outer join vCrm_Order o1 on o1.Opportunity_ID = op.Opportunity_Id
left outer join vCRM_Order__Detail od1 on o1.Order__Id = od1.Order_Id
and (od1.Product_Id = op.Product_Id or od1.Package_ID = op.package_id)
where
Order__Id = 28088

linqer gives:

(from op in ASGWebDBContext.vCrm_Opportunity_Products
join o1 in ASGWebDBContext.vCrm_Orders on op.Opportunity_Id equals o1.Opportunity_Id into o1_join
from o1 in o1_join.DefaultIfEmpty()
join od1 in ASGWebDBContext.vCRM_Order__Details
on new { o1.Order__Id, op.Product_Id, Package_ID = op.package_id }
equals new { Order__Id = od1.Order_Id, od1.Product_Id, od1.Package_ID } into od1_join
from od1 in od1_join.DefaultIfEmpty()
where
Convert.ToInt64(o1.Order__Id) == 28088
select new {
op.Product_Name,
Product_Id = (Int64?)Convert.ToInt64(op.Product_Id),
Opportunity__Product_Id = (Int64?)Convert.ToInt64(op.Opportunity__Product_Id),
OrderDetail =
od1.Order__Detail_Id == null ? "false" : "true"
}).Distinct()

this returns WRONG results (sql and linq results don't match).

All the info that I've found for LEFT JOIN with multiple conditions use AND (i believe that's what linqer did)...

Thanks for all your help,

Cheers,

Ivan
[e-mail]

[Mikhail Oumantsev]

LINQ doesn't allow a LEFT JOIN with conditions that use OR.
Linqer shouldn't not allow such type of conversion.
This fix will be included into the next update.
Thank you.

[stewy]

I guess LINQER doesn't allow WITH AS either.

[Mikhail Oumantsev]

No, it doesn't.
The SQL statement has to start from SELECT operator.