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:
The Design Mode of Student Table looks like this:
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:
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:
I hope this article is useful for you.