Find duplicates between 2 datatables and remove them

Question

I have data from 2 different sql databases I have created 2 datatables with the data The columns are not the same in both datatables But i need to find duplicates in the first column in both and remove them from the first datatable

This is the creation of the datatables

` DataTable dt2 = new DataTable();

                using (SqlDataAdapter adapter1 = new SqlDataAdapter(comm1))
                {
                    adapter1.Fill(dt2);

                    DataTable[] dt3 = dt2.AsEnumerable()
                    .Select((row, index) => new { row, index })
                    .GroupBy(x => x.index / 9999)  // integer division, the fractional part is truncated
                    .Select(g => g.Select(x => x.row).CopyToDataTable())
                    .ToArray();`

` DataTable dt = new DataTable();

                    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                    {
                            adapter.Fill(dt);


                            string antal = dt.Rows.Count.ToString();
                        File.AppendAllLines("E:\\Logs\\WashingMachine\\" + date + ".txt", new[] { "Rows in DB at " + ort + " =  " + antal + "  " });

                            DataTable[] dt1 = dt.AsEnumerable()
                            .Select((row, index) => new { row, index })
                            .GroupBy(x => x.index / 9999)  // integer division, the fractional part is truncated
                            .Select(g => g.Select(x => x.row).CopyToDataTable())
                            .ToArray();`

I have tried with this command:

` public static DataTable CompareTwoDataTable(DataTable dt, DataTable dt2) { dt.Merge(dt2); DataTable d3 = dt2.GetChanges();

        return d3;
    }

DataTable d5 = CompareTwoDataTable(dt, dt2);`

and

var dtData3 = dt.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);

To get the duplicates but cant get it to work

Please help me

Answer 1

For looking for duplicated you have to use Intersect.

You can select all the values of the column desired of each datatable and then apply the intersect.

Answer 2

Depending on how you would like to identify a duplicate, if matching the whole item you can use LINQ and compare column and filter out the depreciate, otherwise create a customer comparison extension method to check if proprieties match.

matching all columns, something like below should work.

public static DataTable RemoveDuplicates(DataTable dt1, DataTable dt2)

{ HashSet dt2FirstColumnData = new HashSet(dt2.AsEnumerable().Select(row => row[0].ToString()));

DataTable result = dt1.Clone(); 
foreach (DataRow row in dt1.Rows)
{
    if (!dt2FirstColumnData.Contains(row[0].ToString()))
    {
        result.ImportRow(row);
    }
}

return result;

}