Now that you are familiar with both Lambda and Query Syntaxes, we will explore the methods one by one – or at least the ones I believe are incredibly useful, starting with Where.

Filter With Ease

Using Where, you can easily filter collections.

If you are trying to filter DataTables, you are better off using the Filter DataTable Activity as it gets the work done quick, however, there are situations which the Filter Activity simply can’t account for.

It isn’t capable enough to perform complex conditional operations like merging two column value and checking if the values comply with a given condition.

Also, you can’t use the Filter DataTable Activity with Lists, Arrays, or any data collections.

This is where Where comes into the picture.

Where?

Like always, lets look at a simple operation first.

The Spreadsheet located in the Data Folder contains a column called “Release Date”.

We will try to retrieve movie records which were released after 1^st of January, 2001.

When dealing with dates, you have to convert the strings into dates because “01/01/2001”< “01/10/2002” is nonsensical.

For the most part, the convert class is all you will ever need, but if you have to be really specific about the date format, like if it is in non-English format then you have to assign a CultureInfo like so:

Datetime.ParseExact("12 Mars 2010","dd MMMM yyyy", _
System.Globalization.CultureInfo.CreateSpecificCulture("fr-FR"))

We won’t be needing any of that here, but I wanted to show you that possibilities are endless when you learn a bit of coding.

//Query Syntax
--------------
(From row In dt_sampleData.AsEnumerable()
Where Convert.ToDateTime(row(“Release Date”)) > New DateTime(2001,01,01)
Select row).CopyToDataTable()

//Lambda Syntax
---------------
dt_sampleData.AsEnumerable().Where(Function(w) _ 
Convert.ToDateTime(w(“Release Date”)) > New DateTime(2001,01,01)).CopyToDataTable()

Do note, Where returns ALL records which meet a specific condition. If you want to retrieve select column values which meet a given condition, you have to chain a Select to the filtered collection.

Select is for another day, today we will focus on Where.

Link The Methods

Methods can be chained one after the other, and it can be done with the same operation.

It might not make much sense to use the same operations over and over again, instead of getting everything done in a single operation.

Stuffing all your checks and operations into a single Where is possible, but not recommended. It has nothing to do with performance, but more to do with readability.

//Query Syntax
--------------
(From row In dt_sampleData.AsEnumerable()
Where Convert.ToDateTime(row("Release Date")) > New DateTime(2000,01,01)
Where Convert.ToDateTime(row("Release Date")) < New DateTime(2005,01,01)
Select row).CopyToDataTable()

//Lambda Syntax
---------------
dt_sampleData.AsEnumerable() _
.Where(Function(w) Convert.ToDateTime(row(“Release Date”)) > New DateTime(2001,01,01)) _
.Where(Function(w) Convert.ToDateTime(row(“Release Date”)) < New DateTime(2005,01,01))).CopyToDataTable()

“But I Don’t Want to Chain Methods.“

Alright then, you can chain conditions instead using And/Or AndAlso/OrElse operators.

Not many are familiar with the AndAlso/OrElse operators, so lemme explain what they are.

Unlike the And/Or operators, the AndAlso/OrElse operators enhance performance by short circuiting.

Lemme explain that as well, when you compare two or more branches of logic, the compiler will execute all branches of logic before arriving at a conclusion.
This of course happens within a matter of milliseconds or less, but as the branches of logic grow in complexity, it negatively affects performance.

The AndAlso and OrElse breaks out as soon as it evaluates a condition to False and True respectively.

IsNumeric(“1300”) OrElse 
IsNumeric(“1354”) OrElse 
Not IsNumeric(“TCT”)
//The LHS is validated and the OrElse short-circuits and returns true.

IsNumeric(“1300”) AndAlso 
IsNumeric(“1354”) AndAlso 
IsNumeric("TCT") AndAlso 
Not IsNumberic("Blogging")
//The AndAlso short-circuits as soon as it encounters a condition which evaluates to false

Now that we know how the short-circuiting operators do their magic, its time for us to start casting some spells!

//Query Syntax
--------------
(From row In dt_sampleData.AsEnumerable() Where 
Convert.ToDateTime(row("Release Date")) > New DateTime(2000,01,01) AndAlso
Convert.ToDateTime(row("Release Date")) < New DateTime(2005,01,01) AndAlso
Convert.ToString(row("Genre")).Trim().ToLower().Equals("comedy")
Select row)CopyToDataTable()

//Lambda Syntax
---------------
dt_sampleData.Asenumerable().Where(Function(w) 
Convert.tOdateTime(w("Release Date")) > New DateTime(2000,01,01) AndAlso
Convert.ToDateTime(w("Release Date")) < New DateTime(2005,01,01) AndAlso
Convert.toString(w("Genre")).Trim().ToLower().Equals("comedy")).CopyToDateTable()

As the operation becomes complex, it makes sense to rely on Query Syntax instead as you can assign values to local variables which improves the readability.

//Query Syntax
--------------
(From row In dt_sampleData.AsEnumerable()
Let rowrelDate = Convert.ToDateTime(row("Release Date"))
let rowGenre = Convert.Tostring(row("Genre")).Trim().ToLower()
Where rowRelDate > New DateTime(2000,01,01) AndAlso
rowRelDate < New dateTime(2005,01,01) AndAlso
rowGenre.Equals("comedy")
Select row).CopyToDataTable()

And with that you have your sandwiched all your conditions into a single clause.

In Conclusion

Where is pretty simple to work with.

It comes with a predicate which consumes Boolean values, which is why we have to encase conditions within it.

The example below throws an error since there are no juicy Boolean values for the predicate to chow on.

dt_sample.AsEnumerable().Where(Function(w) Convert.Tostring(w("Genre"))).CopytoDataTable()
//The Where is incomplete without a condition

The right method to use here would be Select and we will explore that in the coming days.