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.