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:

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.