LINQ is not easy to wrap your head around.

Heck, even I find myself struggling with it from time to time, especially when the problem comes sandwiched with layers upon juicy layers of complexities.

It’s a struggle to find any resource out there which describes it in layman terms, other then the technical descriptions which will lay you flat on the ground.

But What if I Didn’t Want To Do Anything Fancy With It?

Maybe you just want to filter out rows that met a certain criteria.

Maybe you just want the first three rows.

Maybe you don’t like using the For Each Loop, because its not nearly as cool as compartmentalizing the entire Logic into a single Assign Activity.

But to be honest, you don’t have to use it everywhere.

I would only suggest using LINQ if the dataset is huge, or if the data manipulation involves multiple conditions or computations.

That, and when you wanna show off.

Lambda vs Query

LINQ can be written in one of two ways.

One is by linking methods together, while the other is by using an approach much similar to SQL. Sometimes, it would make sense to use a combination of both to tidy up our code.

We will be discussing the former here and explore the latter in another article.

Chaining Methods

The title pretty much explains how Lambda Expressions work.

When you use the Lambda Expression, you are coupling railway coaches of methods one after the other.

The passengers in the train are seated inside an anonymous function which performs the work for us. These anonymous functions are referred to as Lambda Functions.

The work these individual blocks of code will perform could either be filtering data, creating projections or both.

How you go about performing these operations is for another day, however I will present a couple of examples just to give you an idea of how it works, and how you can work with it.

Lets Take Data Tables for Example

DataTables are often heavily relied upon when working with RPA. You will also work extensively with APIs, Arrays, Lists, IEnumerables of various objects, Lists of IEnumerables, and the list goes on.

But for today, we will limit ourselves to the simplest of operations, as this is only an introduction. I will torture you with the rest on another day.

The IEnumerable methods can only be invoked on data where the items are separated, like in the case of Lists and Arrays.

On a totally unrelated topic, when you heat the surface of a block of ice and bring it in contact with another block of ice, it fuses together.

Water is splashed onto its surface to hasten the process. It’s once the ice blocks are fused in place when we start sculpting.

Similarly, DataTables are a combination of rows and columns, and treated as a solid icy block of data. Before we can fuse methods onto it, we have to thaw it with an AsEnumerable Method.

dt_sampleData.AsEnumerable()

The AsEnumerable Method thaws our DataTable into a collection of DataRows, which the Methods can easily fuse itself to.

dt_sampleData.AsEnumerable().ToList()

This really doesn’t do much other than convert our DataTable into a List of DataRows. Also, List is an extension of the IEnumerable class, which means you can think of this list of DataRows as an IEnumerable of DataRows.

You don’t have to memorize any of this. All you have to understand is that once we fuse an AsEnumerable Method to our DataTable, we will have an ice block in pristine condition ready for sculpting.

dt_sampleData.AsEnumerable()…Now What?

Yeah, Now What?

Another thing to keep in mind, is you don’t have to memorize all the extensions.

The extensions are available once you invoke intellisense by pressing Ctrl+Space, and besides, you won’t use all of them.

That being said, there are a handful of Methods you ought to familiarize yourself with, like Where, Select and GroupBy.

There are the most important ones, and you will perform most of the magic with them.

Where lets you filter data, Select lets you project data like you were folding it into origami, and GroupBy requires an article to describe become there is no way to construct a one line sentence to describe what GroupBy does in layman terms.

Well you can, but it won’t make any sense.

GroupBy lets you group data.

To solidify whatever we have learned to far, we will now look at one incredibly simple operations using Where.

Where?

If you haven’t downloaded the workflow I had painstakingly put together, then go and do it right now or else I will keep up with the emotional blackmail.

You will find a Spreadsheet titled “DataToPlayAroundWith” and you’d better do as the name suggests or I will be very disappointed.

Any excel file will do, but the workflow references that particular spreadsheet, which is why it’s better if you go through that itself.

Inside the excel, there is a column called “Genre”.

There are some records which fall under the category of “Crime” which you’d like to retrieve.

Maybe you’ve always liked watching criminal masterminds at work, and made it your life’s mission to disappoint your parents by becoming one.

Think about this for a while, how would you normally go about filtering data?

You’d probably use a For Each, nest an If Condition to check if Row(“Genre”).ToString.Equals(“Crime”) and assign that row to a new DataTable.

If you’re smart, then you’d probably go for the Filter DataTable Activity and get the work done there.

This is fine, but what if the dataset is large? The Filter DataTable Activity still does a pretty good job, so you don’t necessarily have to use it in situations where you are only filtering.

Where starts to shine when the filtering is a bit more complex, like when you want to perform some sort of a regex check, or apply conditions involving other columns in ways the Filter DataTable Activity can’t account for.

dt_sampleData.AsEnumerable().Where()

Where can’t operate without any parameters. The Where houses a Predicate which loves to munch on Boolean values, so it won’t operate without one.

But before that, you have to declare a Lambda Function, which is a function without a name.

He is a little shy, and would like to remain anonymous.

dt_sampleData.AsEnumerable().Where(Function(w))

Now lets give our hungry predicate something to chew on.

dt_sampleData.AsEnumerable().Where(Function(w) w(“Genre”).ToString.Equals(“Crime”))

But is this really ok?

What if there are some extra spaces, or if some of the genre are not in proper case?

“CriMe” is the same as “Crime” so we have to account for that as well.

dt_sampleData.AsEnumerable().Where(Function(w) w(“Genre”).ToString.Trim.ToLower.Equals(“crime”))

I guess we are done…or are we?

Sure, we have sculpted our block of ice to perfection, but shouldn’t we preserve it now?

We have to freeze it back into a DataTable to preserve the changes we made, and that is done by fusing a CopyToDatatable Method to it.

dt_sampleData.AsEnumerable().Where(Function(w) w(“Genre”).ToString.Trim.ToLower.Equals(“crime”)).CopyToDataTable()

But What If I Wanted The First Three Rows?

The Take Method would be the most appropriate for that, but if you really want to use Where, you can achieve that as well.

dt_sampleData.AsEnumerable().Take(3).CopyToDataTable() //Preferred
dt_sampleData.AsEnumerable().Where(Function(w,i) i<3).CopyToDataTable() //Not Preferred

But lets be realistic for a moment, if we are going to apply a filter and want the first three rows, obviously it has to be the first three records from the Filtered Collection, right?

dt_sampleData.AsEnumerable().Where(Function(w) w(“Genre”).ToString.Trim.ToLower.Equals(“crime”)).Take(3).CopyToDataTable() //Most Likely Scenario

And that’s how LINQ works.

If you don’t get it right away, that’s ok.

Like with anything worthwhile, you have to keep working with it.

Persist, and it will eventually reveal its secrets to you.