title: Distinct on multiple columns in datatable
body: <p>Trying to do what a SQL query (<code>SELECT DISTINCT (first,second),third FROM table</code>) would do but I am doing it with LINQ and a datatable. </p>
  
  <hr>
  
  <p><strong>EDIT</strong></p>
  
  <p>SQL should look like a Mysql </p>
  
  <p><code>select first, second, third
   FROM table
   group by first, second</code></p>
  
  <hr>
  
  <pre><code>DataTable secondTable = new DataTable();
  secondTable.Columns.Add("name", typeof(string));
  secondTable.Columns.Add("date", typeof(string));
  secondTable.Columns.Add("clockIn", typeof(string));
  secondTable.Columns.Add("clockOut", typeof(string));
  
  var t4 = (from a in firstTable.AsEnumerable()
  select new
  {
      name = a.Field&lt;string&gt;("name"),
      date = a.Field&lt;string&gt;("date"),
      clockIn = a.Field&lt;string&gt;("clockIn"),
      clockOut = a.Field&lt;string&gt;("clockOut")
  }).Distinct();
  
  var t5 = (from a in firstTable.AsEnumerable()
  select new
  {
      name = a.Field&lt;string&gt;("name"),
      date = a.Field&lt;string&gt;("date")
  }).Distinct();
  
  var t6 = (from d in t5
            join a in t4
            on new
            {
                d.name,
                d.date
            }
            equals new
            {
                a.name,
                a.date
            }
            select secondTable.LoadDataRow(
            new object[]
            {
                 d.name,d.date,a.clockIn,a.clockOut
            }, false)).ToList();
  
  ViewBag.Data = secondTable;
  </code></pre>
  
  <p>What this code is doing is, it is joining <code>t4</code> and <code>t5</code> in <code>t6</code> with no exclusions. While what I desire is all rows from <code>t4</code> that are present in <code>t5</code> should join with <code>t6</code> on the basis on (name, date) AND all rows from <code>t5</code>that don't exist in <code>t4</code> should be excluded. Can anyone please help? </p>