LEFT JOIN with OR condition in LINQ

Question

I need to write following T-SQL in LINQ:

SELECT T1.ID, T2.Name
FROM T1
LEFT JOIN T2 ON (T1.ID = I2.ID1 OR T1.ID = T2.ID2)

An OR-join would look like this in LINQ:

T1.Join(T2, t1=>new{}, t2=>new{}, (t1,t2)=>new{ID=t1.Id, t2=t2}).Where(o=>o.Id == o.t2.Id1 || o.Id==o.t2.Id2);

But that query is an INNER JOIN, not a LEFT JOIN. Some kind of LEFT JOIN would look like this:

T1.GroupJoin(T2, t1 => t1.Id, t2 => t2.Id1, (t1, t2) => new { Id = t1.Id, Name1 = t2.Select(t => t.Name) }).DefaultIfEmpty()
  .GroupJoin(T2, o => o.Id, t2 => t2.Id2, (i, j) => new { Id = i.Id, Name1 = i.Name1, Name2 = j.Select(t => t.Name) }).DefaultIfEmpty();

This query produces correct results, but makes 2 joins instead of 1. Or is it really equivalent to original T-SQL?

Does anybody know how to rewrite this query better?

Why do you have two GroupJoins? – Giannis Paraskevopoulos Sep 30 '14 at 10:16 — Giannis Paraskevopoulos, Sep 30 '14 at 10:16

score 8 · Accepted Answer · edited May 23 '17 at 10:28

8

This answer from a similar question gives us an easy way to write LEFT JOINs: https://stackoverflow.com/a/4739738/1869660

var query = from t1 in T1
            from t2 in T2.Where(tt2 => (t1.ID == tt2.ID1) || (t1.ID = tt2.ID2))
                         .DefaultIfEmpty()
            select new { t1.ID, t2.Name }

edited May 23 '17 at 10:28

Community

1
1

answered Sep 30 '14 at 11:19

Sphinxxx

12,484
4
54
84

score -1 · Answer 2 · answered Sep 30 '14 at 11:16

-1

To solve this with single linq, try using cross join

var results = (from a in test1
from b in test2
where a.ID == b.ID1 || a.ID == b.ID2
select new {x = a.ID, y = b.Name});

answered Sep 30 '14 at 11:16

vikas

931
6
11

1

This will result in an INNER JOIN, not a LEFT JOIN. – Bogdan Verbenets Sep 30 '14 at 14:43

score -2 · Answer 3 · edited Sep 30 '14 at 11:03

var LeftJoin = from emp in ListOfEmployees
               join dept in ListOfDepartment
               on emp.DeptID equals dept.ID into JoinedEmpDept 
               from dept in JoinedEmpDept.DefaultIfEmpty()
               select new                          
               {
                   EmployeeName = emp.Name,
                   DepartmentName = dept != null ? dept.Name : null                          
               };

LEFT JOIN with OR condition in LINQ

3 Answers3

Linked