Inner join in LINQ
Introduction
Inner Join returns a set of rows from two or multiple tables where there is match in join condition.
Join in LINQ is a great feature introduced in .NET 3.0. It works same way as we do in SQL.
There are three types of inner join.
Simple Key Join
Composite Key Join and
Mutiple Key Join
In this article we will discuss Simple Key joins. In simple key join two or more tables/objects are joined with a single key(column).
Lets take an example of Employee and project and discuss it.
Here I have declared 2 classes Employee and Project. In this example I would like to show who is working on which project.
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", 1));
lstEmployee.Add(new Employee(2, "Rohit", 2));
lstEmployee.Add(new Employee(3, "Sumit", 3));
lstEmployee.Add(new Employee(4, "Sanjay", 4));
lstEmployee.Add(new Employee(5, "Najmul", 4));
lstEmployee.Add(new Employee(6, "Smith", 5));
lstEmployee.Add(new Employee(7, "Kiran", 7));
lstEmployee.Add(new Employee(8, "Suzain", 8));
return lstEmployee;
}
}
Project Class
public class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public Project()
{
}
public Project(int ProjectId, string ProjectName)
{
this.ProjectId = ProjectId;
this.ProjectName = ProjectName;
}
public List<Project> GetProject()
{
List<Project> lstProject = new List<Project>();
lstProject.Add(new Project(1, "Go Airlines"));
lstProject.Add(new Project(2, "UniHomes"));
lstProject.Add(new Project(3, "TravAfrica"));
lstProject.Add(new Project(4, "MahindraMahindra"));
lstProject.Add(new Project(5, "KMShops"));
lstProject.Add(new Project(6, "H2O"));
lstProject.Add(new Project(7, "SweetHomes"));
lstProject.Add(new Project(8, "BTW"));
return lstProject;
}
}
}
Here I have declared 2 Methods GetEmployee() and GetProject() above. GetEmployee() method in Employee class will return a list of employee and GetProject() in Project class will return a list of Project.
In the method ShowJoinExample() below I have added a join condition between Project and Employee on ProjectId to show who all employees work on which project.
public static void ShowJoinExample()
{
var objEmp = new Employee();
var objProj = new Project();
try
{
var employee = objEmp.GetEmployee();
var project = objProj.GetProject();
var Query = from e in employee
join p in project on e.ProjectId equals p.ProjectId
select new { e.EmployeeName, p.ProjectName };
foreach (var q in Query)
{
Console.WriteLine("Employee : " + q.EmployeeName + "\t Project : " + q.ProjectName);
}
Console.ReadKey();
}
finally
{
if (objEmp == null)
objEmp = null;
if (objProj == null)
objProj = null;
}
}
}
Output
