CODEWITHSUNDEEP
c#linqsql-server-2008
user3830047
user3830047

Reputation: 13

LINQ Left Join 3 tables with OR operator

I have this SQL query:

SELECT * FROM [CMS_MVC_PREPROD].[dbo].[T_MEMBER] m  
left outer join [CMS_MVC_PREPROD].[dbo].[T_COMPANY] c
ON m.Company_Id = c.Id
left outer join [CMS_MVC_PREPROD].[dbo].[T_ADRESSE] a  
ON m.Id = a.Member_Id OR a.Company_Id = c.Id

But i could not translate it to Linq

I have some trouble with this line in particular:

ON m.Id = a.Member_Id OR a.Company_Id = c.Id

EDIT: I try this query from Ehsan Sajjad

from m in db.Member
join c in db.T_Company on m.Company_Id equals c.Id into a
from c in a.DefaultIfEmpty()
from ta in db.T_Address
where m.Id == ta.Member_Id || ta.Company_Id == c.Id

But it only returns members who have an address and i want all members. Maybe it will work with a full join

Thanks in advance

Upvotes: 0

Views: 449

Answers (2)

usr
usr

Reputation: 171226

LINQ only supports equi-joins directly. You'll have to use a different query pattern:

from m in db.MEMBER
//add join to "c" here
from a in (
     from a db.ADRESSE
     where m.Id == a.Member_Id || a.Company_Id == c.Id
     select a).DefaultIfEmpty()
select new { m, a }

Just a sketch. The trick is using DefaultIfEmpty on a subquery. Both L2S and EF can translate this to an OUTER APPLY which is equivalent to a LEFT JOIN.

Upvotes: 1

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62498

You can write this way to make it work:

from m in db.Member
join c in db.T_Company on m.Company_Id equals c.Id into a
from c in a.DefaultIfEmpty()
from ta in db.T_Address
where m.Id == ta.Member_Id || ta.Company_Id == c.Id

Upvotes: 0

Related Questions