Compare 2 datatables and update Rows if 4 columns match

I have two datatables, both with the same columns. dt1 will have 10-20 rows that are from an older file. These rows have to be compared to dt2, which has 300-500 rows.

How do I use linq to compare the rows from dt1 to dt2 and if the columns shown below all match, update the entire row in dt1 with what is in dt2. If the row from dt1 doesn’t have a match, leave it as is. There are other columns in the tables, but those do not require matching.

image

Thank you.

hi @mbuehler

You can refer the below article and get the details:

Thanks

Hi @mbuehler
Can you share your file?

Thanks for the reply, @adiijaiin. If I am understanding the operations correctly that you have linked, wouldn’t that require all columns to match or not match to work correctly?

Unfortunately, I can’t share the file.

can you create sample ?

Yes, that would require matching of all columns.

1 Like

In this scenario I actually have 21 columns in the datatables. There is not a unique identifier, so I have to match 4(shown in screen shot above) of the 21 columns.

If there is no match to those 4 columns in dt2, then the row in dt1 can be skipped. If all of the 4 columns match, then that row needs to overwrite the row in dt1.

The goal is to update the rows in dt1 that appear in dt2 based on those 4 matched columns.

Hi @mbuehler

You can use LINQ to compare and update rows in dt1 based on matching criteria with dt2. Assuming that the columns you want to compare for matching are named “Column1,” “Column2,” and “Column3” (you can replace these with your actual column names), here’s a LINQ-based approach in C#:

var query = from row1 in dt1.AsEnumerable()
            join row2 in dt2.AsEnumerable()
            on new
            {
                Column1 = row1.Field<string>("Column1"),
                Column2 = row1.Field<string>("Column2"),
                Column3 = row1.Field<string>("Column3")
            }
            equals new
            {
                Column1 = row2.Field<string>("Column1"),
                Column2 = row2.Field<string>("Column2"),
                Column3 = row2.Field<string>("Column3")
            }
            into matchingRows
            from match in matchingRows.DefaultIfEmpty()
            select new
            {
                Row1 = row1,
                Row2 = match
            };

foreach (var pair in query)
{
    if (pair.Row2 != null)
    {
        // Rows matched in dt2, update the entire row in dt1 with data from dt2
        pair.Row1.ItemArray = pair.Row2.ItemArray;
    }
    // If no match is found, do nothing, as you want to leave it as is.
}

In this code:

  • We use a LINQ query to join dt1 and dt2 based on the specified columns.
  • For matching rows, we update the entire row in dt1 with the data from the corresponding row in dt2.
  • If there’s no match in dt2, we leave the row in dt1 as it is, as per your requirement.

This code assumes that the columns you want to compare have the same data type in both DataTables (string in this case). Adjust the column names and data types as needed to match your actual DataTable structure.

Thanks!!

Thanks, @Nitya1.

Based on this the output should be dt1 with updated rows from dt2 where the columns match correct?

Thanks all. I was not able to utilize some of the above suggestions and get them to work. I ended up using a couple of nested for each row in DT activities and looped through dt2 with the current row from dt 1 looking for a match and then adding it to an additional table. I don’t think it’s the quickest method, but it’s working.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.