var tbl1 = new table1[] { new table1("1"), new table1("2"), new table1("3") };
var tbl2 = new table2[] { new table2("1"), new table2("2"), new table2("3") };
var tbl3 = new table3[] { new table3("1"), new table3("2"), new table3("3") };
var tbl4 = new table4[] { new table4("1"), new table4("2"), new table4("3") };
var tbl5 = new table5[] { new table5("1")};
var tbl6 = new table6[] { new table6("3") };
var result = from t1 in tbl1
join t2 in tbl2 on t1.prop1 equals t2.prop2
join t3 in tbl3 on t1.prop1 equals t3.prop3
join t4 in tbl4 on t1.prop1 equals t4.prop4
join t5 in tbl5 on t1.prop1 equals t5.prop5 into t5grp
join t6 in tbl6 on t1.prop1 equals t6.prop6 into t6grp
from t5left in t5grp.DefaultIfEmpty()
from t6left in t6grp.DefaultIfEmpty()
select new
{
prop1 = t1.prop1,
prop2 = t2.prop2,
prop3 = t3.prop3,
prop4 = t4.prop4,
prop5 = (t5left != null ? t5left.prop5 : String.Empty),
prop6 = (t6left != null ? t6left.prop6 : String.Empty)
};
foreach (var x in result)
{
Response.Write(String.Format("Prop1: {0}, Prop2: {1}, Prop3: {2}, Prop4: {3}, Prop5: {4}, Prop6: {5}",x.prop1,x.prop2,x.prop3,x.prop4,x.prop5,x.prop6));
Response.Write("<BR>");
}
Please remember to mark as Answer if the post helps you out.
josephr2013
Member
32 Points
45 Posts
How to join table in Linq which have records and which do not have records
Feb 03, 2013 01:43 AM|LINK
Hi
i have 4 tables which will have data and another 2 tables where records are optional.
so now I cannot write an inner join for all 6 tables. so how should i proceed. Could you please suggest.
I have the code below which is not working please corect it and post me.
var var1 = (from Table1 t1 in context.Table1
//join Table2 t2 in context.Table2 on t1.prop1 equals t2.prop1
//join Table3 t3 in context.Table3 on t1.prop2 equals t3.prop2
join Table4 t4 in context.Table4 on t1.prop3 equals t4.prop3
join Table5 t5 in context.Table5 on t1.prop4 equals t5.prop4
join Table5 t6 in context.Table6 on t1.prop5 equals t6.prop5 where (t1.prop1 == 100)
select new
{
Prop1 = t1.prop1,
// Prop2 = t2.Prop2,
Prop3=t4.Prop3,
Prop4=t5.prop4,
Prop5=t6.Prop5)
});
Thank you.
Best regards,
Joseph
.
shakimran
Member
328 Points
148 Posts
Re: How to join table in Linq which have records and which do not have records
Feb 03, 2013 02:14 AM|LINK
HI,
Please read this tuitorial.
http://www.codeproject.com/Articles/488643/LinQ-Extended-Joins
Talal Tayyab
Participant
892 Points
132 Posts
Re: How to join table in Linq which have records and which do not have records
Feb 03, 2013 11:38 AM|LINK
You can do left outer join in LINQ using the DefaultIfEmpty keyword.
http://msdn.microsoft.com/en-US/library/vstudio/bb397895.aspx
Based on your example:
var tbl1 = new table1[] { new table1("1"), new table1("2"), new table1("3") }; var tbl2 = new table2[] { new table2("1"), new table2("2"), new table2("3") }; var tbl3 = new table3[] { new table3("1"), new table3("2"), new table3("3") }; var tbl4 = new table4[] { new table4("1"), new table4("2"), new table4("3") }; var tbl5 = new table5[] { new table5("1")}; var tbl6 = new table6[] { new table6("3") }; var result = from t1 in tbl1 join t2 in tbl2 on t1.prop1 equals t2.prop2 join t3 in tbl3 on t1.prop1 equals t3.prop3 join t4 in tbl4 on t1.prop1 equals t4.prop4 join t5 in tbl5 on t1.prop1 equals t5.prop5 into t5grp join t6 in tbl6 on t1.prop1 equals t6.prop6 into t6grp from t5left in t5grp.DefaultIfEmpty() from t6left in t6grp.DefaultIfEmpty() select new { prop1 = t1.prop1, prop2 = t2.prop2, prop3 = t3.prop3, prop4 = t4.prop4, prop5 = (t5left != null ? t5left.prop5 : String.Empty), prop6 = (t6left != null ? t6left.prop6 : String.Empty) }; foreach (var x in result) { Response.Write(String.Format("Prop1: {0}, Prop2: {1}, Prop3: {2}, Prop4: {3}, Prop5: {4}, Prop6: {5}",x.prop1,x.prop2,x.prop3,x.prop4,x.prop5,x.prop6)); Response.Write("<BR>"); }