private void PerformLeftOuterJoin()
{
try
{
//Prepaar Left DataTable.
DataTable dtContacts = new DataTable("Contacts");
dtContacts.Columns.Add("ContactID", typeof(int));
dtContacts.Columns.Add("FirstName", typeof(string));
dtContacts.Columns.Add("LastName", typeof(string));
//Prepaar Right DataTable.
DataTable dtContactAddresses = new DataTable("Addresses");
dtContactAddresses.Columns.Add("AddressID", typeof(int));
dtContactAddresses.Columns.Add("AddressLine1", typeof(string));
dtContactAddresses.Columns.Add("AddressLine2", typeof(string));
dtContactAddresses.Columns.Add("AddressLine3", typeof(string));
dtContactAddresses.Columns.Add("City", typeof(string));
dtContactAddresses.Columns.Add("State", typeof(string));
dtContactAddresses.Columns.Add("Country", typeof(string));
dtContactAddresses.Columns.Add("ContactID", typeof(int));
//Prepare Final Output DataTable
DataTable dtContactResult = new DataTable("Contact_Address");
dtContactResult.Columns.Add("FirstName", typeof(string));
dtContactResult.Columns.Add("LastName", typeof(string));
dtContactResult.Columns.Add("AddressLine1", typeof(string));
dtContactResult.Columns.Add("AddressLine2", typeof(string));
dtContactResult.Columns.Add("AddressLine3", typeof(string));
dtContactResult.Columns.Add("City", typeof(string));
dtContactResult.Columns.Add("State", typeof(string));
dtContactResult.Columns.Add("Country", typeof(string));
DataRow dr = null;
//Add Rows In Left DataTable.
dr = dtContacts.NewRow();
dr.ItemArray = new object[] { 1, "Jevis", "Reshamwala" };
dtContacts.Rows.Add(dr);
dr = dtContacts.NewRow();
dr.ItemArray = new object[] { 2, "Mehul", "Jariwala" };
dtContacts.Rows.Add(dr);
dr = dtContacts.NewRow();
dr.ItemArray = new object[] { 3, "Dhaval", "Desai" };
dtContacts.Rows.Add(dr);
//Add Rows In Right DataTable.
dr = dtContactAddresses.NewRow();
dr.ItemArray = new object[] { 1, "3/456", "Navapura Karwa Road,", "Near Bhagal.", "Surat", "Gujarat", "India", 1 };
dtContactAddresses.Rows.Add(dr);
dr = dtContactAddresses.NewRow();
dr.ItemArray = new object[] { 2, "4/123", "Ghanchi Street,", "Near Patel Wadi", "Surat", "Gujarat", "India", 2 };
dtContactAddresses.Rows.Add(dr);
//Get Contact along with the their address.
var query = (from DataRow drLeft in dtContacts.Rows
join DataRow drRight in dtContactAddresses.Rows
on drLeft["ContactID"] equals drRight["ContactID"]
select new
{
FirstName = drLeft["FirstName"],
LastName = drLeft["LastName"],
AddressLine1 = drRight["AddressLine1"],
AddressLine2 = drRight["AddressLine2"],
AddressLine3 = drRight["AddressLine3"],
City = drRight["City"],
State = drRight["State"],
Country = drRight["Country"]
});
//Prepare Final Output.
foreach (var item in query)
{
dr = dtContactResult.NewRow();
dr.ItemArray = new object[]
{ item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
item.City,item.State,item.Country};
dtContactResult.Rows.Add(dr);
}
//As above result shown 3rd contact "Dhaval" is missing because it has not entered an address.
//So we can go for "Left Outer Join"
//Let\'s see how to do it.
//Clear Rows from Existing Result.
dtContactResult.Rows.Clear();
//Get Contact along with the their address.
query = (from DataRow drLeft in dtContacts.Rows
join DataRow drRight in dtContactAddresses.Rows
on drLeft["ContactID"] equals drRight["ContactID"]
into rightRow
from rw in rightRow.DefaultIfEmpty()
select new
{
FirstName = drLeft["FirstName"],
LastName = drLeft["LastName"],
AddressLine1 = rw == null ? "Not Avilable" : rw["AddressLine1"],
AddressLine2 = rw == null ? "Not Avilable" : rw["AddressLine2"],
AddressLine3 = rw == null ? "Not Avilable" : rw["AddressLine3"],
City = rw == null ? "Not Avilable" : rw["City"],
State = rw == null ? "Not Avilable" : rw["State"],
Country = rw == null ? "Not Avilable" : rw["Country"]
});
//Prepare Final Output.
foreach (var item in query)
{
dr = dtContactResult.NewRow();
dr.ItemArray = new object[]
{ item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
item.City,item.State,item.Country};
dtContactResult.Rows.Add(dr);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}