CODEWITHSUNDEEP
c#sqllinq
ehsan ahmadi
ehsan ahmadi

Reputation: 498

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

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

Upvotes: 7

Views: 19600

Answers (5)

logeshpalani31
logeshpalani31

Reputation: 1638

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

Upvotes: 0

Muhammad Abdul Arif Sarker
Muhammad Abdul Arif Sarker

Reputation: 1219

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

Upvotes: 3

Syed Mhamudul Hasan
Syed Mhamudul Hasan

Reputation: 1349

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

Upvotes: -1

Sajidur Rahman
Sajidur Rahman

Reputation: 643

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

Upvotes: 0

Navoneel Talukdar
Navoneel Talukdar

Reputation: 4608

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

Upvotes: 2

Related Questions