Friday, 20 July 2018

Join in LINQ using C#



Today, I think I should write the article on join using LINQ because some developers 
are confused and most of the interviewer asking about join in LINQ.

I have created two tables in a database named TestingDB. The first table name is Employees. the second table name is Students. I have used LINQ to SQL to communicate with the database 

The data context name created for LINQ To SQL is 

DataClassesTest1DataContext.



The Complete Code JoinTest.aspx looks like this:



The Complete Code for JoinTest.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind=" JoinTest.aspx.cs"Inherits="JOINS_LINQ. JoinTest " %>
<!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=" grdLeftOuterJoinTest " 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=" grdRightOuterJoinTest " 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=" grdoncatTest " 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 JoinTest : System.Web.UI.Page
    {
        protected void PageLoad(object sender, EventArgs e)
        {
            LeftOuterJoinTest ();
            RightOuterJoinTest();
            ConcatTest();
        }
        public void LeftOuterJoinTest()
        {
            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
                        };
            grdLeftOuterJoinTest.DataSource = query;
            grdLeftOuterJoinTest.DataBind();
        }
        protected void ConcatTest()
        {
            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);
            grdoncatTest.DataSource = concatquery;
            grdoncatTest.DataBind();
        }
        protected void RightOuterJoinTest ()
        {
            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
                        };
            grdRightOuterJoinTest.DataSource = query;
            grdRightOuterJoinTest.DataBind();
        }
    }
}
}
The Output of the application looks like this:




I hope this article is useful to you.


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)