Error in Inner Join Data Tables using custom LINQ

Hi Everyone,

I want to use custom LINQ to inner join 2 data tables due to the functionality of “OR” that UiPath cant work on. However, an error occurs saying “Assign: Object reference not set to an instance of an object.” using the code below. Would anyone help me, please?

(From d1 In dt1.AsEnumerable
Let jr = dt2.AsEnumerable.Where(Function (d2) d1("id").toString.Trim.Equals(d2("ID").toString.Trim) Or d1("Passport").toString.Trim.Equals(d2("Passport").toString.Trim))
From j In jr
Let ra = d1.ItemArray.Concat(j.itemArray).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

For the columns matching:
dt1 = “id”, “Passport”
dt2 = “ID”, “Passport”
whereas dtResult is the variable to save the result

is ensured that dtResult is initialized and prepared with target column structure?
dt1, dt2 also needs to be also not nul and be populated with data

Hi @ppr

dResult is not initialized until this assign activity takes place.

And the 2 inner joining columns of dt1 and dt2 might occur blank cells.

Would then lead to the error mentioned above?

may you share some screnshots from the rlevant parts along with some sample data and exoected output. This would help for the next steps. Thanks

Hi Sorry for the late reply

Please kindly referring to the screenshots below:

Sheet 1 (dt1)
image

Sheet 2 (dt2)
image

Sheet 3 (dtResult)
image

You may refer to the excel file as well. Thanks if you can help
Testing.xlsx (10.2 KB)

focus on that dtResult is prepared in advance e.g. with a build datatable and modelling the needed datacolumn structure

keep in mind that

Let ra = d1.ItemArray.Concat(j.itemArray).toArray

will result to a ra with too much items as from dt2 again ID and Passport will be fetched.

have a check on a strategy like

Let ra = d1.ItemArray.Concat({j("Subject", j("Result")}).toArray

Sorry may I know if there are ways not to prepare dtResult in advance since there might be further columns to be added in dt1 and dt2 afterwards

when setting up a LINQ in this way we do need the prepared dtResult. But we can also dynamize its generation. So we are flexible and prepared on varying data column structures.

different strategies:

  • Datatable Merges on datatable.Clone
  • for each and dynamized setup the target column structure by processing dt1, dt2