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

possible duplicate of [LEFT JOIN in LINQ to entities?](http://stackoverflow.com/questions/19356439/left-join-in-linq-to-entities) — Polynomial Proton, May 29 '16 at 07:42
Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Bartho Bernsmann, Jun 21 '19 at 05:58

Muhammad Abdul Arif Sarker · Accepted Answer · 2016-06-06T11:19:46.303

3

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();

edited Jun 06 '16 at 11:19

answered May 29 '16 at 09:40

Muhammad Abdul Arif Sarker

1,211
4
15
25

OP has asked for left join in your case actually it 's inner join so it will only select the matched results not extra one from personnel which is the purpose of left join.you can see https://msdn.microsoft.com/en-IN/library/bb397895.aspx – Navoneel Talukdar Jun 06 '16 at 10:54

score 2 · Answer 2 · answered May 29 '16 at 07:33

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,
    ......
}

score 0 · Answer 3 · edited May 29 '16 at 09:16

var q=(
              from pd in dataContext.personnel 
              join od in dataContext.Employee 
                  on pd.EmployTypecode equals od.EmployTypecode 
                  into t 
              from rt in t.DefaultIfEmpty() 
              orderby pd.EmployTypecode 
              select new 
              {  
                  EmployTypecode=(int?)rt.EmployTypecode,
                  pd.Name, 
                  pd.Family,  
                  rt.EmplytyppeTye 
              }
         ).ToList();

score 0 · Answer 4 · answered Mar 07 '23 at 06:45

You can write like below in Linq both lamda and query

Using query syntax

var resultOfQuery = (from u in users
            join p in managers on u.Manager equals p.Email into ps
            from p in ps.DefaultIfEmpty()
            select new
            {
                User = u,
                Manager = p
            }).ToList();

Using Lambda

        var resultOfQUery = users.GroupJoin(managers, u => u.Manager, p => p.Email, (u, ps) => new {u, ps})
            .SelectMany(t => t.ps.DefaultIfEmpty(),
                (t, p) => new
                {
                    User = t.u,
                    Manager = p
                });

score -1 · Answer 5 · answered May 29 '16 at 09:26

Why dont use SQL query to convert EF to LIST. In EF 6.1

write

 public class personnel
    {
        public String Name { get; set; }
        public String Family { get; set; }
        public String EmployTypecode { get; set; }
        public String employtypeName { get; set; }
        public String EmplytyppeTye { get; set; }
    }

List<personnel> personnels = dbentities.Database.SqlQuery<personnel>(@"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 ").ToList();

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

5 Answers5

Linked