Where clause not working on LINQ IQueryable query

Question

Using Linq to Entities, the where clause is not working when I do a Where lamba expression on a Linq object created with a written LINQ statement.

This does not work. Query executes, but results are returned unfiltered.

var myQuery = (from l in db.MyTable
                     select l);
myQuery.Where(r => availableStatusList.Contains(r.Status));
var myObj = myQuery.ToList();  

This does work. Query executes, AND results are returned filtered properly.

var myQuery = (from l in db.MyTable
                     select l).Where(r => availableStatusList.Contains(r.Status));
var myObj = myQuery.ToList();

As far as I understand, both of these should return equivalent results. Any reason why the first one does not listen to the Where clause?

Answer 1

Shorter answer:

Similar to a string mutator function, you need to assign it back to the variable:

string sample = "text";

// this doesn't change sample
sample.ToUpper();   

// this one does
sample = sample.ToUpper();

So what you need is:

myQuery = myQuery.Where(...)

Answer 2

A Where clause does not create a new object when invoked; it applies a filter to an existing collection by wrapping the collection in a filter that will eventually be run. However, since it is a pure function, the return value of that filter needs to be returned to the original reference myQuery, which is why the second example works...you have chained the result through the Where() clause back to myQuery. You return the filtered collection back, materializing the deferred query with ToList().

In the first example, you are assuming that the filter is applied directly to the collection, but that isn't how LINQ works, since the filter doesn't modify the collection immediately when the function is called. Rather, it only applies a predicate that WILL be applied provided it is "attached" to the original Queryable collection and you resolve the query with ToList().

It is sometimes easier to think about deferred execution as a series of promises.

If I give you a dollar, you have a dollar. If I take 25 cents for taxes, I've immediately resolved our transaction (query).

However, if I promise to give you a dollar on Tuesday, I've returned a Queryable<T> promise.

Multiple events could occur however between now and Tuesday. I could chain a filter for taxes (25 cents), a filter for a stick of gum (25 cents), or any other filter.

However, our bookkeeping system has a caveat. We can't just call .Taxes() (our Where clause) on the total promised amount and expect it to update. We have to record our transaction against the promised amount by returning the filter to the original variable, updating it with the transaction that has occurred against our promised amount.

myQuery = myQuery.Where(condition);

On Tuesday, when you come to collect your payment (by calling ToList() on the series of chained promises/filters), I deduct what has occurred from what was promised, resulting in a 50 cent payout. That's deferred execution at work.