How to assign a value using datarow inside loop with Linq

Help Studio
, ,
1

I want to extract a value from a different table inside “for each datatable” activity.
The value I want to extract is in a different table, and it is corresponding to a value of the row in the loop.

Here’s the visualized version of my question.
image

image

(Please excuse for the query in the image that does not make sense.)

image
image508×615 31.2 KB

What I tried is this, but this is not what I want.
I want to use the current row value instead of dt2.AsEnumerable.

(From d1 In dt1.AsEnumerable
Where dt2.AsEnumerable.Any(Function (x) x("col2").ToString.Trim.Equals(d1("col2").ToString.Trim))
Select r=d1("col1").ToString).FirstOrDefault()

How do I achieve this?

2

You should use the Join Datatable activity. What you are doing is called a join, it’s a standard database operation. The end result is a table containing the data from both source datatables, matched up by the designated column.

2 Likes
3

we can categorize scenarios into different cases: hasMatch, LookUp, Join Data
And we can solve a particular case with different options: LINQ; Join DataTable, LookUp, … and others

As you want to catch d1#Col1 from d1 where d1#Col2 has a match in dt2#Col2 we do see a lookUp Case.

As mentioned above we can implement it with different options

we address your question and exclude others:
LookUp CurrentRow#Col1 into dt1#Col2 and fetch d1#Col1 Or Null

OR

Left Join dt2 to dt1 on dt2#Col2 = dt1#Col2 - catch dt1#Col1 Or Null if not present

Just mention a few options along with the first()/FirstOrDefault() Operator coupled with a lambda

2 Likes
4

I have used datatable join activity, but it does not work as expected so I eventually use a different option. :frowning: I have seen other people complaining the join activity does not work either.

But thank you for your answer.
I totally forgot there is a join table option. I am going to try.

5

How do I use LookUp?

6

@yesterday

In the look up table activity provide the datatable from which look up has to be performed…

Then give the input column name or index you want to search on…

And in the output column give the column name from where the data has to be fetched

In the properties you will find option to save the data to a variable after lookup

Hope this helps

Cheers

1 Like
7

Hi @yesterday,

Assuming that you have two DataTable objects named “dtSource” and “dtLookup”, and you want to extract a value from “dtLookup” based on a value in the current row of “dtSource”:

  1. Use the For Each DataTable activity to loop through each row of “dtSource”.
  2. Inside the loop, use the Lookup method to retrieve the corresponding value from “dtLookup”. The Lookup method takes two arguments: the value to search for, and the name of the column to return the value from.
Assign Activity: value = dtLookup.AsEnumerable().Where(Function(row) row("ID").ToString() = currentRow("LookupID").ToString()).Select(Function(row) row("Value").ToString()).FirstOrDefault()

In this example, “ID” and “Value” are the names of the columns in “dtLookup” that contain the corresponding value and the value to search for, respectively. “LookupID” is the name of the column in “dtSource” that contains the value to search for. The Where method filters the rows in “dtLookup” where the value in the “ID” column matches the value in “LookupID” column of the current row in “dtSource”. The Select method returns the value from the “Value” column of the matching row. The FirstOrDefault method returns the first matching value, or Nothing if there are no matches.

  1. You can then use the extracted value in your workflow as needed.

Here’s the full example code:

For Each currentRow In dtSource.AsEnumerable()
    Assign: value = dtLookup.AsEnumerable().Where(Function(row) row("ID").ToString() = currentRow("LookupID").ToString()).Select(Function(row) row("Value").ToString()).FirstOrDefault()
    ' Use the extracted value in your workflow as needed
Next

Note that you may need to adjust the code depending on the specific column names and data types in your tables.

1 Like
8
  • decomposing further your LINQ into essential activities: have a look at the LookUp DataTable Activity
    OR
  • Use a LINQ as mentioned by Abhimanyu on the where approach
    OR
  • use a prepared LookUp Dictionary along with the ContainsKey()
1 Like
9

I was learning Linq so wanted to know how to do it with Linq.
This worked!

Thank you so much!

1 Like
10

Thank you so much for your answer.
I will try the activity!

1 Like
11

Thank you for your answer.
I am learning Linq now so it is very helpful.

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.