Left Outer Join Using LINQ

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.

   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:          });

Now let's see the result.

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: }

LINQ Zip Operator

The Zip operator was added in Framework 4.0. It enumerates two sequences in step (like a zipper), returning a sequence based on applying a function over each element pair.
The extension method signature is:

   1: public static IEnumerable<TResult> Zip<TFirst, TSecond, TResult>(

   2:                                     this IEnumerable<TFirst> first,

   3:                                     IEnumerable<TSecond> second,

   4:                                     Func<TFirst, TSecond, TResult> resultSelector)

Zip cycles through two sequences using deferred execution. The items at the same index are paired and each pair is transformed using a function that is usually provided as a lambda expression. The sequence stops as soon as one of the sequences exhausts its elements; ideally, the sequences are identical in length or you can say in other word continues until all of the items in either sequence have been processed. If one sequence has more elements than the other, the extra elements are not projected into the new sequence. The merging happens in the func function. It takes two arguments (the element from first source, and the element from the second source) and lets you return a combined type.

Example:

   1: using System;

   2: using System.Linq;

   3:  

   4: namespace ZIP_OPERATOR_DEMP

   5: {

   6:   class Program

   7:   {

   8:     static void Main(string[] args)

   9:     {

  10:       //Initialize two lists of integer with same sequence.

  11:       int[] integers1 = new int[] { 1, 2, 3, 4, 5 };

  12:       int[] integers2 = new int[] { 10, 20, 30, 40, 50 };

  13:  

  14:       //Now apply zip operator on it.

  15:       var sumsZip = integers1.Zip(integers2, (i, j) => i + j);

  16:  

  17:       Console.WriteLine("Output of zip of two interger series.");

  18:       Console.WriteLine("=====================================");

  19:  

  20:       //Get the out of Zip operation.

  21:       foreach (var sum in sumsZip)

  22:       { Console.WriteLine(sum); }

  23:  

  24:       Console.WriteLine("\n");

  25:  

  26:       //Initialize two lists with different sequence and different datatypes.

  27:       int[] integers3 = new int[] { 1, 2, 3, 4, 5 };

  28:       char[] characters = new char[] { 'A', 'B', 'C', 'D', 'E', 'F' };

  29:       var items = characters.Zip(integers3, (c, i) => string.Format("{0}{1}", c, i));

  30:  

  31:       Console.WriteLine("Output of zip of two different datatype series.");

  32:       Console.WriteLine("===============================================");

  33:  

  34:       //Get the out of Zip operation.

  35:       foreach (var item in items)

  36:       { Console.WriteLine(item); }

  37:  

  38:       Console.WriteLine("\n");

  39:  

  40:       //Now we zip more then two list with one by one basis.

  41:       //So final output will be full address line for each element.

  42:       var bldgNum = new string[] {"A5", "A2", "A1" };

  43:       var flatNum = new int[] {104, 109, 25, 200 };

  44:       var streetNm = new string[] {"Baker Street", "Cross Street", "Hu Street" };

  45:       var city = new string[] { "CO", "WA", "AU", "CA" };

  46:  

  47:       Console.WriteLine("Output of multiple zip operation multiple series.");

  48:       Console.WriteLine("=================================================");

  49:  

  50:       //Zip all the partiall address to full address.

  51:       var address = bldgNum.Zip(flatNum, (bl, fl) => bl + ", " + fl.ToString())

  52:                            .Zip(streetNm, (fl, st) => fl + " , " + st)

  53:                            .Zip(city, (st, ct) => st + ", " + ct);

  54:  

  55:       foreach (var addr in address)

  56:         Console.WriteLine(addr);

  57:  

  58:       Console.ReadLine();

  59:     }

  60:   }

  61: }

Output:

 

Explanation:

For the first example we'll zip together two sequences containing the same number of elements, where each sequence contains the same data type. In the code below you can see that the Zip operator is used as an extension method of the first array. The second array is passed to the first parameter. The second argument specifies the projection function that is used to generate the items in the resultant collection. In this case each pair of values is summed.

In the second example we add an array of characters so that we can demonstrate combining sequences of differing types. Here a character and an integer from the source arrays are combined with string.Format to generate a string. Note also that the list of characters is longer than the integer sequence. The final item, "F", is dropped from the results. 

Conclusion:

We can use this "ZIP" linq operator where we want some action to perform on same or different data types sequences but operator consider only "Source" & "Destination" index match else ignore other element in sequence.

For more details you can refer MSDN.