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?
So if you working with only in-memory data collection IEnumerable is a good choice but if you want to query data collection which is connected with database `IQueryable is a better choice as it reduces network traffic and uses the power of SQL language.
Linq Namespace. Both IEnumerable and IQueryable are forward collection. Querying data from a database, IEnumerable execute a select query on the server side, load data in-memory on a client-side and then filter data. Querying data from a database, IQueryable execute the select query on the server side with all filters.
Introduction to LINQ Include. LINQ include helps out to include the related entities which loaded from the database. It allows retrieving the similar entities to be read from database in a same query. LINQ Include() which point towards similar entities must read from the database to get in a single query.
The major difference between IQueryable and IEnumerable is that IQueryable executes query with filters whereas IEnumerable executes the query first and then it filters the data based on conditions.
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.
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(...)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With