Using dynamic filters with Entity Framework Core

Question

I'm developing an application (.Net Core 3.1, C# 8) that is using Entity Framework Core.

I would like to filter a table with several filtering options.

I'm getting the filter conditions in JSON and I'm deserializing it into an object. I want to write a where LINQ query which will filter the table based on those dynamic filtering options.

The twist is I need to manage the filtering with many options and combinations.

You can filter for the market, country, vendor and the rest of the filter options will be null.
You would like to filter for country and vendor then the market will be null and also the rest of the filter options.

I'm querying a huge table, so it is important to write a query which translates fully into SQL.

The following code is not working properly. I'm looking for something similar that can solve this issue:

var filters = new demoFilterEntity()
{
      Market = new List() { "LAT", "NAM" }
};

var filteredData = demoMainRepository.GetAll().Where(x =>
      x.Market != null && (filters.Market != null ? filters.Market.Contains(x.Market) : false) &&
      x.Country != null && (filters.Country != null ? filters.Country.Contains(x.Market) : false)).ToList();

I would appreciate suggestions on how I can get through this and manage the filtering dynamically.

user_2501279 · Answer

If you have only AND conditions you can do it with just chaining Where clauses:

var query = demoMainRepository.GetAll().Where(x => x.Market != null); 
if(filters.Market != null)
{
    query = query.Where(x => filters.Market.Contains(x.Market));
}
...
var filteredData = query.ToList();

Also as @Lajos Arpad said maybe you need to consider combining fields null checks (i.e. x.Market != null) with the filters checks:

var query = demoMainRepository.GetAll(); 
if(filters.Market != null)
{
    query = query.Where(x =>  x.Market != null && filters.Market.Contains(x.Market));
}
...
var filteredData = query.ToList();

user_2326503 · Answer

I'm currently researching this topic and have found dynamic lambda expressions is a thing The .Where method accepts Expression> type which you can create dynamically.

Kind of cumbersome but it works good once you wrap your head around it

I recommend this article in Codemag Dynamic Lambda Expressions

Using dynamic filters with Entity Framework Core

2 Answers