CODEWITHSUNDEEP
c#entity-framework.net-coreentity-framework-coreasp.net-core-3.1
Szabolcs Kun
Szabolcs Kun

Reputation: 63

Using dynamic filters with Entity Framework Core

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.

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<string>() { "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.

Upvotes: 3

Views: 10597

Answers (2)

atlmag
atlmag

Reputation: 191

I'm currently researching this topic and have found dynamic lambda expressions is a thing The .Where method accepts Expression<Func<Entity, bool>> 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

Upvotes: 0

Guru Stron
Guru Stron

Reputation: 141845

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();

Upvotes: 3

Related Questions