Join Operations using LINQ in UiPath Studio

Have you ever tried to perform Join operations with LINQ instead of Join Data Tables activity in UiPath?

“Join” is an operation that allows you to combine data from two or more tables based on a common column or multiple columns. It is used to retrieve related information from multiple sources and create a new table with the desired columns.

There are four main types of join, which differ in how they handle unmatched rows from the tables: inner join, full outer join, left outer join, and right outer join,

The differences between these types of joins are:

Inner Join: returns records that have matching values in both tables.
Full Outer Join: returns all records from both tables, regardless of whether they have matching values or not.
Left Outer Join: returns all records from the left table, and the matched records from the right table. If there is no match, the right table columns are filled with null values.
Right Outer Join: returns all records from the right table, and the matched records from the left table. If there is no match, the left table columns are filled with null values.

These operations can be performed with LINQ in UiPath using the Assign activity and the Invoke Code activity.

Let’s consider an example scenario. Suppose you have two data tables: dt_Employees and dt_Salaries, which contain information about employees and their salaries respectively. You want to join these two data tables based on the EmployeeID column and display the employee name, department, and salary in a new data table. Using LINQ, you can write the following query in an Assign activity:

dt_Result = (From e In dt_Employees
Join s In dt_Salaries
On e("EmployeeID") Equals s("EmployeeID")
Select e("Name"), e("Department"), s("Salary")).CopyToDataTable()

🚀 Note: Consider a scenario where whitespace characters exist at the end of a data entry in one of the tables but are not present in the other. In such cases, these two values will not match, and they will be displayed as separate rows in the result table. When performing a join operation using LINQ, we can make adjustments to the data by using methods like Trim to ensure more accurate results:

dt_Result = (From e In dt_Employees
Join s In dt_Salaries
On e("EmployeeID").ToString().Trim() Equals s("EmployeeID").ToString().Trim()
Select e("Name"), e("Department"), s("Salary")).CopyToDataTable()

Happy automating!