Updated April 10, 2023
Introduction to LINQ Inner Join
LINQ Inner Join returns only the match records from both tables; that is, it returns common data from the two data sources. The mismatching records will be eliminated from the resultant set. In other words, imagine that we had two data sources by using it; we can retrieve only the common records that exist in both the data sources left over and will be removed from the resultant set. In the LINQ query, the inner join is performed using the “join” keyword.
Syntax:
SQL Inner Join Syntax
Select Column_Names
From table_1
Inner join table_2 on table_1.colomn_name=table_2.column_name;LINQ Query Syntax
Var _query=from emp in db.EmployeeMaster
Join dept in db.DepartmentMaster
On emp.eID equals dept.empID
Select new
{
emp.eID,
emp.eName,
dept.dName
};How inner join works in LINQ?
In the LINQ query, the inner join is performed using the “join” keyword. It returns the matching records or patterns that appear in two or more tables; the remaining non-matching records or patterns will not be considered, and the resultant set returns only the matching records from the tables. Let’s understand with the diagram,
Let’s see the following simple example.
Table_1 – EmployeeMaster
Table_2- DepartmentMaster
Query Example
Var _query=from emp in db.EmployeeMaster
Join dept in db.DepartmentMaster
On emp. DepartmentID equals dept. DepartmentID
Select new
{
EmployeeCode=emp. EmployeeCode,
EmployeeName=emp. EmployeeName,
DepartmentName=dept. DepartmentName
};
Result
EmployeeCode EmployeeName DepartmentName
E002 Alex Human-Resources
E003 Max Development
Examples
Imagine that we have two data sources, EmployeeMaster and DepartmentMaster. Using them, we can retrieve only the common records that exist in both; the data sources left behind will be eliminated from the resultant set. Let’s see the following example,
Example #1
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Console_LinQ
{
//program_1
class Program
{ // Getting Employee details
public class EmployeeDetails
{
public int employeeID
{
get;
set;
}
public string employeeName
{
get;
set;
}
public string employeeQualification
{
get;
set;
}
} // Employee department details
public class DepartmentDetails
{
public int employeeID
{
get;
set;
}
public string employeeDept
{
get;
set;
}
public int employeeSalary
{
get;
set;
}
}
class LinqProgram
{
static public void Main()
{
List<EmployeeDetails> empDetails = new List<EmployeeDetails>() {
new EmployeeDetails() {employeeID = 1000, employeeName = "Henry",
employeeQualification = "MCA"},
new EmployeeDetails() {employeeID = 1005, employeeName = "Remo",
employeeQualification = "B.E"},
new EmployeeDetails() {employeeID = 1001, employeeName = "Smith",
employeeQualification = "MCA"},
new EmployeeDetails() {employeeID = 1007, employeeName = "Rio",
employeeQualification = "B.E"},
new EmployeeDetails() {employeeID = 1003, employeeName = "Jack",
employeeQualification = "M.Sc"},
new EmployeeDetails() {employeeID = 1004, employeeName = "Peter",
employeeQualification = "B.E"},
};
List<DepartmentDetails> deptDetails = new List<DepartmentDetails>() {
new DepartmentDetails() {employeeID = 1000, employeeDept = "Development",
employeeSalary = 60000},
new DepartmentDetails() {employeeID = 1001, employeeDept = "Development",
employeeSalary = 35000},
new DepartmentDetails() {employeeID = 1002, employeeDept = "Admin",
employeeSalary = 45000},
new DepartmentDetails() {employeeID = 1003, employeeDept = "Graphical-Designing",
employeeSalary = 57000},
};
// by using the inner join we can retrieve the Employee Name and their corresponding Salary
var result = from emp in empDetails
join dept in deptDetails
on emp.employeeID equals dept.employeeID
select new
{
Employee_Name = emp.employeeName,
Employee_Salary = dept.employeeSalary
};
// to view the result
Console.WriteLine("\n\nUsing LINQ Inner Join Method ");
Console.WriteLine("----------------------------");
Console.WriteLine("\n Getting the Employee Names With their corresponding Salary \n\n");
Console.WriteLine("\tEmployee Names\t Salary \n");
Console.WriteLine("\t--------------\t ------ \n");
foreach (var data in result)
{
Console.WriteLine("\t {0}\t\t {1}",
data.Employee_Name, data.Employee_Salary);
}
Console.WriteLine("\n\t--------------\t ------ \n");
Console.ReadKey();
}
}
}
} In the above program, the EmployeeDetails contains the attributes EmployeeID, Name, and Qualification; likewise, DepartmentDetails contains the attributes EmployeeID, Department name, and salary of an employee. The common attribute in both data is EmployeeID, so we can easily retrieve the matching records exits in both tables by using ID attributes; from that EmployeeID, we can use join to retrieve matching records. Let's check the following output,Output:
Example #2
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Console_LinQ
{
//program_1
class Program
{
// Getting Employee details
public class EmployeeDetails
{
public int employeeID
{
get;
set;
}
public string employeeName
{
get;
set;
}
public string employeeQualification
{
get;
set;
}
public int DepartmentID { get; set; }
public static List<EmployeeDetails> Get_Employees()
{
return new List<EmployeeDetails>()
{
new EmployeeDetails() {employeeID = 1000, employeeName = "Henry",DepartmentID=1,
employeeQualification = "MCA"},
new EmployeeDetails() {employeeID = 1005, employeeName = "Remo", DepartmentID=2,
employeeQualification = "B.E"},
new EmployeeDetails() {employeeID = 1001, employeeName = "Smith",DepartmentID=1,
employeeQualification = "MCA"},
new EmployeeDetails() {employeeID = 1007, employeeName = "Rio",DepartmentID=3,
employeeQualification = "B.E"},
new EmployeeDetails() {employeeID = 1003, employeeName = "Jack",DepartmentID=1,
employeeQualification = "M.Sc"},
new EmployeeDetails() {employeeID = 1004, employeeName = "Peter",DepartmentID=3,
employeeQualification = "B.E"},
};
}
}
// Employee department details
public class DepartmentDetails
{
public int deptID
{
get;
set;
}
public string deptName
{
get;
set;
}
public static List<DepartmentDetails> Get_Departments()
{
return new List<DepartmentDetails>(){
new DepartmentDetails { deptID = 1, deptName = "IT"},
new DepartmentDetails { deptID = 2, deptName = "Human-Resources"},
new DepartmentDetails { deptID = 3, deptName = "Designing"},
};
}
}
class LinqProgram
{
static public void Main()
{
//using Linq query to get the matching records with the help of ID- attribute, with the common attribute we can easily retrieve the data
var result1 = from e in EmployeeDetails.Get_Employees()
join d in DepartmentDetails.Get_Departments()
on e.DepartmentID equals d.deptID
select new
{
EmployeeName = e.employeeName,
DepartmentName = d.deptName
};
Console.WriteLine("\n\nLINQ Query - Inner Join Method ");
Console.WriteLine("------------------------------");
Console.WriteLine("\n\tEmployee Names\t Departments \n");
Console.WriteLine("\t--------------\t ----------- \n");
foreach (var employee in result1)
{
Console.WriteLine("\t\t"+employee.EmployeeName + "\t" +
employee.DepartmentName);
}
Console.ReadLine();
}
}
}
}Output:
Conclusion
I hope this article will help you with a good understanding. It explains the essential functionalities, like how the joins are used and the working flow of the process to get the matching records that exist in the data sources, with several examples.
Recommended Articles
This is a guide to LINQ Inner Join. Here we discuss the Introduction, syntax, and How it works with examples and code implementation. You may also have a look at the following articles to learn more –
