Friday 17 August 2012

How to do join between two Table using LinQ

Join given below tables with LinQ to Sql Technique.








Step 1. Open Visual Studio 2008 and choose Web application. Press Ctrl+Alt+s or go to View Menu and choose Server Explorar.

Step 2. Right Click on Data Connection and choose Add connection Option.



























After that Database connection Dialog Box will be open as given below Image:































Select Your Server name, Database name and Enter you Credentials if it has.

After made of connection your server explorer should show you database and its associated tables as shown in below image:
















Step 3. Go to Project Menu and choose Add New Item option and then choose LinQ to SQL Classes file



After that this file will be included in your Solution Explorer 












Step 4. Double Click on this Sample.dbml file then Object Relation Designer will be displayed as given below image:


Step 5. Drag your Server Explorer Tables into Object Relation Designer Frame as shown in below Image:


Step 6. Below code in your default.aspx.cs page.


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace LinqToSql
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
          SampleDataContext dc = new SampleDataContext();
          var ids = from c in dc.EMPs
                    join e1 in dc.CITies on c.ID equals e1.ID  into jeet
                    from j in jeet
                    select new { c.ID, c.NAME, j.CITY_NAME };

           foreach (var v in ids)
           {
               Response.Write(v.ID.ToString() +" "+ v.NAME.ToString()+" "+ v.CITY_NAME.ToString()+"<br>");
           }
        }
    }
}

Finally records will be shown as given below output