compare datatables and save unmatched rows in third datatable

Question

I am trying to compare two datatables and capture the difference in third datatable.

DataTable one = new DataTable();
one.Columns.Add("ID");
one.Columns.Add("PCT");
one.Rows.Add("1", "0.1");
one.Rows.Add("2", "0.2");
one.Rows.Add("3", "0.3");
DataTable two = new DataTable();
two.Columns.Add("ID");
two.Columns.Add("PCT");
two.Columns.Add("OldPCT");
two.Rows.Add("1", "0.1", "0");
two.Rows.Add("2", "0.1", "0");
two.Rows.Add("3", "0.9", "0");
two.Columns.Remove("OldPCT");

//First method
DataTable three = two.AsEnumerable().Except(one.AsEnumerable()).CopyToDataTable();
foreach (DataRow dr in three.AsEnumerable())
{
    string strID = dr[0].ToString();
    string strPCT = dr[1].ToString();
}

//second method
var diffName = two.AsEnumerable().Select(r => r.Field<string>("PCT")).Except(one.AsEnumerable().Select(r => r.Field<string>("PCT")));
if (diffName.Any())
{
    DataTable Table3 = (from row in two.AsEnumerable()
                        join name in diffName
                        on row.Field<string>("PCT") equals name
                        select row).CopyToDataTable();
}

So far I have tried two methods and I'm not not getting my expected result and it should be like.

In third datatable the values should be like mentioned below.

 ID    PCT
 2     O.1
 3     0.9   

Recent One:

DataTable one = new DataTable();
one.Columns.Add("ID");
one.Columns.Add("PCT");
one.Rows.Add("1", "0.1");
one.Rows.Add("2", "0.2");
one.Rows.Add("2", "0.2");
one.Rows.Add("3", "0.3");
one.Rows.Add("3", "0.3");
DataTable two = new DataTable();
two.Columns.Add("ID");
two.Columns.Add("PCT");
two.Rows.Add("1", "0.1");
two.Rows.Add("2", "0.1");
two.Rows.Add("2", "0.1");
two.Rows.Add("3", "0.8");
two.Rows.Add("3", "0.9");

Now I need to get all the rows from datatable two except first row. But I am getting only last three rows.

Answer 1

Building on Hogan's answer, you can use DataRowComparer.Default as the second parameter to the Except() method (instead of creating a custom IEqualityComparer):

// this will get all rows from table two that don't match rows in one
// the result is an IEnumerable<DataRow> 
var unmatched = two.AsEnumerable()
  .Except(one.AsEnumerable(), DataRowComparer.Default);

// CopyToDataTable converts an IEnumerable<DataRow> into a DataTable
// but it blows up if the source object is empty

// this statement makes sure unmatched has data before calling CopyToDataTable()
// if it is empty, we 'clone' (make an empty copy) of one of the original DataTables
var three = unmatched.Any() ? unmatched.CopyToDataTable() : one.Clone();

This will do a value-based comparison of the fields in each row to determine if they are equal.

Answer 2

You need a custom IEqualityComparer

void Main()
{
   DataTable one = new DataTable();
   one.Columns.Add("ID");
   one.Columns.Add("PCT");
   one.Rows.Add("1", "0.1");
   one.Rows.Add("2", "0.2");
   one.Rows.Add("3", "0.3");
   DataTable two = new DataTable();
   two.Columns.Add("ID");
   two.Columns.Add("PCT");
   two.Columns.Add("OldPCT");
   two.Rows.Add("1", "0.1", "0");
   two.Rows.Add("2", "0.1", "0");
   two.Rows.Add("3", "0.9", "0");
   two.Columns.Remove("OldP

   DataTable three = two.AsEnumerable().Except(one.AsEnumerable(),new RowEqualityComparer()).CopyToDataTable();
   foreach (DataRow dr in three.AsEnumerable())
   {
       string strID = dr[0].ToString();
       string strPCT = dr[1].ToString();
   }
}


class RowEqualityComparer : IEqualityComparer<DataRow>
{
   public bool Equals(DataRow b1, DataRow b2)
   {
     if ((b1.Field<string>("ID") == b2.Field<string>("ID")) && (b1.Field<string>("PCT") == b2.Field<string>("PCT")))
     {
       return true;
     }
     else
     {
       return false;
     }
   }

   public int GetHashCode(DataRow bx)
   {
     return (bx.Field<string>("ID")+bx.Field<string>("PCT")).GetHashCode();
   }
}