Updated April 6, 2023
Introduction to LINQ Left Join
LINQ Left Join is used to return all the records from the left side data source and the matching records from the right data source. In case there are no matching columns in the right table relationship to left table, it returns NULL values. We can call Left Join also as Left Outer Join. To achieve Left Join, it is compulsory to use the “INTO” keyword and the method “DefaultEmpty().”
Syntax:
Let’s see the following syntax using Left Join to get the records from the left side data source and the matching records from a right-side data source,
from emp in Employee.GetEmployees()
join addr in Address.GetAddress()
on emp.Address_ID equals addr.ID
into EmployeeAddressList
from address in EmployeeAddressList.DefaultEmpty()
select new {emp,address};How left join works in LINQ?
Left Outer Join returns all the records from the left table, and from the right table, it returns only the matching records. In case there is no matching column in the right table relationship to left table, it returns NULL values.
For query syntax, we need to call the method DefaultIfEmpty() on every series to get the matching records; see the below code of query-syntax as follows.
var query_result = from emp in empDetails
join dept in deptDetails
on emp.employeeID equals dept.employeeID
into data_A
from data_B in data_A.DefaultIfEmpty(new DepartmentDetails())
select new
{
Employee_ID=emp.employeeID,
Employee_Name = emp.employeeName,
Employee_Salary = data_B.employeeSalary
};To achieve the left-join using method-syntax, we need to make use of GroupJoin() along with the methods DefaultIfEmpty() and SelectMany(), see the below code of method-syntax as follows.
var method_result = empDetails.GroupJoin(deptDetails,
emp => emp.employeeID,
dept => dept.employeeID,
(emp, dept) => new { emp, dept })
.SelectMany(x => x.dept.DefaultIfEmpty(),(employee, department) => new
{
EmployeeID=employee.emp.employeeID,
EmployeeName = employee.emp.employeeName,
EmployeeSalary = department == null ? 0: department.employeeSalary
}
);Examples
In order to achieve the left outer join using the query syntax, we need to call the method DefaultEmpty(). Let’s see the following implementation using Left Join in LINQ.
Example #1
Code:
using System;
using System.Collections.Generic;
using System. Linq;
using System. Text;
using System.Threading.Tasks;
namespace Console_Joins
{
class Program_LeftJoin
{
public class BookClass
{
public int Book_ID { get; set; }
public string Book_Name { get; set; }
}
public class OrderClass
{
public int Order_ID { get; set; }
public int Order_BookID { get; set; }
public string Order_PayMode { get; set; }
}
static void Main(string[] args)
{
List<BookClass> BooksList = new List<BookClass>
{
new BookClass{Book_ID=1001, Book_Name="Think and Grow Rich"},
new BookClass{Book_ID=1002, Book_Name="Awaken Giant Within"},
new BookClass{Book_ID=1003, Book_Name="As a Man Thinketh"},
new BookClass{Book_ID=1004, Book_Name="Greatest Salesman"},
new BookClass{Book_ID=1005, Book_Name="Positive Thinking"}
};
List<OrderClass> BooksOrderList = new List<OrderClass>
{
new OrderClass{Order_ID=101, Order_BookID=1001, Order_PayMode="Credit-Card"},
new OrderClass{Order_ID=102, Order_BookID=1005, Order_PayMode="Net-Banking"},
new OrderClass{Order_ID=103, Order_BookID=1001, Order_PayMode="Cheque"},
new OrderClass{Order_ID=104, Order_BookID=1003, Order_PayMode="Cash"},
new OrderClass{Order_ID=105, Order_BookID=1005, Order_PayMode="Credit-Card"},
new OrderClass{Order_ID=106, Order_BookID=1004, Order_PayMode="Net-Banking"}
};
var Order_Books = from book in BooksList
join orders in BooksOrderList
on book.Book_ID equals orders.Order_BookID
into data_A
from data_B in data_A.DefaultIfEmpty(new OrderClass())
select new
{
book.Book_ID,
B_Name = book.Book_Name,
data_B.Order_PayMode
};
Console.WriteLine("LINQ LEFT-JOIN\n");
Console.WriteLine("--------------\n");
Console.WriteLine("BOOK_ID \tBOOK_NAME \t PAYMENT_MODE\n");
Console.WriteLine("------- \t---------- \t ------------\n");
foreach (var item in Order_Books)
{
Console.WriteLine(item.Book_ID + "\t | " + item.B_Name+ "\t | " + item.Order_PayMode +"\t\n");
}
Console.ReadLine();
}
}
}Output:
Example #2
Code:
This program describes the Left Join with both the syntax method and query; to achieve the left outer join using the query syntax, we need to call the method DefaultEmpty() to get the result of group join.
In this program, for query syntax, we need to call the method DefaultIfEmpty() on every series to get the matching records. For method-syntax we need to make use of GroupJoin() along with the methods DefaultIfEmpty() and SelectMany(). Let’s see the following implementation, which includes both the query and method syntax,
using System;
using System.Collections.Generic;
using System. Linq;
using System. Text;
using System.Threading.Tasks;
namespace Console_LinQ
{
//program_2
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 = "Richard",
employeeQualification = "MCA"},
new EmployeeDetails() {employeeID = 1005, employeeName = "Ricky",
employeeQualification = "B.E"},
new EmployeeDetails() {employeeID = 1001, employeeName = "Jack",
employeeQualification = "MCA"},
new EmployeeDetails() {employeeID = 1007, employeeName = "Ponting",
employeeQualification = "B.E"},
new EmployeeDetails() {employeeID = 1003, employeeName = "Paul",
employeeQualification = "M.Sc"},
new EmployeeDetails() {employeeID = 1004, employeeName = "Beeja",
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},
};
//use of the left join to retrieve the Employee Name and their corresponding Salary, Employee_ID
//LINQ-Left Outer Join - using Query Syntax
var query_result = from emp in empDetails
join dept in deptDetails
on emp.employeeID equals dept.employeeID
into data_A
from data_B in data_A.DefaultIfEmpty(new DepartmentDetails())
select new
{
Employee_ID=emp.employeeID,
Employee_Name = emp.employeeName,
Employee_Salary = data_B.employeeSalary
};
// LINQ-Left Outer Join - using Method Syntax
var method_result = empDetails
.GroupJoin(
deptDetails,
emp => emp.employeeID,
dept => dept.employeeID,
(emp, dept) => new { emp, dept }
)
.SelectMany(
x => x.dept.DefaultIfEmpty(),
(employee, department) => new
{
EmployeeID=employee.emp.employeeID,
EmployeeName = employee.emp.employeeName,
EmployeeSalary = department == null ? 0 : department.employeeSalary
}
);
// displaying the result
Console.WriteLine("\n\nLINQ Left Join");
Console.WriteLine("\n----------------");
Console.WriteLine("\n Employee Names With their corresponding Salary\n");
Console.WriteLine("\nUsing Query Syntax\n");
Console.WriteLine("\tEmployee ID\tEmployee Names\t Salary \n");
foreach (var data in query_result)
{
Console.WriteLine("\t {0}\t\t{1}\t\t {2}",
data.Employee_ID,data.Employee_Name,data.Employee_Salary);
}
Console.WriteLine("\nUsing Method Syntax\n");
Console.WriteLine("\tEmployee ID\tEmployee Names\t Salary \n");
foreach (var dataVal in method_result)
{
Console.WriteLine("\t{0}\t\t{1}\t\t {2}",
dataVal.EmployeeID,dataVal.EmployeeName,dataVal.EmployeeSalary);
}
Console.WriteLine("\n\t-----------\t--------------\t ------ \n");
Console.ReadLine();
}
}
}
}Output:
Conclusion
I hope the article helps out to realize the above examples programmatically. Here in this article, I have explained how to implement the LINQ-Left Join using in both Query syntax and Method syntax.
Recommended Articles
This is a guide to LINQ Left Join. Here we discuss the Introduction, syntax, working of left join in LINQ along with examples and code implementation. You may also have a look at the following articles to learn more.
