I have two DataTables:
In both of them is a column “Order Number”
I would like to take Each “Order Number” from dt1 and check if it exist in dt2
If exist-> take next “Order Numer”
If not exist → write comment in dt1 (Column “Comment’)” This order does not exist in dt2
How to do it?
You can use For Each Row in Datatable in Dt1 and put into if condition to search Order Number in dt 2 and use this expression:
dt2.asEnumerable.Where(Function (x) x.item(“Order Number”).ToString.ToUpper.Trim.Equals(curretRow(“Order Number”).ToString.ToUpper.Trim)).Count = 0
Basically, get Order Number and lookup in dt2. If Order Number not found in any row in dt2, Count return = 0.
Then in True branch, put assign activity to currentRow.item(“Comment”) = “This order does not exist in dt2”
FYI, another approach: using LINQ with Invoke code.
dt1.AsEnumerable.Where(Function(r) dt2.AsEnumerable.All(Function(r2) r2("Order Number").ToString<>r("Order Number").ToString)).ToList().ForEach(
r("Comment")="This order does not exist in dt2"
Sample20230126-1aL.zip (2.9 KB)
Count return = 0.
Let us mention this recommendation as well:
So we rewrite to any with predicate
…Any(Function (x) …)
In case of another optimization is needed also have a look here:
Let us introduce the next changes
we are creating lists with the concatenated strings from dtold, dtnew
and creating a dictionary of only the items common in both lists
dtNewSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList
dtOldSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList
we took benefits from lists/dictionary keys
Very thanks for advice.
I took notice and I will sharing in another oportunities