entity framework: conditional filter

Question

Let's say I have Customers table and I want to filter it by the following:

Country: All, US, UK, Canada
Income: All, low, high, medium
Age:All, teenager, adult, senior

if I had to build an SQL string for this filter, it would be something like this:

if (Country != "All") sql += "country = " + Country
if (Income != "All") sql += "and income = " + Income
if (Age != "All") sql += "and age = " + Age;

So, basically, the user can filter by some, but not necessary all fields.

How do you do this using Entity Framework ?

Thanks !

Agree. Done. Thanks for the reply. PS: I am originally from Minsk too. — David, Jul 13 '12 at 14:32

score 28 · Answer 1 · answered Jul 13 '12 at 07:27

28

LINQ to Entity queries return IQueryable's, so you can build your query this way:

IQueryable<Person> query = context.People;

if (Country != "All")
{
    query = query.Where(p => p.Country == Country);
}

if (Income != "All")
{
    query = query.Where(p => p.Income == Income);
}

if (Age != "All")
{
    query = query.Where(p => p.Age == Age);
}

List<Person> fetchedPeople = query.ToList();

This case is almost too simple, but this is very helpful in more complex situations when you need to add filtering dynamically.

answered Jul 13 '12 at 07:27

Yakimych

17,612
7
52
69

1

I've used this method for a long time, I just profiled the resulting query and it appears adding filtering in this way, query = query.Where, doesn't impact the SQL, and the filtering ends up being done in memory after rows are returned. In this example, since the initial query is just context.People, the database will return the whole People table on ToList(), and then EF filters it in memory. – TechnoCore Apr 04 '19 at 17:16
I haven't used EF for a long time, but this sounds somewhat weird and rather unlikely. C# does not have a way to "remember" the old value of the `query` variable, and in this example it gets overwritten by a new one, which contains the filter. Is there any difference if you use a new variable name instead? Such as filteredQuery? Or (for comparison) have you tried creating a query with all the filters applied from the start (rather than conditionally) and profiling? I would guess the result would be the same in all 3 cases. – Yakimych Apr 04 '19 at 19:28
I profiled filters applied in the defining line and filters applied later. The generated SQL is very obvious, it only has WHERE clauses that are in the defining line. I agree that it cannot "remember" the original query from the defining line, but it does not expand the new filter into the original, it just waits to get the rows back and filters after. This is why people use things like PredicateBuilder or LINQKit. – TechnoCore Apr 05 '19 at 18:03
My guess is that the commenter having problems above was assigning the results of the initial query to a variable of type `IEnumerable` instead of `IQueryable`. – Joe Amenta Feb 28 '23 at 21:22

Sergey Berezovskiy · Accepted Answer · 2012-07-13T07:35:07.563

15

You can include conditional parameter this way:

return Customers.Where(
                customer =>
                customer.Name == Name &&
                (Age == "All" || customer.Age == Age) &&
                (Income == "All" || customer.Income == Income) &&
                (Country == "All" || customer.Country == Country)
                ).ToList();

If some condition is true (e.g. country is equal to All), then all parameter condition becomes true, and this parameter does not filter result.

edited Jul 13 '12 at 07:35

answered Jul 13 '12 at 07:18

Sergey Berezovskiy

232,247
41
429
459

Is there any drawback of this method in **2019**? – ninbit Nov 01 '19 at 09:18
@ninbit that was long time ago. I would go with conditional composition instead of a single condition for all parameters. – Sergey Berezovskiy Nov 01 '19 at 11:21
1

But it doesn't work if we `select` after composition. Do you have any idea? The error is like `it doesn't have the method 'select'` – ninbit Nov 02 '19 at 12:12
1

Old but Gold. Still very useful in cases where you cannot use composition. – Amer Feb 19 '23 at 02:55

score 2 · Answer 3 · answered Nov 29 '21 at 03:01

You can use extension method to help readable and maintainable code.

LinqExtensions.cs

public static class LinqExtensions
{
    public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Expression<Func<TSource, bool>> predicate)
    {
        return condition ? source.Where(predicate) : source;
    }
}

Refactoring code

List<Person> peoples = context.People
    .WhereIf(Country != "All", p => p.Country == Country)
    .WhereIf(Income != "All", p => p.Income == Income)
    .WhereIf(Age != "All", p => p.Age == Age)
    .ToList();

entity framework: conditional filter

3 Answers3

Linked

Related