LINQ is complex enough by itself, which is why I decided to kick it up a notch and torture you with some more.

Pain and suffering are the catalyst for success, and I use this platitude each time to gaslight people when they ask me why I like spending hours in front of an illuminated screen, creating content most people are never going to read.

I happen to like writing, and wasting spending time in front of my illuminated screen.

How Did I Learn LINQ?

There are no courses online that teach you how to adequately develop LINQ.

There are few YouTube videos that explore LINQ, but they at best, impart basics, and basics will only get you so far. You have to immerse yourself and absorbing as much of that advanced stuff as you can, before you can even think of applying any of it.

I repeat, a shallow understanding of the subject won’t get you anywhere.

But I digress.

How did I learn LINQ?

By stalking people on UiPath forum.

I learned a lot, and owe a great debt of gratitude to ppr and Yoichi-san for sharing their invaluable knowledge online.

Going through ppr’s solutions has taught me a lot about LINQ, and it would only be fair if I plagiarized some of his solutions for my blog posts.

Yoichi-san on the other hand, is a Regex wielding wizard, and pretty darn proficient with LINQ as well, but we will limit ourselves to ppr’s solution for today.

Brace Yourselves.

Imagine you come across this sort of Monstrosity.

And afterwards greeted by his son, Timmy.

Here is What You Have to Do

Have you had a look at Monstrosity? He has a Results Column which isn’t currently populated with data.

We will delete that Column manually from the Excel Workbook, as it will interfere with our process.

Timmy holds data which will fill up the Result Column.

When we process the first instance of that row, it has to be marked with its respective code.

If any of the rows are duplicated, then the Results Column for that particular row has to be marked as “Tabulated”.

Earlier we had explored an approach which combined the use of a Dictionary variable and a LINQ Query to achieve the desired outcome.

Timmy’s Table makes an excellent candidate for conversion.

If you remember, you don’t necessarily have to rely on oonga bunga alternatives for storing Timmy’s Table into a Dictionary. Instead of relying on prehistoric For Each Activities, we are going to fast forward into the future and use some advanced civilization stuff to get what we want.

TimmyTable.AsEnumerable.ToDictionary(Function(k) k(0).ToString, Function(v) v(1).ToString)

But if that was your approach, then you clearly weren’t paying attention.

Timmy’s table contains more than two columns, and if you had done your homework, then you’d know by now that a Dictionary cannot contain Duplicate Keys.

How do we rectify this?

By concatenating the first two columns.

The reason we have selected the first two columns, is because those aren’t the values which are ultimately going to be fed into the Results Column present in Monstrosity Table.

The Status Column is what we want, which is why it becomes the Value, while the concatenated columns becomes the Key, both of which will end up in our Dictionary Variable.

TimmyTable.AsEnumerable.ToDictionary(Function(k) k(0).ToString & k(1).ToString, Function(v) v(1).ToString)

Now that we have our Dictionary ready, we have completed the first step.

Step Two: Retrieve a List of Column Names

There is a reason for this.

Remember what we did earlier? We concatenated the first two Columns before adding them to our dictionary because they weren’t unique themselves.

If you were to just peer back at Monstrosity, and the values that ought to go into the Results Column, I think you are slowly starting to see what it is we are trying to achieve here.

No, I Don’t. Everything Scares Me.

Great!

Then you will love this.

Before we head into the main part of our solution, there is one last prerequisite.

We need the Column names, to create “collections” that we can uniquely identify, so that we may fill in data appropriately.

We can hardcode values into an Array, but I don’t like any of that hardcore stuff.

Let’s handle it professionally, by coming up with a Query that pulls out the Column names from our DataTable.

DtData.Columns.Cast(Of DataColumn).Select(Function(s) s.ToString).ToArray

This is fine and dandy, but say for example we didn’t wish to include few columns from our DataTable, how would be go about achieving that?

You simply have to append .TakeWhile Method, but you have to know when to use it.

The TakeWhile isn’t a filter – it goes on taking as long as a condition is met, before short-circuiting. If you want to check the condition for all elements in a collection, then I would recommend using the Where Method instead.

I wanted to use the TakeWhile Method just to show you that something like that exists.

DtData.Columns.Cast(Of DataColumn).Select(Function(s) s.ToString).TakeWhile(Function(tk) Not tk.ToString.Equals(UnwantedColumnNameOrIndex)).ToArray

Here is a fancy Illustration I came up with to draw your attention away from the horror you had to endure.

Spoiler Alert: Step Three Is Not Going to Go as Smoothly

I was thinking of converting this into a Method Syntax, but that would shave way too many years off my life.

You are about to see the LINQ in its entirety.

You won’t understand a damn thing.

You might recognize few things here and there and maybe even piece things together, and if you are capable of doing that then why waste your time here?

You’re already a LINQ wizard.

This article is for those who weren’t blessed with super powers.

Warning: LINQ Can Hurt Your Eyes, Either Wear Sunglasses or Go Get Yourself an MBA

(From d In Dt_Monstrosity.AsEnumerable
Group d By k = String.Join(String.Empty,Arr_Columns.Select(Function(s) d(s).ToString).ToArray) Into grp = Group
Order By Dt_Monstrosity.Rows.IndexOf(grp.First())
Let res = Dict_Timmy(grp.First()d(“Type”).ToString & grp.First()d(“Value”).ToString)
Let ral = grp.Select(Function(s,i) s.ItemArray.Append(If(i=0, res, “Tabulated”)).ToArray).ToList
Let drl = ral.Select(Function(s) Dt_Result.Rows.Add(s)).ToList
Select drl).Count

Yes, that is a lot to take in.

It took me a while to wrap my head around it as well.

If you think this is a little too advanced for you, then head over to the other articles I’ve written on LINQ, before returning to this.

The other option is…

This monstrosity won’t be decoded in this article.

We have already crossed the 1000-word cross mark, which is usually when people start to lose interest and click away.

Tune in next week, for the solution break down, where you too, will break down.