Complex query with filters in Entity Framework Core

Question

Complex query with filters in Entity Framework Core

c# entity-framework entity-framework-core filter

Question

I have a model with only 3 classes: User, Filter and FilterEntry.

class Filter 
{
    public List<FilterEntry> Inclusions { get; set; } 
    public List<FilterEntry> Exclusions { get; set; } 
}

public class FilterEntry
{
    public string Name { get; set; }
    public int? Age { get; set; }
}

public class User 
{
    public string Name { get; set; }
    public int Age { get; set; }
}

A Filter is filter that was persisted to the DB, you can see it like a definition of a filter. It contains one or more FilterEntries that define the filter. The Inclusions and Exclusions are the restrictions that apply to the filter.

An example:

var filter = new Filter 
    { 
       Inclusions = new[] { new FilterEntry { Age = 33 } }, 
       Exclusions = new[] { new FilterEntry { Name = "John" }, new FilterEntry { Name = "Peter" } }, 
    };

This defines a Filter that will represent people aged 33, except for who are called "John" or "Peter". So, when this filter is applied to the Users, it should return all the users that are 33, except for Johns and Peters.

Now the problem. How do I create a query using Entity Framework that, given a Filter, returns the Users according to it?

I don't even know how to start! All I have is this:

Filter filter = dbContext.Filters.First(x => x.FilterId == filterId);
var filteredUsers = from u in dbContext.Users
     where ... // user is any of the in filter.Inclusions
     where ... // user is not in any of the filter.Exclusions
     select u;

NOTICE that Filter and FilterEntry keep a 1-N relationship. I omitted the keys to simplify the code.

SuperJMN

Popular Answer

I had a similar scenario arise where I wanted to be able to specify dynamic filters like that. I ended up using the System.Linq.Dynamic.Core NuGet package to help (https://github.com/StefH/System.Linq.Dynamic.Core). This allows you to run Linq queries using string inputs. Then, rather than having to build a Linq expression tree from your Filter, you just need to construct a where clause as a string.

public class Filter
{
    public List<FilterEntry> Inclusions { get; set; }
    public List<FilterEntry> Exclusions { get; set; }

    public IQueryable<User> ApplyTo<User>(IQueryable<User> queryable)
    {
        // TODO: Dynamically build this string instead of hard-coding it.
        //       You would probably iterate through Inclusions and Exclusions
        //       appending to the where clause as you go.
        var whereClause = "Age == @0 && Name != @1 && Name != @2";
        var params = new object[] { 33, "John", "Peter" };

        return queryable.Where(whereClause, params);
    }
}

I realise this isn't a working solution, but hopefully it at least gives you a place to start.

Mitch

View more on Stack Overflow

Eric Lease · Accepted Answer · 12/4/2017 10:45:24 PM

You want to build a dynamic where clause, so I would recommend PredicateBuilder. I haven't tested the code below, but you would want something like...

Filter filter = dbContext.Filters.First(x => x.FilterId == filterId);

var pb = new PredicateBuilder<User>();

foreach(var inclusion in filter.Inclusions)
{
    if(inclusion.Age.HasValue) 
    {
        pb = pb.And(p => p.Age == inclusion.Age.Value);
    }
    if(!string.IsNullOrWhiteSpace(inclusion.Name))
    {
        pb = pb.And(p => p.Name == inclusion.Name);
    }
}

foreach(var exclusion in filter.Exclusions)
{
    if(exclusion.Age.HasValue) 
    {
        pb = pb.And(p => p.Age != exclusion.Age.Value);
    }
    if(!string.IsNullOrWhiteSpace(exclusion.Name))
    {
        pb = pb.And(p => p.Name != exclusion.Name);
    }
}

var filteredUsers = dbContext.Users.AsExpandable().Where(pb);

Complex query with filters in Entity Framework Core

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related