LINQ to SQL Left Outer Join

  Total Views : 194
  Comments : 0
 
  Keywords : Linq to sql left join in c# / vb.net with example, How to use linq to sql left outer join in c# / vb.net with example, Linq to sql left outer join with where null condition in c# / vb.net

Here we will learn LINQ to SQL Left join or Left outer join in c#, vb.net with example and how to use LINQ to SQL Left join to get elements from left table and matching elements from right table in c#, vb.net with example.

LINQ to SQL Left Outer Join

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

 

In LINQ to SQL to achieve LEFT JOIN behavior, its mandatory to use "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 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 implement 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 .dbml file now we will show data in our application for that Right click on application → select Add → New Item → Select Web Form → Give name as Default.aspx and click OK button. 

 

Now open Default.aspx page and write the code like 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 code behind file and write the code like 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 above example, we are trying to get data from “EmployeeDetails” and “Departments” table using left outer join.

Output 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 required data from multiple tables based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User