Reader Level:
Articles

Co-related Queries and Self Join Using LINQ

By Vijay Prativadi on November 17, 2012
Today, in this article let’s play around with one of the interesting and most useful concepts in LINQ.
  • 0
  • 0
  • 5101

Co-related Queries

Introduction

Today, in this article let's play around with one of the interesting and most useful concepts in LINQ.

Question: What is co-related queries using LINQ?

In simple terms "It provides flexibility to perform co-relation of queries using LINQ".

Step 1: Create a new WebForm project

LINQ-Co-Related-Queries1.png

Step 2: The employee table output of the application looks like this:

LINQ-Database1.png

Step 3: The department table output of the application looks like this:

LINQ-Database2.png

Step 4: The complete code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Co_RelatedQueriesLINQApp.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">
    <style type="text/css">
        .grid
        {
            margin-top: 50px;
        }
    </style
>
    <title></title>
</head>
<
body>
    <form id="form1" runat="server">
    <center>
        <div>
            <table>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label1" runat="server" Text="Co-related Queries using LINQ" Font-Bold="true"
                            Font-Size="Large" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="Button1" runat="server" Text="Select Data" Font-Names="Verdana" Width="213px"
                            BackColor="Orange" Font-Bold="True" OnClick="Button1_Click" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:GridView ID="GridView1" runat="server" CssClass="grid" BackColor="LightGoldenrodYellow"
                            BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
                            <AlternatingRowStyle BackColor="PaleGoldenrod" />
                            <FooterStyle BackColor="Tan" />
                            <HeaderStyle BackColor="Tan" Font-Bold="True" />
                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                            <SortedAscendingCellStyle BackColor="#FAFAE7" />
                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                        </asp:GridView>
                    </td>
                </tr>
            </table>
        </div>
    </center>
    </form>
</body>
</
html>

Step 5: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace
Co_RelatedQueriesLINQApp
{
    public partial class WebForm1 : System.Web.UI.
Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            var query = (from r in objEntities.Employeewhere
                         r.Location == (from q in objEntities.Department
                         where q.Location == r.Location select q.Location).FirstOrDefault()select
                         new{FirstName = r.FirstName,LastName = r.LastName,Age = r.Age,Location = r.Location});
            GridView1.DataSource = query;
            GridView1.DataBind();
        }
        #region Instance MembersCompanyEntities objEntities = new CompanyEntities();
        #endregion
    }
}

Step 6: The output of the application looks like this:

LINQ-Co-Related-Queries2.png

Step 7: The data retrieved output of the application looks like this:

LINQ-Co-Related-Queries3.png

Self Join

Question: What is self join using LINQ?

In simple terms "It provides the flexibility to perform a join on a single table and get the result set based on the request query".

Step 1: Create a new WebForm project

Step 2: The complete code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="SelfJoinApp.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">
    <style type="text/css">
        .grid
        {
            margin-top: 50px;
        }
    </style
>
    <title></title>
</head>
<
body>
    <form id="form1" runat="server">
    <center>
        <div>
            <table>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label1" runat="server" Text="Self Join using LINQ" Font-Bold="true"
                            Font-Size="Large" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="Button1" runat="server" Text="Select Data" Font-Names="Verdana" Width="213px"
                            BackColor="Orange" Font-Bold="True" OnClick="Button1_Click" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:GridView ID="GridView1" runat="server" CssClass="grid" BackColor="LightGoldenrodYellow"
                            BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
                            <AlternatingRowStyle BackColor="PaleGoldenrod" />
                            <FooterStyle BackColor="Tan" />
                            <HeaderStyle BackColor="Tan" Font-Bold="True" />
                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                            <SortedAscendingCellStyle BackColor="#FAFAE7" />
                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                        </asp:GridView>
                    </td>
                </tr>
            </table>
        </div>
    </center>
    </form>
</body>
</
html>

Step 3: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;namespace SelfJoinApp
{
    public partial class WebForm1 : System.Web.UI.
Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            var query = (from r in objEntities.Employeejoin q in objEntities.Employee on r.ManagerId
                equals q.EmpIdselect new{FirstName = r.FirstName,
                LastName = r.LastName,ManagerName = string.Concat(q.FirstName, " ", q.LastName)}).ToList();
            GridView1.DataSource = query;
            GridView1.DataBind();
        }
        #region Instance MembersCompanyEntities objEntities = new CompanyEntities();
        #endregion
    }
}

Step 4: The output of the application looks like this:

Self-Join-in-LINQ1.png

Step 5: The data retrieved output of the application looks like this:

Self-Join-in-LINQ2.png

I hope this article is useful for you. I look forward for your comments and feedback.Thanks Vijay Prativadi

Vijay Prativadi

Hey Everyone, It's Vijay. I am SDE mostly up on Microsoft Technologies. If you are thinking to know what I do mostly when at work. I get a chance to work upon few Microsoft technologies. All the way along my core tech... Read more

COMMENT USING

Trending up