Where Operator Part 3

Where operator is a restriction operator that allows you to filter collection of objects if the collection implements IEnumerable. If the collection happens to implement IQueryable, where operator gets translated to equivalent SQL server operator and the filter gets applied on SQL server. When using where operator you provide a Func delegate that  returns the element if it matches the condition passed in as a lambda expression.

public static IEnumerable<T> Where<T>(
this IEnumerable<T> source,
Func<T, bool> predicate);

Above code represents the prototype for the Where operator. Looking at the prototype, we can see that it is an extension that is visible to any object that implements IEnumerable<T>. The second argument to the where operator takes in a predicate that gets applied on every element being iterated in the collection. Only when the predicate returns a value of true does the element gets counted as being part of returned sequence. The predicate takes an argument of T which is the type of element source collection consists of. When you are applying a where clause the number of elements can never be more then the source collection and the objects are not transformed in any form or fashion.

The second prototype for the where operator looks like this

The Second Where Prototype
public static IEnumerable<T> Where<T>(
this IEnumerable<T> source,
Func<T, int, bool> predicate);

The only difference it has from the first method is the predicate takes an additional argument which is the index of the element in the source collection.

Lets walk through some examples of where operator to see where we use the where operator.

In the above example, I am filtering the collection of integers to return only integers that are even numbers by passing the lambda expression which only evaluates to true when a certain number returns a remainder of 0. You can write the same query using query expression instead of writing it using the operator syntax. Here is how the example would look like.

The query syntax is much easier to read than the method syntax but then both yield the same result.

Lets modify the above query to use the second version of the where operator which has an additional  argument of integer which is the index of the element in the source sequence. Since the second version of the where operator cannot be represented using the query syntax, we have to resort to the traditional method syntax to achieve the objective. Lets look at an example of that.

Looking at the above example you would notice that our lambda expression also makes use of the index of the element to only return elements which fall within the index of 0-3 and are even numbers.

Another interesting point that I discovered recently is where operator can appear more than once in a query. Lets look at an example of that.

From the above example, you would notice that I am applying the where clause twice, first to show numbers greater than 3 and then to retrieve numbers that are only evens.

Not only can you use where operator with linq to objects, but as mentioned earlier where operator can also be used in linq to SQL queries to apply filtering on the database side. I will drill through few examples to illustrate usage of where operator using northwind database.

In the above example to illustrate how the where operator gets converted to SQL, I am making use of the log property to log all the statements to the console window. You would notice that our where operator gets converted to where  in SQL with equal to. But what if wanted a behavior of where Like instead of exact matching. Well, there are number of CLR methods that gets converted to like operator. The one that I found that gets interpreted as like are contains, StartsWtih, EndsWith  and CompareTo. Lets look at an example to see the usage.

In the above example we are making use of contains, StartsWith and EndsWith method in .net which gets translated to SQL. Look at the SQL statement generated we can observer that contain operator have a list of two cities gets converted to in operator to select from a list of city. However StartsWith and EndsWith operator gets converted to Like operator with % passed in along with the value.

If the above method does not give you full control over the where clause generated by linq to sql, You can use SqlMethods.Like operator to exactly specify the like clause.

I wont be covering SqlMethods, since its a topic in itself that warrants a blog post. However I will demonstrate a small example that would show its usage in query syntax and the control it provides in generating the like statement that you desire.

From the above example, you would notice that we have full control over where we can put % sign to dictate in how much close proximity do we want our matches to be.