Making sweet, sweet progress.
Not only are you away of the two syntaxes with which you can construct LINQ, you also know how to filter and project data into whatever shape you want!
Now it’s time to up your game by learning how to order data, but first here is something that I have to address.
It’s a trap most of us end up falling into.
Don’t Order Me Around!
No one likes being ordered around, unless the person dishing out orders actually knows what he is doing.
Most managers simply give orders, instead of distributing the load appropriately, because they don’t even know what the heck they are dealing with.
Project managers are especially guilty of this, since they are expected to only handle client interactions, without paying too much attention to the technical aspects of the product.
To be fair, it isn’t necessary for them to understand the technical aspects, but it is important for them to consult with senior developers, technical leads or solution architects before deciding on timelines and how many developers will be required for that particular interval, which they don’t.
Having the authority to manipulate people can be likened to having the knowledge to manipulate data. It’s easy to use once you get a hang of it, but becomes a problem when you rely on that alone to get the job done.
Likewise, don’t rely on just these techniques being described here to complete your tasks.
There are built in activities which you can rely on, such as the Sort Table Activity, Filter DataTable etc. LINQ must only be used for complex scenarios involving large datasets.
Using it everywhere leads to needless complication.
Not everyone understands LINQ, and whatever processes you develop won’t be easy to maintain.
If you are going to use them, make sure you add as much details in the log or as annotations so that the developer responsible for maintaining your process won’t end up in a mental asylum after seeing your workflow.
That ‘Analogy’ Of Yours Was Just To Get Back At Your Managers Wasn’t It?
Let us proceed!
Basic OrderBy Method
Order By arranges data in ascending order by default.
Just like with Where, the result of an OrderBy Method can be directly sandwiched back into a DataTable.
dt_sampleData.AsEnumerable().OrderBy(Function(o) o(“Release Date”)).CopyToDataTable()
This seem to arrange the items as expected, and that is because the row type in excel itself recognizes it as a datetime value.
If it were stored as text, then you have to parse it accordingly like so:
dt_sampleData.AsEnumerable().OrderBy(Function(o) Datetime.Parse(o(“Release Date”).ToString)).CopyToDataTable()
Its always better to stay on the safer side and perform these conversions, especially when working with dates.
Some might be in formats that the Date.Parse or Convert.ToDateTime methods won’t be able to parse. In situations like those, it would make sense to rely on the DateTime.ParseExact Method.
DateTime.ParseExact("23/12/2022", "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) //The format has to be specified, which can be a string or an array of strings
Out of The Box Ways to Order
Instead of ordering items by the row items, we can also order items by the properties of those row items i.e., such as its length.
This might not seem as out of the box as you thought it would be, but it can come in handy sometimes.
dt_sampleData.AsEnumerable() _ .OrderBy(Function(o) o("Title").ToString.Length) _ .Select(Function(s) s("Title").ToString).ToList()
What if I simply passed in the string?
What would happen then, is that the dataset would get ordered alphabetically.
If the string contains starts with number(s), then the numbers are placed ahead of the strings that start with alphabets.
dt_sampleData.AsEnumerable() _ .OrderBy(Function(o) o("Title").ToString) _ .Select(Function(s) s("Title").ToString).ToList()
Order In Reverse
The Order By Method orders data in Ascending Order, but what if we wanted to achieve the exact opposite?
We have to Order By Descending, and remove the spaces wink* wink*.
dt_sampleData.AsEnumerable() _ .OrderByDescending(Function(o) Convert.ToDateTime(o("Release Date").ToString)) _ .Select(Function(s) Convert.ToDateTime(s("Release Date").ToString)).ToList()
Pretty neat isn’t it?
Wait until you see the next section.
Order By This, Then By Another
You may also order the dataset by multiple columns.
There is a Then By Method which you can use to achieve that but remember, the Then By Method must be preceded by an Order By Method.
I don’ t think I need to explain why (c’mon, just think about it for a while).
dt_sampleData.AsEnumerable() _ .OrderByDescending(Function(o) Convert.ToDateTime(o("Release Date").ToString)) _ .ThenBy(Function(tb) tb("Title").ToString.Length) _ .Select(Function(s) s("Title").ToString).ToList()
Before We Wrap Things Up
Let us explore few Query Syntaxes.
With Order By in Query Syntax, you can pass in a Keyword (Ascending/Descending) to order it in the way you want it to.
//Order By Descending (From row In dt_sampleData.AsEnumerable() Order By Datetime.Parse(row("Release Date").ToString) Descending) _ .CopyToDataTable()
//Order By Ascending (From row In dt_sampleData.AsEnumerable() Order By Datetime.Parse(row("Release Date").ToString)) _ .CopyToDataTable()
When you have to order by Multiple Columns, you have to place the column names in the order of importance(which column it should order by first before moving onto the next) and the column names have to be separated by commas.
(From row In dt_sampleData.AsEnumerable() Order By Datetime.Parse(row("Release Date").ToString) Descending , row("Release Date").ToString Ascending).CopyToDataTable()
Now To Wrap Things Up
It might not be apparent just where exactly all of this can be applied, but that is not the point of this article.
What you need right now is exposure.
When you expose yourself to ideas, it leaves an imprint on your mind.
That imprint will sprout into a network of impressions which trains your intuition. This is how you learn, by exposing and giving yourself the space and time to work through it.