Composite key join in LINQ
A composite key join is used to perform join operation in which you want to use more than one key to define a match.
In this article we will discuss Composite Key join in LINQ. In this join two or more tables/objects are joined with a more than one keys(column).
Lets take an example of Employee, Project and ProjectLocation and discuss it.
In this example we will work on the query below.
"List all the employee name, project name and their project location where employee belong to a project and a project has a project location defined."
Here I have declared 3 classes Employee, Project and ProjectLocation.
Employee Class
public class Employee
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
public int ProjectId { get; set; }
public Employee()
{
}
public Employee(int EmployeeId, string EmployeeName, int ProjectId)
{
this.EmployeeId = EmployeeId;
this.EmployeeName = EmployeeName;
this.ProjectId = ProjectId;
}
public List<employee> GetEmployee()
{
List<employee> lstEmployee = new List<employee>();
lstEmployee.Add(new Employee(1, "Kamal Kant", 1));
lstEmployee.Add(new Employee(2, "Rohit Sharma", 2));
lstEmployee.Add(new Employee(3, "Sumit Gupta", 3));
lstEmployee.Add(new Employee(4, "Sanjay Singh", 2));
lstEmployee.Add(new Employee(5, "Najmul Hoda", 2));
lstEmployee.Add(new Employee(6, "John Smith", 1));
lstEmployee.Add(new Employee(7, "Kiran Verma", 3));
lstEmployee.Add(new Employee(8, "Suzain Khan", 1));
return lstEmployee;
}
}
Project Class
public class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public int LocationId { get; set; }
public Project()
{
}
public Project(int ProjectId, string ProjectName, int LocationId)
{
this.ProjectId = ProjectId;
this.ProjectName = ProjectName;
this.LocationId = LocationId;
}
public List<project> GetProject()
{
List<project> lstProject = new List<project>();
lstProject.Add(new Project(1, "GoAirlines", 2));
lstProject.Add(new Project(2, "UniHomes", 3));
lstProject.Add(new Project(3, "TravAfrica",4));
lstProject.Add(new Project(4, "MahindraMahindra", 1));
return lstProject;
}
}
ProjectLocation Class
public class ProjectLocation
{
public int LocationId { get; set; }
public string Location { get; set; }
public ProjectLocation()
{
}
public ProjectLocation(int LocationId, string Location)
{
this.LocationId = LocationId;
this.Location = Location;
}
public List<projectlocation> GetProjectLocation()
{
List<projectlocation> lstProject = new List<projectlocation>();
lstProject.Add(new ProjectLocation(1, "India"));
lstProject.Add(new ProjectLocation(2, "UAE"));
lstProject.Add(new ProjectLocation(3, "USA"));
lstProject.Add(new ProjectLocation(4, "South Africa"));
return lstProject;
}
}
Here I have declared 3 Methods GetEmployee() , GetProject() and GetProjectLocation()above. GetEmployee() method in Employee class will return a list of employee and GetProject() in Project class will return a list of Project and GetProjectLocation() in ProjectLocation class will return a list of ProjectLocation.
In the method CompositeKeyJoinExample() below I have added a join condition between Project, Employee and ProjectLocation on ProjectId, employeeId and LocationId keys.
public static void CompositeJoinExample()
{
var objEmp = new Employee();
var objProj = new Project();
var objLoc = new ProjectLocation();
try
{
var employee = objEmp.GetEmployee();
var project = objProj.GetProject();
var location = objLoc.GetProjectLocation();
var Query = from e in employee
from l in location
join p in project
on new { e.ProjectId, l.LocationId } equals new { p.ProjectId, p.LocationId }
select new { e.EmployeeName, p.ProjectName, l.Location };
Console.WriteLine("Employee Name\tProject Name\t Location");
Console.WriteLine("=============\t============\t ========");
foreach (var q in Query)
{
Console.WriteLine( "{0}\t{1}\t{2}" , q.EmployeeName,q.ProjectName,q.Location);
Console.WriteLine("\n");
}
Console.ReadKey();
}
finally
{
if (objEmp == null)
objEmp = null;
if (objProj == null)
objProj = null;
if (objLoc == null)
objLoc = null;
}
}
Output
