How to use left & Right outer join in LINQ

Sksingh
Posted by Sksingh under LINQ category on | Points: 40 | Views : 40900
Let keyword provides facility to declare a temporary variable inside the Linq Query.
We can assign the result of manipulation to temporary variable inside query and we can use that temporary variable to another manipulation.

In below example let keyword hold the variable as pow and calculated value get manupulate in LINQ query itself.

In this post, will try to achieve left and right join using LINQ. As we know there are no such keywords defined in C#, we have to use DefaultIfEmpty() function to get the desired result.

Lets have two classes as below

class Employee
{
public string Name { get; set; }
public int ID { get; set; }
public int ProjectID { get; set; }
}

class Project
{
public int ProjectID { get; set; }
public string ProjectName { get; set; }
}


Now will try to write left and right outer join for above classes relation.

static void Main(string[] args)
{

Project P1 = new Project() { ProjectID = 1, ProjectName = "UID" };
Project P2 = new Project() { ProjectID = 2, ProjectName = "RBS" };
Project P3 = new Project() { ProjectID = 3, ProjectName = "XYZ" };
// Employee List
List<Employee> ListOfEmployees = new List<Employee>();
ListOfEmployees.AddRange((new Employee[]
{
new Employee() { ID = 1, Name = "Sunil", ProjectID = 1 },
new Employee() { ID = 1, Name = "Anil", ProjectID = 1 },
new Employee() { ID = 1, Name = "Suman", ProjectID = 2 },
new Employee() { ID = 1, Name = "Ajay", ProjectID = 3 },
new Employee() { ID = 1, Name = "Jimmy", ProjectID = 4 }}));

//Project List
List<Project> ListOfProject = new List<Project>();
ListOfProject.AddRange(new Project[] { P1, P2, P3 });

//Left join
var Ljoin = from emp in ListOfEmployees
join proj in ListOfProject
on emp.ProjectID equals proj.ProjectID into JoinedEmpDept
from proj in JoinedEmpDept.DefaultIfEmpty()
select new
{
EmployeeName = emp.Name,
ProjectName = proj != null ? proj.ProjectName : null
};

//Right outer join
var RJoin = from proj in ListOfProject
join employee in ListOfEmployees
on proj.ProjectID equals employee.ProjectID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new
{
EmployeeName = employee != null ? employee.Name : null,
ProjectName = proj.ProjectName
};

//Printing result of left join
Console.WriteLine(string.Join("\n", Ljoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray<string>()));

//printing result of right outer join
Console.WriteLine(string.Join("\n", RJoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray<string>()));

Console.ReadLine();

}


From above code output will be for left join it will take all employees and its corresponding project even though if any employee does not belong to any project then project name has to set as blank.

On other hand right outer join take all projects from project list and corresponding employee at left side.

Comments or Responses

Posted by: Sheonarayan on: 11/4/2016 Level:HonoraryPlatinum | Status: [Administrator] | Points: 10
Thanks SkSingh, it helped me.

Keep it up!

Login to post response