Menus

Saturday, December 22, 2012

Joins Using LINQ in C#

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
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)