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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    MVC Corporation is consulting and IT services based company.