Left Right Full Outer Join and
Cross join with Linq to Sql
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();
}
}
0 comments:
Post a Comment