SQL to LINQ - Left Join two conditions

Question

How can I convert this outer left join to LINQ?

    SELECT *  from table1 t1
      left join table2 t2 on  t1.code = t2.code AND  t2.id ='xxxxx'
       WHERE t1.someId = 2

I'm trying something like this:

from t1 in db.table1
join t2 in db.table2 on t1.Code equals t2.Code
into oj
from sub in oj.DefaultIfEmpty()
where t1.someId == 2
where (sub.id == 'xxxx')

but not all rows from left table are being returned. The where clauses i think are applied after the join.

Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Camilo Terevinto, Jul 13 '18 at 00:47
For multiple parameters like that you have to create anonymous types with the values as the keys to compare `on new {t1.Code, id ="xxxxx"} equals new {t2.Code, t2.id}`. — juharr, Jul 13 '18 at 00:58
still not working because sub.id is only on one table.... i would like to apply the t2.id ='xxxxx' with the join and not after — piris, Jul 13 '18 at 01:43

score 1 · Accepted Answer · answered Jul 13 '18 at 03:49

1

var res=(from t1 in table1.Where(x=>x.someId==2)
         join t2 in table2.Where(y=>y.id==xxxx)
         on t1.code = t2.code
         into r 
         from t3 in r.DefaultIfEmpty()
         select new {t1,t3}).ToList();

answered Jul 13 '18 at 03:49

Sumit raj

821
1
7
14

SQL to LINQ - Left Join two conditions

1 Answers1