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

Help Studio
, , , , , , ,
#1

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?

Forum Engagement Daily Reports
#2

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

#3

Hi @Ray_Sha1

Welcome back to community

Have a look on the thread

Regards
Gokul

#4

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

#5

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
#6

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
#7

Hi,

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

#9 
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
#10

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

#11

Yea, But it considering only the exact same values.

#12

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
#13

@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.

#14

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
#15

The value obtained is
ABC
Def

I’ll verify with other sets

#16

@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
#17

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

#18

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

trying another method

#19

looks similar to your requirement:

1 Like
#20

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

1 Like
#21

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