title: Joining two DataTables in C# with non matching data
body: <p>I have two tables in SQL that I populate into DataTables in C#. They SQL tables are housed on different servers and different DB's.</p>
  
  <p>In the first table I have 5 columns</p>
  
  <pre><code>Name(string), AgentStatus(string), TimeInState(double), TaskHandled(double), Region(string)
  </code></pre>
  
  <p>The second table I have 3 columns</p>
  
  <pre><code>Name(string), CChats(double), AChats(double)
  </code></pre>
  
  <p>I have been using this link to merge the tables in C# </p>
  
  <p><a href="https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp">Inner join of DataTables in C#</a></p>
  
  <p>However the issue I am having is this.</p>
  
  <p>Table 1 has 59 rows, 1 for each user.</p>
  
  <p>Table 2 has 25 rows, 2 for each user that has a Chat account.</p>
  
  <p>When I merge them in C# I only get the matches from table 1 that match the 25 rows in table 2. I need to show all rows from table1 and if they have a row in table 2 show that data, otherwise display 0's</p>
  
  <p>I know where the issue is, its in the select statement in the link above, but I am not sure how to fix it in C#</p>
  
  <p>Here is my code that does not work......The listbox is just to see the output for debugging.....</p>
  
  <pre><code>        DataTable dt1 = new DataTable();
          DataTable dt2 = new DataTable();
  
          dt1.Columns.Add("Name", typeof(string));
          dt1.Columns.Add("Status", typeof(string));
          dt1.Columns.Add("Time", typeof(double));
          dt1.Columns.Add("Calls", typeof(double));
          dt1.Columns.Add("Region", typeof(string));
  
          dt2.Columns.Add("Name", typeof(string));
          dt2.Columns.Add("CChats", typeof(double));
          dt2.Columns.Add("AChats", typeof(double));
  
          foreach(DataRow dr in _agentStates.Rows)
          {
              DataRow row = dt1.NewRow();
              row["Name"] = dr[0].ToString();
              row["Status"] = dr[1].ToString();
              row["Time"] = Convert.ToDouble(dr[2].ToString());
              row["Calls"] = Convert.ToDouble(dr[3].ToString());
              row["Region"] = dr[4].ToString();
              dt1.Rows.Add(row);
          }
          foreach(DataRow dr in _chatCount.Rows)
          {
              DataRow row = dt2.NewRow();
              row["Name"] = dr[0].ToString();
              row["CChats"] = Convert.ToDouble(dr[1].ToString());
              row["AChats"] = Convert.ToDouble(dr[2].ToString());
              dt2.Rows.Add(row);
  
          }
  
          var result = from table1 in dt1.AsEnumerable()
                       join table2 in dt2.AsEnumerable() on (string)table1["Name"] equals (string)table2["Name"]
                       select new
                       {
                           Name = (string)table2["Name"],
                           Status = (string)table1["Status"],
                           Time = (double)table1["Time"],
                           Calls = (double)table1["Calls"],
                           Region = (string)table1["Region"],
                           CChats = (double)table2["CChats"]
                       };
          foreach (var item in result)
          {
             listBox1.Items.Add(item.Name + " " + item.CChats.ToString());
  
          }
  </code></pre>