Multiple .Where() clauses on an Entity Framework Queryable

Question

I am trying to implement a complex filter using Entity Framework: I want to add where clauses to the queryable object based on my provided search criteria.

Can I do the following in Entity Framework 6?

var queryable = db.Users.Where(x => x.Enabled && !x.Deleted);
// Filter
var userId = User.Identity.GetUserId();
queryable.Where(x => x.AspNetUser.Id == userId);
queryable.Where(x => x.Status >= 2); 
// ...etc

I know that I can do:

var queryable = db.Users
 .Where(x => x.Enabled && !x.Deleted)
 .Where(x => x.AspNetUser.Id == userId)
 .Where(x => x.Status >= 2);

But I am not getting the expected results with this solution. It seems to be ignoring the seconds two where clauses.

You need to assign the result, something like: `queryable = queryable.Where(x => x.AspNetUser.Id == userId);` By the way, you can use `and (&&)` and avoid writing multiple `where` clauses. — Akbari, Jan 02 '16 at 03:46

score 17 · Accepted Answer · answered Jan 02 '16 at 03:54

17

.Where returns a queryable, it does not modify the original. So you just need to save the new queryable.

var queryable = db.Users.Where(x => x.Enabled && !x.Deleted);
// Filter
var userId = User.Identity.GetUserId();
queryable = queryable.Where(x => x.AspNetUser.Id == userId);
queryable = queryable.Where(x => x.Status >= 2); 
// ...etc

answered Jan 02 '16 at 03:54

Vlad274

6,514
2
32
44

4

That's fine, but builds an AND clause. It's not quite straightforward to create multiple OR conditions. – Aaron Hudon Nov 13 '21 at 22:41

score 2 · Answer 2 · answered Jan 02 '16 at 04:00

I guess what you are trying is runtime search. If that is the case you have two options.

Using expressions to build a run time where clause. You may have to use a predicate builder. This predicate builder may not work with Entity Framework. But this one should work with entity framework.
Using Dynamic Linq. This by far the easiest. Its very versatile. Not only can you do Where, it supports select and order by and others as well runtime quite easily!!! Do take a look.

Multiple .Where() clauses on an Entity Framework Queryable

2 Answers2

Linked

Related