LInq left join with multiple condition in on clause

Question

I have a join in SQL I need to write it in linq here is the select statment:

select * from products p
left join customer cust
on((cust.identifier = p.orgID or cust.identifier = p.personalno) and cust.identifier is not null);

How can I write this statement in linq?

Answer 1

• If you really want to do it with a JOIN, you could do it like this:

  from cust in customer
  join _p1 in products on cust.identifier equals _p1.orgID into _p1
  from p1 in _p1.DefaultIfEmpty()
  join _p2 in products on cust.identifier equals _p2.personalno into _p2
  from p2 in _p2.DefaultIfEmpty()
  where cust.identifier != null
  && (p1 != null || p2 != null)
  select new { Customer = cust, Product = p1 == null ? p2 : p1 }
  

• An easier solution without the JOINkeyword would be:

  from p in products
  from cust.Where(q => q.identifier != null && (p.orgID == q.identifier || p.personalno == q.identifier)).DefaultIfEmpty()
  where cust != null
  select new { Product = p, Customer = cust }
  

• To answer the question, that the headline suggests, do the following to do a join on multiple conditions, but be aware, that this only works for AND conditions and not for OR conditions:

  from t1 in Table1
  join t2 in Table2 on new { Criteria1 = t1.criteria1, Criteria2 = t1.criteria2 } equals new { Criteria1 = t2.criteria1, Criteria2 = t2.criteria2 }
  select new { T1 = t1, T2 = t2 }