LINQ to SQL Left Outer Join

In LINQ to SQL, the LEFT JOIN is useful to return all the records or rows from the left table and matching records from the right table. If no columns matching rows in the right table, it will return only left table records.

 

In LINQ to SQL, to achieve LEFT JOIN behavior, it's mandatory to use the "INTO" keyword and "DefaultIfEmpty()" method.

Syntax of LINQ to SQL Left Outer Join

Following is the syntax of using LINQ to SQL Left Outer Join to get data from multiple tables.

 

var result = from ed in db.EmployeeDetails
             join d in db.Departments on ed.DeptId equals d.DeptId into edept
             from dpem in edept.DefaultIfEmpty()
             select new
             {
               Name = ed.EmpName,
               Department = dpem.DeptName
             };

If you observe the above syntax, we joined “EmployeeDetails”, “Departments” tables using into and DefaultIfEmpty() methods to get required data by using left outer join.

Example of LINQ to SQL Left Outer Join

Before we start implementing LINQ to SQL left outer join example, first, we need to create a database with required tables and map those tables to LINQ to SQL file (.dbml). If you don't know the process, don't worry; check this link create and map database tables to LINQ to SQL file (.dbml).

 

Once we create and map required tables to the .dbml file now, we will show data in our application for that Right-click on the application à select Add à New Item à Select Web Form à Give name as Default.aspx and click the OK button. 

 

Now open the Default.aspx page and write the code as shown below.

 

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Show Employee Details in Gridview</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>

Now open the code behind file and write the code as shown below.

 

protected void Page_Load(object sender, EventArgs e)
{
  EmployeeDBDataContext db = new EmployeeDBDataContext();
  if (!Page.IsPostBack)
  {
    var result = from ed in db.EmployeeDetails
                 join d in db.Departments on ed.DeptId equals d.DeptId into edept
                 from dpem in edept.DefaultIfEmpty()
                 select new
                 {
                   Name = ed.EmpName,
                   Location = ed.Location,
                   Gender = ed.Gender,
                   Department = dpem == null ? "No Department" : dpem.DeptName
                 };
    gvDetails.DataSource = result;
    gvDetails.DataBind();
  }
}

If you observe the above example, we are trying to get data from the “EmployeeDetails” and “Departments” table using the left outer join.

Result of LINQ to SQL Left Outer Join

Following is the result of LINQ to SQL left outer join example.

 

LINQ Left Outer Join in C# / VB.NET with Example

 

This is how we can use LINQ to SQL left outer join in c# to get the required data from multiple tables based on our requirements.