How can I use Left join in linq that we use in sql?

Question

How can I use Left join in Linq that I write SQL query?

select 
    p.Name, p.Family,
    E.EmployTypecode, E.employtypeName, E.EmplytyppeTye 
from 
    personnel as p
left join 
    Employee as E on E.EmployTypecode = p.EmployTypecode 

Answer 1

Use Join keyword instead of Left join and it is mandatory to use "INTO" keyword and "DefaultIfEmpty()" method as right table returns null value.

   var query = from p in personnel 
               join e in Employee on p.EmployTypecode equals e.EmployTypecode into t
               from nt in t.DefaultIfEmpty()
               orderby p.Name

    select new
    {
        p.Name, p.Family,
        EmployTypecode=(int?)nt.EmployTypecode,  // To handle null value if Employtypecode is specified as not null in Employee table.
        nt.employtypeName, nt.EmplytyppeTye
    }.ToList();

Answer 2

Do it like this :

var query = 
from  p in personnel
join e in Employee 
    on p.EmployTypecode equals e.EmployTypecode
into temp
from j in temp.DefaultIfEmpty()
select new
{
    name = p.name,
    family = p.family,
    EmployTypecode = String.IsNullOrEmpty(j.EmployTypecode) ? "" : j.EmployTypecode,
    ......
}