Joins Using LINQ in C#

Today, in this article we will see how to perform join operation using LINQ queries. I have created two tables in database named 'Candidate'. The first table name is Employee. the second table name is Student. I have used LINQ to SQL to communicate with database.

The data context name created for LINQ To SQL is: DataClasses1DataContext.

The Design Mode of Employee Table looks like this:

Employee Table.png

The Design Mode of Student Table looks like this:

Student Table.png

The Complete Code for WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="JOINS_LINQ.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<style type="text/css">
.gridstyle
{
float: left;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<center>
<asp:Label ID="Label2" runat="server" Text="Left Outer Join" Font-Bold="true" CssClass="gridstyle"></asp:Label><br />
<br />
<asp:GridView ID="GridView1" runat="server" CssClass="gridstyle" BackColor="White"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" OnLoad="PageLoad">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
</center>
<br />
<br />
<center>
<asp:Label ID="Label1" runat="server" Text="Right Outer Join" Font-Bold="true" CssClass="gridstyle"></asp:Label><br />
<br />
<asp:GridView ID="GridView2" runat="server" BackColor="White" CssClass="gridstyle"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" OnLoad="PageLoad">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
</center>
<center>
<asp:Label ID="Label3" runat="server" Text="Concat Operation" Font-Bold="true" CssClass="gridstyle"></asp:Label><br />
<br />
<asp:GridView ID="GridView3" runat="server" BackColor="White" CssClass="gridstyle"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" OnLoad="PageLoad">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
</center>
</div>
</form>
</body>
</html>

Code Toolbox Requirements:

3 Labels and 3 Grid Views.

The Complete Code for WebForm1.aspx.cs looks like this:

using System;
using System.Linq;

namespace JOINS_LINQ
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void PageLoad(object sender, EventArgs e)
{

LeftOuterJoin();
RightOuterJoin();
Concat();
}

public void LeftOuterJoin()
{
var db = new DataClasses1DataContext();
var query = from p in db.Employees
join r in db.Students
on p.EmpId equals r.PersonId into temp
from t in temp.DefaultIfEmpty()
select new
{
Designation = p.EmpDesignation,
EmployeeName = p.EmpName,
FirstName = t.FirstName,
LastName = t.LastName,
Age = t.Age
};
GridView1.DataSource = query;
GridView1.DataBind();
}

protected void Concat()
{
var db = new DataClasses1DataContext();
var query1 = from r in db.Students select r.FirstName;
var query2 = from p in db.Employees select p.EmpName;
var concatquery = query1.Concat(query2);
GridView3.DataSource = concatquery;
GridView3.DataBind();
}

protected void RightOuterJoin()
{
var db = new DataClasses1DataContext();
var query = from r in db.Students
join p in db.Employees
on r.PersonId equals p.EmpId into temp
from t in temp.DefaultIfEmpty()
select new
{
FirstName = r.FirstName,
LastName = r.LastName,
Age = r.Age,
Designation = t.EmpDesignation,
EmployeeName = t.EmpName

};

GridView2.DataSource = query;
GridView2.DataBind();
}

}

}

The Output of the application looks like this:

LINQ Joins output.png

I hope this article is useful for you.

7 Comments

Join the conversation! Your thoughts help the community grow.

siri13y ago
Could u please tell me how to update multiple table values?
ely14y ago
Hi, can i know why u use p.EmpId equals r.PersonId ? why not 'p.PersonId equals r.PersonId' ?
Rajesh Kumar14y ago
Good Work.....
Manoj Acharya14y ago
Dear Vijay, It is very use full post. Thanks and keep it up.
- Vijay Prativadi14y ago
  Thank You Manoj!!!
Michell Johnson14y ago
Dear Vijay it's really a great work, and it's very useful task thanks a lot.
- Vijay Prativadi14y ago
  Thank you Michell !!!