Remove duplicate rows from two datatables based on a specific columns

hi, I want to compare column1 of dt1 and col2 of dt2 and delete the row of duplicate values.

dt1
image

dt2
image

result:
image

Can i do it using linq?

@hckwon5

use this query
dt1.AsEnumerable().Where(function(row) dt2.AsEnumerable().Any(function(x) x(“Name”).tostring.Equals( row(“Name”).tostring) )).CopyToDataTable

Note: If this code resolve your problem please mark as a solution
Thanks

Thanks for the reply.
But i have to extract only the ones that are not duplicated

1 Like

@hckwon5

try this

(From a In dt2.AsEnumerable()
                          Join b In not Dt1.AsEnumerable()
                          On a("id") Equals b("id")
                          Select a).CopyToDataTable()  
1 Like

Hey @hckwon5 ,

Have a look on the below thread

Thanks,
Sanjit

Hi @hckwon5

Try this approach

image

col2Arr = dt2.AsEnumerable.Select(Function (r) r("col2").ToString.Trim.ToUpper).ToArray
dt_Result = dt1.AsEnumerable.Where(Function(r) Not col2Arr.Contains(r("Column1").ToString.Trim.ToUpper)).CopyToDataTable

Refer the xaml
LINQ_FilterDTBasedOnAnotherDTsCol.xaml (11.2 KB)

we can understand the case as a match case check

dtResult =

(From d in DT1.AsEnumerable
Let c1 = d("column1").toString.ToUpper.Trim
Where Not dt2.AsEnumerable.Any(Function (x) x("col2").toString.ToUpper.Trim.Equals(c1))
Select r = d).CopyToDataTable

which is from the idea not much far away as that one from my colleague @kumar.varun2

on a first fast reading this definition of duplicates was a little bit misleading as we do decide to keep or not a dt1 row when we do see the dt1.column1 value in any dt2.col2 value

so it is a match/presence check and not duplicate check. As we are interested in the non-matchers we will not do it with inner join - Join On

For approach selection we decide match/presence | joinings | Lookup cases and will pick up from these options the helping one

DT=(From r in DT1.Asenumerable
Let a = DT2.Asenumerable.where(Function(r1) r1(col2).Tostring.Tolower.equals(r(col1).Tostring.Tolower)
Where Not a.count=1
Select r).Copytodatable