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:

Difference between Ienumerable and Iqueryable



IEnumerable and IQueryable are used for data manipulation in LINQ from the database and collections.


IEnumerable
  1. IEnumerable exists in the System.Collections namespace.
  2. IEnumerable is suitable for querying data from in-memory collections like List, Array and so on.
  3. While querying data from the database, IEnumerable executes "select query" on the server-side, loads data in-memory on the client-side and then filter the data. 
  4. IEnumerable is beneficial for LINQ to Object and LINQ to XML queries.
  5. If you create an IEnumerable, then all rows will be pulled into memory as objects before running the query.
IQueryable

  1. IQueryable exists in the System.Linq Namespace.
  2. IQueryable is suitable for querying data from out-memory (like remote database, service) collections.
  3. While querying data from a database, IQueryable executes a "select query" on server-side with all filters.
  4. IQueryable is beneficial for LINQ to SQL queries.
  5. If you create an IQueryable, then the query may be converted to SQL and run on the database server
Posted by at No comments:
Subscribe to: Posts (Atom)