If last week’s article didn’t send chills down your spine, then you clearly aren’t human.
It took me approximately 4 days to wrap my head around that query, before forgetting who I was, where I was, and what I was doing.
Bad jokes aside, it did take me a while to understand this, and it will for you as well.
You won’t understand most of what you are about to read, and that is okay.
You will find yourself revisiting this article several times, before you are capable enough to visualize the end to end flow of logic.
Also, this article isn’t going anywhere. Its going to remain up forever…or until WordPress decides it has had enough of me and boots me off its platform.
WARNING: You Have Been Warned.
(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
Lets break this down line by line.
From d In Dt_Monstrosity.AsEnumerable
This translates to, for each row item in Dt_Monstrosity.
C’mon, you know this don’t you?
Group d By k = String.Join(String.Empty,Arr_Columns.Select(Function(s) d(s).ToString).ToArray) Into grp = Group
You are grouping the collection on the basis of…its better if I show you an illustration instead.
Here is a short and sweet explanation.
The Row Items pertaining to each column (obtained from our Arr_Columns) is Joined to form a string.
Its on the basis of this string, that the entire collection is “Grouped”.
Note: we aren’t altering the collection. The collection is merely being grouped on a given basis, while the collection items retains its structure.
The rows aren’t concatenated, its remains as it is.
Obstacle one is down, now let’s move onto the rest.
Order By Dt_Monstrosity.Rows.IndexOf(grp.First())
This snippet of code orders our to-be collection as per the original collection it is coming from.
Sometimes after grouping, the collections have a tendency to become jumbled up. This is done to rectify that.
You remember Timmy, don’t you?
Let res = Dict_Timmy(grp.First()d(“Type”).ToString & grp.First()d(“Value”).ToString)
Let is what we use in VB.Net to assign values to variables.
res in this case, is a temporary variable, which will store the Value being pulled out from Dict_Timmy.
What is With The grp.First()?
We have grouped the collection into an Igrouping which is a collection of sub-collections.
We cannot use the Igrouping as key to pull out our value from Dict_Timmy.
But we can’t pull out the value we want unless we reference the row item.
That is why we are pulling out the first row item from each group, and using that to pull out the value we want from Dict_Timmy.
I’m glad you made it this far without losing your mind.
We are almost through, just two more steps and we are out of this mess.
Let ral = grp.Select(Function(s,i) s.ItemArray.Append(If(i=0, res, “Tabulated”)).ToArray).ToList
This bit gave me a really hard time.
Up until then, I was familiar with basic Select and Where operations, so when I came across this, I decided to call it a day and cry myself to sleep.
I woke up next morning, and still couldn’t figure it out. Proceeded to cry myself to sleep that day as well.
It eventually clicked one day.
I was starting to see the bigger picture, and in that excitement, I decided to decipher the last bit, but I couldn’t so I had to cry myself to sleep once more.
The Logic Behind It All
Remember, we had to mark the first row item in each group with its respective status, while the rest(duplicates) had to be marked as “Tabulated”.
This particular Function helps us achieve that.
While s stores each item in the collection from grp, the i behaves as an iterator.
When processing the first group, s stores each element while i goes on incrementing, until it reaches the end of the first group, after which the value in i is reset.
But that’s not all.
We are also appending the new result to the existing collection, which is why I specifically mentioned that we will be deleting the Result Column from the Worbook.
If we keep the column, you will end up with an exception which states that the array size is longer than the Data Table.
This is why we add the Result Column using the Add DataColumn Activity, which stores the values as null, as opposed to directly fetching it from the excel file.
That will take a while to sink in so relax.
Give it some time.
Last But Not The Least
The part that frustrated me for quite a bit, before I decided to head over to the solution and bang myself over the head for not reading the description.
Let drl = ral.Select(Function(s) Dt_Result.Rows.Add(s)).ToList Select drl).Count
@ppr had detailed that the .Count Method was used as a silly trick.
The data had already been added to the DataTable in this step, so there isn’t a need to assign it to a DataTable once more.
But we are using an Assign Activity, which doesn’t like it when we provide an expression that doesn’t produce a return value, which is why this silly trick has been employed.
This wasn’t a proper deconstruction, but it has shown you just how powerful LINQ can be.
Imagine trying to automate this using For Eachs and If Conditions.
Think of the madness that would ensue.
Think of the time you‘d waste fitting things together.
But you don’t have to worry about any of that, now that you are LINQing things together, like a developer ought to.