If you know how to order items in a DataTable by a given column, then you are probably looking for something a little more advanced.

But really, how much more advanced can we get with this? Why would I want to order it any more than it already is?

There are times when the OrderBy is not specific enough to order the items in the way you want it to. We will look at one such example in this post.

Example Time!

Here’s a report of the people working under you, the types of leave they have availed and how many.

You want to know which employees have taken more Casual Leaves than they were supposed to (because you are a dictator), but don’t want to use too many operations.

You have decided to take it up as a challenge, because even though the query below gives you what you want (kinda), you still wish to improvise.

Dt_Data.AsEnumerable.Where(Function(w) w(“Leave Type”).ToString.ToLower.Trim.Equals(“leave type”) And CInt(w(“Leaves Taken”).ToString.Trim) > More?).CopyToDataTable

Maybe you want to see how many employees have availed Annual Leaves.

Maybe you also want details of the rest to make comparisons.

Or maybe you simply want to know what your team is up to.

You know what that means?

It means, you can’t use Where.

Where does an excellent job of filtering, which translates into exclusion. It snips off the excess and gives you the items you want to focus on.

But that is not all.

Where requires you to be specific. “More” is not an integer Where can work with, also we don’t to snip anything off here, so we have to find a way to Order it on the basis of phrases.

But is that Even Possible?

We are about to find out.

Lets see if OrderBy and its cousin OrderByDescending can help us out here.

The query has ordered the items alphabetically. This is not what we wanted, since we wanted.

How about ordering it on the basis of the character length?

So, is it even possible to order a DataTable based on phrases?

Well, I wouldn’t be writing this if it weren’t possible, now would I?

You Need an Array First

Since OrderBy is only smart enough to sort items either alphabetically or based on length, we have to think out of the box.

Lucky for you, I have done my homework, and thanks to Yoichi-san, I was able to find a proper solution (translation: Yoichi-san provided the solution).

You read the title, so you probably have an idea of what we are about to dive into.

But Why an Array?

Array has an extension which lets us retrieve the index of a particular item present in a given array.

Take this array for example:

{“Second”, “First”} //Store this into arr_Phrases

Array.IndexOf(arr_Phrases, “First”) //returns 1
Array.IndexOf(arr_Phrases, “Second”) //returns 0

If it doesn’t find the element, it returns -1.

Array.IndexOf(arr_Phrases, “Third”) //returns -1

Using this, we can arrange our DataTable based on a selection of phrases.

So lets declare our Array!

Next, lets populate our array with values. We want “Casual Leave” takers to come first, followed by “Annual Leave” and then the rest.

Yes, yes, Casual Leave has to come first, followed by Annual…and that is exactly what is going to happen.

Lemme explain.

If you remember, the output of our Array.Index also contains a -1 if the item is not found, so if we order by ascending, that means -1 will always come first.

If all of that went over your head, then don’t worry, it went over mine when I first started learning it as well.

Here, have a closer look at the output:

As you can see, I have only provided two values into my Arr_Phrases, and anything that is not present in that array returns -1.

OrderBy method orders items in ascending by default, so for us to get the items in the way we want it to be arranged, we will have to Order it By Descending.

This also means, we have to Order the items in our Array in opposite for it to work as its supposed to.

Kinda confusing I know, but read through this article few more times and it will eventually click. I would recommend practicing it though, instead of reading through and hoping your grey matter will retain everything.

Using the below LINQ, we may order our DataTable the way we want to:

Dt_Data.AsEnumerable.OrderByDescending(Function(o) Array.IndexOf(Arr_Phrases,o("Leave Type").ToString)).CopyToDataTable