Need to Compare Two datatable columns and remove the rows

Help Studio
, ,
1

I have Two data tables and need to remove the all rows from dt1 which are matching to dt2 column1 Values to the dt1 Column1 Values. Please help me with any sort of select queries.

2

@Yezdani_Shaik

Please try this

lets say data is in dt1 and dt2.Use the below in assign

dt1 = dt1.AsEnumerable.Where(function(x) dt2.AsEnumerable.Where(function(y) x("dt1Column1Name").ToString.Equals(y("dt2Column1Name").ToString).Count>0).CopyToDataTable

cheers

3

your case can be understood as a

  • Join DataTable case - then postprocessing the JoinResult
  • Match case

Match case:
Assign Activity
dtFiltered =

(From d in dt1.AsEnumerable
Let k = d("Column1").toString.Trim.ToUpper
Where Not dt2.AsEnumerable.Any(Function (d2) d2("Column1").toString.Trim.ToUpper.Equals(k))
Select r = d).CopyToDataTable

In case of an empty result is also to expect we can handle by:

4

Hi @Yezdani_Shaik

Dt1
image

Dt2
image

dtResult =

dt1.Clone()

dtResult =

(From r In dt1.AsEnumerable()
 Where Not dt2.AsEnumerable().Any(Function(a) r("Column1").ToString().Trim().ToUpper().Equals(a("Column1").ToString().Trim().ToUpper()))
 Let Result = New Object(){r("Column1"),r("Column2")}
 Select dtResult.Rows.Add(Result)).CopyToDataTable

image
image744×566 16.2 KB

Regards

5

Hi ppr ,

¿it is possible to add multiple columns criteria in example 3 columns criteria?

Edit: I have founded the solution (with two columns criteria but for multiple is similar):

(From d In dt_1.AsEnumerable
Let k1 = d(“Column1”).ToString.Trim
Let k2 = d(“Column2”).ToString.Trim
Where Not dt_2.AsEnumerable.Any(Function(d2) d2(“Column1”).ToString.Trim.Equals(k1) AndAlso d2(“Column2”).ToString.Trim.Equals(k2))
Select r = d).CopyToDataTable

Thanks & BR. !!!