By default join occurs in LINQ that is "INNER JOIN". If we want achieve "LEFT OUTER JOIN" that collect all data from "Left" side table and "Null" if not found in "Right" table so we can achieve with keyword "DefaultIfEmpty".
Let's see an Example how to do this.
I am having two table first is "Contacts"[Left] table & another is "Addresses"[Right] table.
Structure of this table as given below.
Now we want final output DataTable as "Contact_Address" means all contact with their address so we have final output datatable structure as shown below.
Now i am trying to get result of this two table as join using LINQ.
1: var query = (from DataRow drLeft in dtContacts.Rows
2: join DataRow drRight in dtContactAddresses.Rows
3: on drLeft["ContactID"] equals drRight["ContactID"]
4: select new
5: {
6: FirstName = drLeft["FirstName"],
7: LastName = drLeft["LastName"],
8: AddressLine1 = drRight["AddressLine1"],
9: AddressLine2 = drRight["AddressLine2"],
10: AddressLine3 = drRight["AddressLine3"],
11: City = drRight["City"],
12: State = drRight["State"],
13: Country = drRight["Country"]
14: });
I am getting the result something like this those contact who has no address it will be lost in above query so it is be default inner join.
Now I want to display all the "Contacts" regardless that have "Address" entered or not.
So we will go for the LEFT OUTER JOIN as we have "Contacts" table as Left Table.
So we will go for the LEFT OUTER JOIN as we have "Contacts" table as Left Table.
1: query = (from DataRow drLeft in dtContacts.Rows
2: join DataRow drRight in dtContactAddresses.Rows
3: on drLeft["ContactID"] equals drRight["ContactID"]
4: into rightRow
5: from rw in rightRow.DefaultIfEmpty()
6: select new
7: {
8: FirstName = drLeft["FirstName"],
9: LastName = drLeft["LastName"],
10: AddressLine1 = rw == null ? "Not Avilable" : rw["AddressLine1"],
11: AddressLine2 = rw == null ? "Not Avilable" : rw["AddressLine2"],
12: AddressLine3 = rw == null ? "Not Avilable" : rw["AddressLine3"],
13: City = rw == null ? "Not Avilable" : rw["City"],
14: State = rw == null ? "Not Avilable" : rw["State"],
15: Country = rw == null ? "Not Avilable" : rw["Country"]
16: });
Explanation :
Hold "drRight" to some temp object say into rightRow now we have explicitly defined that if no join found then get default value that is "null". if correspond address not found against "ContactID" it will return "null" row it is handle by this line of code "rightRow.DefaultIfEmpty()".
so we have to take care explicitly that if no data found at right table so we have to put "Default Value" for that like have handle that using this line of code,
rw == null ? "Not Avilable" : rw["AddressLine1"]
If we put default value and we have not handle this "null" condition then it might be generate an error like "object reference not set to an instance of an object" if no correspond row found at right table.
Conclusion :
By the same way you can do "Right Outer Join" .
Happy Joining.
Here is the code.
1: private void PerformLeftOuterJoin()
2: {
3: try
4: {
5: //Prepaar Left DataTable.
6: DataTable dtContacts = new DataTable("Contacts");
7: dtContacts.Columns.Add("ContactID", typeof(int));
8: dtContacts.Columns.Add("FirstName", typeof(string));
9: dtContacts.Columns.Add("LastName", typeof(string));
10:
11: //Prepaar Right DataTable.
12: DataTable dtContactAddresses = new DataTable("Addresses");
13: dtContactAddresses.Columns.Add("AddressID", typeof(int));
14: dtContactAddresses.Columns.Add("AddressLine1", typeof(string));
15: dtContactAddresses.Columns.Add("AddressLine2", typeof(string));
16: dtContactAddresses.Columns.Add("AddressLine3", typeof(string));
17: dtContactAddresses.Columns.Add("City", typeof(string));
18: dtContactAddresses.Columns.Add("State", typeof(string));
19: dtContactAddresses.Columns.Add("Country", typeof(string));
20: dtContactAddresses.Columns.Add("ContactID", typeof(int));
21:
22: //Prepare Final Output DataTable
23: DataTable dtContactResult = new DataTable("Contact_Address");
24: dtContactResult.Columns.Add("FirstName", typeof(string));
25: dtContactResult.Columns.Add("LastName", typeof(string));
26: dtContactResult.Columns.Add("AddressLine1", typeof(string));
27: dtContactResult.Columns.Add("AddressLine2", typeof(string));
28: dtContactResult.Columns.Add("AddressLine3", typeof(string));
29: dtContactResult.Columns.Add("City", typeof(string));
30: dtContactResult.Columns.Add("State", typeof(string));
31: dtContactResult.Columns.Add("Country", typeof(string));
32:
33: DataRow dr = null;
34:
35: //Add Rows In Left DataTable.
36: dr = dtContacts.NewRow();
37: dr.ItemArray = new object[] { 1, "Jevis", "Reshamwala" };
38: dtContacts.Rows.Add(dr);
39:
40: dr = dtContacts.NewRow();
41: dr.ItemArray = new object[] { 2, "Mehul", "Jariwala" };
42: dtContacts.Rows.Add(dr);
43:
44: dr = dtContacts.NewRow();
45: dr.ItemArray = new object[] { 3, "Dhaval", "Desai" };
46: dtContacts.Rows.Add(dr);
47:
48: //Add Rows In Right DataTable.
49: dr = dtContactAddresses.NewRow();
50: dr.ItemArray = new object[] { 1, "3/456", "Navapura Karwa Road,", "Near Bhagal.", "Surat", "Gujarat", "India", 1 };
51: dtContactAddresses.Rows.Add(dr);
52:
53: dr = dtContactAddresses.NewRow();
54: dr.ItemArray = new object[] { 2, "4/123", "Ghanchi Street,", "Near Patel Wadi", "Surat", "Gujarat", "India", 2 };
55: dtContactAddresses.Rows.Add(dr);
56:
57: //Get Contact along with the their address.
58: var query = (from DataRow drLeft in dtContacts.Rows
59: join DataRow drRight in dtContactAddresses.Rows
60: on drLeft["ContactID"] equals drRight["ContactID"]
61: select new
62: {
63: FirstName = drLeft["FirstName"],
64: LastName = drLeft["LastName"],
65: AddressLine1 = drRight["AddressLine1"],
66: AddressLine2 = drRight["AddressLine2"],
67: AddressLine3 = drRight["AddressLine3"],
68: City = drRight["City"],
69: State = drRight["State"],
70: Country = drRight["Country"]
71: });
72:
73: //Prepare Final Output.
74: foreach (var item in query)
75: {
76: dr = dtContactResult.NewRow();
77: dr.ItemArray = new object[]
78: { item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
79: item.City,item.State,item.Country};
80: dtContactResult.Rows.Add(dr);
81: }
82:
83: //As above result shown 3rd contact "Dhaval" is missing because it has not entered an address.
84: //So we can go for "Left Outer Join"
85: //Let's see how to do it.
86:
87: //Clear Rows from Existing Result.
88: dtContactResult.Rows.Clear();
89:
90: //Get Contact along with the their address.
91: query = (from DataRow drLeft in dtContacts.Rows
92: join DataRow drRight in dtContactAddresses.Rows
93: on drLeft["ContactID"] equals drRight["ContactID"]
94: into rightRow
95: from rw in rightRow.DefaultIfEmpty()
96: select new
97: {
98: FirstName = drLeft["FirstName"],
99: LastName = drLeft["LastName"],
100: AddressLine1 = rw == null ? "Not Avilable" : rw["AddressLine1"],
101: AddressLine2 = rw == null ? "Not Avilable" : rw["AddressLine2"],
102: AddressLine3 = rw == null ? "Not Avilable" : rw["AddressLine3"],
103: City = rw == null ? "Not Avilable" : rw["City"],
104: State = rw == null ? "Not Avilable" : rw["State"],
105: Country = rw == null ? "Not Avilable" : rw["Country"]
106: });
107:
108: //Prepare Final Output.
109: foreach (var item in query)
110: {
111: dr = dtContactResult.NewRow();
112: dr.ItemArray = new object[]
113: { item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
114: item.City,item.State,item.Country};
115:
116: dtContactResult.Rows.Add(dr);
117: }
118: }
119: catch (Exception ex)
120: {
121: MessageBox.Show(ex.Message);
122: }
123: }
