Design automations with LINQ queries and UiPath Orchestrator

Introduction

Have you ever faced a situation where you're constantly adding new assets and modifying existing code in your automation project? It can become tedious, especially when making changes in a production environment. Here's a way to design scalable automations. By leveraging UiPath Orchestrator assets and data structures like arrays, you can significantly reduce the need to tweak code in the future.

In this tutorial, we'll explore how to design scalable automation solutions with real-life use cases in Excel automation. We’ll also compare scalable and unscalable approaches to show the benefits of this method.

Prerequisites

Before we dive in, you'll need the following.

• A good understanding of LINQ queries with data tables. You can check out this guide on LINQ and VB.NET.

• Familiarity with UiPath Robotic Enterprise Framework (REFramework). Learn more about it here or take this course on REFramework.

Excel automation

Here’s a sample dataset that we will be working with for our demonstration. We aim to filter certain genders from the “gender” column using LINQ queries.

Let’s start by comparing an unscalable solution with a scalable one.

Unscalable use-case

• Creating Orchestrator assets

In this approach, you’ll need to create multiple assets (e.g., Gender1, Gender2, Gender3, etc.). This can become cumbersome if there are numerous values to account for.

• Accessing the assets in UiPath Studio

• Filtering out those genders from the excel file using linq query

Here’s a sample LINQ query using multiple hardcoded assets: 

dTSample.AsEnumerable.Where(Function(row) row("gender").ToString.Contains(inConfig("Gender1").tostring) or row("gender").ToString.Contains(inConfig("Gender2").tostring) or row("gender").ToString.Contains(inConfig("Gender3").tostring) or row("gender").ToString.Contains(inConfig("Gender4").tostring) or row("gender").ToString.Contains(in_Config("Gender5").tostring)).Count 

Cons: Every time a new gender needs to be filtered, you must:

• Add a new asset (e.g., Gender6). 

• Modify the code to accommodate the new asset, which is not ideal for production environments. 

To address the second item of tweaking the code is what we shall be covering in the next section because it isn’t ideal in a production environment.

Scalable use-case

• Creating Orchestrator asset

Instead of creating multiple assets, use a single asset where values are separated by a delimiter, such as a pipe (|).

• Accessing the asset in UiPath Studio

Fetch the single asset and split its values into an array using this script: in_Config("Genders").Tostring.Split("|"c).Select(Function(g) g.Trim()).ToArray()

• Filtering out those genders from the excel file using linq query

Use the array of values directly in your query:

dTSample.AsEnumerable.Where(Function(row) in_Config("Genders").tostring.Split("|"c).Select(Function(g) g.Trim()).ToArray.contains(row.Field(Of String)("gender").Trim())).Count

Following this approach has its pros, which include: In the event where there is an additional gender that you need to filter out as well, you will have to do only one thing Add a new value to an already existing asset with a separator of your choice.

Pros: 

Every time a new gender needs to be filtered, you must:

• Add a new value to an already existing asset with a delimiter of your choice

• No need to modify the code, making this approach ideal for scalable and maintainable solutions in production.

Conclusion

By using a scalable approach with Orchestrator assets and LINQ queries, you can reduce the complexity, especially when dealing with large datasets and frequent changes. This method allows you to adapt quickly to changes without touching your code, reducing the risk of bugs and improving maintainability.

In summary, the scalable automation offers a clear advantage in terms of flexibility and ease of maintenance, which is crucial for large-scale automations that are likely to evolve over time. Always aim to use assets in a way that minimizes code changes and leverages UiPath powerful data manipulation capabilities, such as LINQ queries.