Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

entity framework: conditional filter

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 !

like image 891
David Avatar asked Jul 13 '12 07:07

David


People also ask

What is global query filter in Entity Framework?

Global query filters are LINQ query predicates applied to Entity Types in the metadata model (usually in OnModelCreating). A query predicate is a boolean expression typically passed to the LINQ Where query operator. EF Core applies such filters automatically to any LINQ queries involving those Entity Types.

What are filters in Entity Framework Core?

EF Core applies such filters automatically to any LINQ queries involving those Entity Types. EF Core also applies them to Entity Types, referenced indirectly through use of Include or navigation property. Some common applications of this feature are: Soft delete - An Entity Type defines an IsDeleted property.

How to use multiple dbcontext query filters on the same entity?

Note the use of a DbContext instance level field: _tenantId used to set the current tenant. Model-level filters will use the value from the correct context instance (that is, the instance that is executing the query). It is currently not possible to define multiple query filters on the same entity - only the last one will be applied.

How does EF Core apply filters to LINQ queries?

EF Core applies such filters automatically to any LINQ queries involving those Entity Types. EF Core also applies them to Entity Types, referenced indirectly through use of Include or navigation property.


1 Answers

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.

like image 156
Yakimych Avatar answered Oct 13 '22 00:10

Yakimych