LINQ to check if item in one column of Datatable 1 is present or contains items from another column in Datatable2

Hi all,

I have 2 datatable- DT1 and DT2.
I want to filter through a COLA in DT1 from a list of Keys present in COL_KEY in DT2.
The keyword can either be present or can contain a part of it.

LINQ query that I’m using is producing a lot of duplicate value or junk value.
I’m using
(From dt1 in DT1.AsEnumerable() from dt2 in DT2.AsEnumerable() where dt1(“COLA”).tostring().contains(dt2(“COL_KEY”).tostring()) Select dt1).toarray().copyToDatatable

Any Idea?

Hi @Ray_Sha1 ,

Could you try this instead?

(From dt1 in DT1.AsEnumerable() from dt2 in DT2.AsEnumerable() where dt1(“COLA”).tostring().contains(dt2(“COL_KEY”).tostring()) Select dt1).copyToDatatable

Kind Regards,
Ashwin A.K

Hi @Ray_Sha1

Welcome back to community

Have a look on the thread

Regards
Gokul

1 Like

Hi,
Thanks for the reply
This is giving me the whole Input list.

Hi @Ray ,

Could you try this instead?

Declare an Array of Strings for the keys.
Assign->

Dt2.AsEnumerable().Select(Function(s) s("COL_KEY").ToString).Distinct().ToArray()

After that, declare a List of DataRows for checking if the condition applied populates the variable with values(directly casting it to datatable will result in error)

Assign->

Dt1.AsEnumerable().Where(Function(w) arr_colKeys.Contains(w("COLA").ToString)).ToList()

image

CompareDataTables.xaml (6.6 KB)

Kind Regards,
Ashwin A.K

1 Like

let us understand that you are interested on dt1 rows when dt1.ColA value is contained in any values from dt2.Col_Key

arrKeys | String() =

dt2.AsEnumerable().Select(Function(x) x("COL_KEY").ToString).Distinct().ToArray()

Result | List(Of DataRow) =

(From d In dt1.AsEnumerable()
Let v = d("ColA").toString
Where arrKeys.Any(Function (x) v.Contains(x))
Select r = d).toList

then based on the filter result we can copy it to a datatable or clone the origin one
check within an if acitvity
Result.Count > 0
Then: dtResult = Result.CopyToDataTable
Else: dtResult = dt1.Clone

1 Like

Hi,

This takes only those values which are equals.
I must also check the value which “contains”

Where arrKeys.Any(Function (x) x.Contains(v))

its is looping over all arrKeys items and checks for a string contains against the COLA value
If any value from arrKey is containing the looped cola value then the dt1 looped row will be forwarded

1 Like

Hi,
Even though we check for contain, the code is checking on the same values.

Yea, But it considering only the exact same values.

grafik

First line would check on item is equal to llo base

second line is similar to the LINQ Hello, Hallo is like arKeys and we get true for llo. A check on string contains base

Please elaborate more on this in relation to the immediate panel output from above

1 Like

@ppr,

I get that too.
lets consider the values in COLA are:
Abc
xAbc
Def
xDef

and in COL_Key are:
ABC
Def

the final output must have:
Abc
xAbc
Def
xDef

that is the outcome I looking for.

then we just incorporate the case insensitive check within

(From d In dt1.AsEnumerable()
Let v = d("ColA").toString.toUpper
Where arrKeys.Any(Function (x) v.Contains(x.toUpper))
Select r = d).toList

The value obtained is
ABC
Def

I’ll verify with other sets

@Ray_Sha1
got it, issue was about the swapped check:

grafik

(From d In dt1.AsEnumerable()
Let v = d(“ColA”).toString.toUpper
Where arrKeys.Any(Function (x) v.Contains(x.toUpper))
Select r = d).toList

grafik

find starter help here:
ppr_LINQBox_Ray_Sha1.xaml (10.5 KB)

1 Like

I’d tried this as well.
The whole Input table is coming up.

Hey @ppr ,
Can you help me with “doesn’t contain” condition?

trying another method

looks similar to your requirement:

1 Like

sure, just share sample data, requirment description and expected output sample. Thanks

1 Like

@ppr,
I got the desired value using activities. I will share the files in a while.