Left Right Full Outer Join and Cross join with Linq to Sql

Left Right Full Outer Join  and Cross join with Linq to Sql

 Previous Topic

1.Left Outer join with Linq to Sql…………………………………….

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

DataClassesDataContext dd = new DataClassesDataContext();

protected void Button1_Click(object sender, EventArgs e)

{

//left outer join………………

var leftouterjoin1 = from m in dd.StuRecords join s in dd.StuColleges on m.id equals s.id into g from g1 in g.DefaultIfEmpty() select new { m.id, m.Name, m.City, g1.CollegeName, g1.BranchName };

//or……

var leftouterjoin = (from m in dd.GetTable<StuRecord>()

join s in dd.GetTable<StuCollege>()

on m.id equals s.id into g

from g1 in g.DefaultIfEmpty()

select new { m.id, m.Name, m.City, CollegeName = g1.CollegeName == null ? string.Empty : g1.CollegeName, BranchName = g1.BranchName == null ? string.Empty : g1.BranchName });

GridView1.DataSource = leftouterjoin;

GridView1.DataBind();

}

}

2.Right Outer join with Linq to Sql…………………………………….

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

DataClassesDataContext dd = new DataClassesDataContext();

private object fullouterjoin;

protected void Button1_Click(object sender, EventArgs e)

{

//right outer join………………………………

var rightouterjoin1 = from m in dd.StuColleges join s in dd.StuRecords on m.id equals s.id into g from g1 in g.DefaultIfEmpty() select new { m.id, g1.Name, g1.City, m.BranchName, m.CollegeName };

//or……

var rightouterjoin  = from m in dd.GetTable<StuCollege>()

join s in dd.GetTable<StuRecord>()

on m.id equals s.id

into g from g1 in g.DefaultIfEmpty()

select new { m.id, Name = g1.Name == null ? "None" : g1.Name, City = g1.City == null ? "None" : g1.City, m.BranchName, m.CollegeName };

GridView1.DataSource = rightouterjoin;

GridView1.DataBind();

}

}

3.Full Outer join with Linq to Sql…………………………………….

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

DataClassesDataContext dd = new DataClassesDataContext();

private object fullouterjoin;

protected void Button1_Click(object sender, EventArgs e)

{

//left outer  join..............................

var vleftouterjoin = (from m in dd.StuRecords

join s in dd.StuColleges on m.id equals s.id

into temcollege from college in temcollege.DefaultIfEmpty()

select new { m.id, m.Name, m.City,BranchName=college.BranchName==null ?"None":college.BranchName,CollegeName=college.CollegeName==null?"None":college.CollegeName });

// right outer join..................................

var vrightouterjoin = (from m in dd.StuColleges

join s in dd.StuRecords on m.id equals s.id

into temprecord from record in temprecord.DefaultIfEmpty()

select new { m.id, Name = record.Name == null ? "None" : record.Name, City = record.City == null ? "None" : record.City, m.BranchName, m.CollegeName });

// full outer join................................. (combination left and right outer join with Union )

fullouterjoin = (from m in dd.StuRecords

join s in dd.StuColleges on m.id equals s.id into temcollege

from college in temcollege.DefaultIfEmpty()

select new { m.id, m.Name, m.City, BranchName = college.BranchName == null ? "None" : college.BranchName, CollegeName = college.CollegeName == null ? "None" : college.CollegeName })

.Union

(from m in dd.StuColleges

join s in dd.StuRecords on m.id equals s.id

into temprecord from record in temprecord.DefaultIfEmpty()

select new { m.id, Name = record.Name == null ? "None" : record.Name, City = record.City == null ? "None" : record.City, m.BranchName, m.CollegeName });

GridView1.DataSource = fullouterjoin;

GridView1.DataBind();

}

}

4.Cross  join with Linq to Sql…………………………………….

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

DataClassesDataContext dd = new DataClassesDataContext();

protected void Button1_Click(object sender, EventArgs e)

{

//cross join in linq to sql

var crossjoin = from m in dd.StuRecords from s in dd.StuColleges select new { m.id, m.Name, m.City, s.BranchName, s.CollegeName };

GridView1.DataSource = crossjoin;

GridView1.DataBind();

}

}