document.write('
Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  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.      //Prepaar Right DataTable.
  11.      DataTable dtContactAddresses = new DataTable("Addresses");
  12.      dtContactAddresses.Columns.Add("AddressID", typeof(int));
  13.      dtContactAddresses.Columns.Add("AddressLine1", typeof(string));
  14.      dtContactAddresses.Columns.Add("AddressLine2", typeof(string));
  15.      dtContactAddresses.Columns.Add("AddressLine3", typeof(string));
  16.      dtContactAddresses.Columns.Add("City", typeof(string));
  17.      dtContactAddresses.Columns.Add("State", typeof(string));
  18.      dtContactAddresses.Columns.Add("Country", typeof(string));
  19.      dtContactAddresses.Columns.Add("ContactID", typeof(int));
  20.      //Prepare Final Output DataTable
  21.      DataTable dtContactResult = new DataTable("Contact_Address");
  22.      dtContactResult.Columns.Add("FirstName", typeof(string));
  23.      dtContactResult.Columns.Add("LastName", typeof(string));
  24.      dtContactResult.Columns.Add("AddressLine1", typeof(string));
  25.      dtContactResult.Columns.Add("AddressLine2", typeof(string));
  26.      dtContactResult.Columns.Add("AddressLine3", typeof(string));
  27.      dtContactResult.Columns.Add("City", typeof(string));
  28.      dtContactResult.Columns.Add("State", typeof(string));
  29.      dtContactResult.Columns.Add("Country", typeof(string));
  30.  
  31.      DataRow dr = null;
  32.  
  33.      //Add Rows In Left DataTable.
  34.      dr = dtContacts.NewRow();
  35.      dr.ItemArray = new object[] { 1, "Jevis", "Reshamwala" };
  36.      dtContacts.Rows.Add(dr);
  37.      dr = dtContacts.NewRow();
  38.      dr.ItemArray = new object[] { 2, "Mehul", "Jariwala" };
  39.      dtContacts.Rows.Add(dr);
  40.      dr = dtContacts.NewRow();
  41.      dr.ItemArray = new object[] { 3, "Dhaval", "Desai" };
  42.      dtContacts.Rows.Add(dr);
  43.  
  44.      //Add Rows In Right DataTable.
  45.      dr = dtContactAddresses.NewRow();
  46.      dr.ItemArray = new object[] { 1, "3/456", "Navapura Karwa Road,", "Near Bhagal.", "Surat", "Gujarat", "India", 1 };
  47.      dtContactAddresses.Rows.Add(dr);
  48.      dr = dtContactAddresses.NewRow();
  49.      dr.ItemArray = new object[] { 2, "4/123", "Ghanchi Street,", "Near Patel Wadi", "Surat", "Gujarat", "India", 2 };
  50.      dtContactAddresses.Rows.Add(dr);
  51.  
  52.      //Get Contact along with the their address.
  53.  
  54.      var query = (from DataRow drLeft in dtContacts.Rows
  55.                   join DataRow drRight in dtContactAddresses.Rows
  56.                     on drLeft["ContactID"] equals drRight["ContactID"]
  57.                   select new
  58.                   {
  59.                     FirstName = drLeft["FirstName"],
  60.                     LastName = drLeft["LastName"],
  61.                     AddressLine1 = drRight["AddressLine1"],
  62.                     AddressLine2 = drRight["AddressLine2"],
  63.                     AddressLine3 = drRight["AddressLine3"],
  64.                     City = drRight["City"],
  65.                     State = drRight["State"],
  66.                     Country = drRight["Country"]
  67.                   });
  68.  
  69.  
  70.      //Prepare Final Output.
  71.      foreach (var item in query)
  72.      {
  73.        dr = dtContactResult.NewRow();
  74.        dr.ItemArray = new object[]
  75.                                { item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
  76.                                  item.City,item.State,item.Country};
  77.        dtContactResult.Rows.Add(dr);
  78.      }
  79.  
  80.  
  81.  
  82.      //As above result shown 3rd contact "Dhaval" is missing because it has not entered an address.
  83.      //So we can go for "Left Outer Join"
  84.      //Let\'s see how to do it.
  85.      //Clear Rows from Existing Result.
  86.  
  87.      dtContactResult.Rows.Clear();
  88.  
  89.      //Get Contact along with the their address.
  90.      query = (from DataRow drLeft in dtContacts.Rows
  91.               join DataRow drRight in dtContactAddresses.Rows
  92.                 on drLeft["ContactID"] equals drRight["ContactID"]
  93.                into rightRow
  94.               from rw in rightRow.DefaultIfEmpty()
  95.               select new
  96.               {
  97.                 FirstName = drLeft["FirstName"],
  98.                 LastName = drLeft["LastName"],
  99.                 AddressLine1 = rw == null ? "Not Avilable" : rw["AddressLine1"],
  100.                 AddressLine2 = rw == null ? "Not Avilable" : rw["AddressLine2"],
  101.                 AddressLine3 = rw == null ? "Not Avilable" : rw["AddressLine3"],
  102.                 City = rw == null ? "Not Avilable" : rw["City"],
  103.                 State = rw == null ? "Not Avilable" : rw["State"],
  104.                 Country = rw == null ? "Not Avilable" : rw["Country"]
  105.               });
  106.  
  107.      //Prepare Final Output.
  108.      foreach (var item in query)
  109.      {
  110.        dr = dtContactResult.NewRow();
  111.        dr.ItemArray = new object[]
  112.                                { item.FirstName,item.LastName,item.AddressLine1,item.AddressLine2,item.AddressLine3,
  113.                                  item.City,item.State,item.Country};
  114.        dtContactResult.Rows.Add(dr);
  115.      }
  116.  
  117.    }
  118.    catch (Exception ex)
  119.    {
  120.      MessageBox.Show(ex.Message);
  121.    }
  122.  
  123.  }
');