Removing records of one datatable from another datatable

Help Studio
, ,
1

I have one datatable with approx 82000 records & another Datatable with approx. 27015 records. I want to remove the records of second DT from the first and get the balanced records.

To achieve the above I tried the below query :slight_smile:
“DT1.AsEnumerable().Where(Function(row) Not DT2.AsEnumerable().Select(Function(r) r.Field(Of String)(“Claim Number”)).Any(Function(x) x = row.Field(Of String)(“Claim Number”))).CopyToDataTable()”

For less number of records the query works but for higher number of records it is getting stuck

Tried with Inner Join too but getting the below error -
‘Job stopped with an unexpected exit code 0xE0434352’

2

Hi @Yoichi ,

Any support from your end would really be appreciated.

3

rewriting your statement to query syntax:

From d in DT1.AsEnumerable
Where Not DT2.AsEnumerable().Select(Function(r) r.Field(Of String)(“Claim Number”)).Any(Function(x) x = d.Field(Of String)(“Claim Number”))
Select d).CopyToDataTable()

we do see that there is a repeated calculation, collecting of the Claim numbers. When LINQs are slow the first thing we can do is to reduce the operations.

Give a try on following:

arrD1CN | String() =
dt1.AsEnumerable.Select(Function (x) x("Claim Number").toString.Trim).Distinct().toArray

arrD2CN | String() =
dt2.AsEnumerable.Select(Function (x) x("Claim Number").toString.Trim).Distinct().toArray

arrNonCommonCNs = arrD1CN.Except(arrD2CN).toArray

Then use the following LINQ

(From d in DT1.AsEnumerable
Where arrNonCommonCNs.Contains(d(“Claim Number”).toString.Trim)
Select r=d).CopyToDataTable

In case of this will also not get processed in an acceptable timeframe, then we have to look for further optimizations and parallelization options