Complex query with filters in Entity Framework Core

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.

Are the filters always going to be name/age or did you exclude other filter requirements? — gh9
– gh9, Commented Dec 4, 2017 at 22:33
Yes, I excluded some filters for the sake of simplicity, but for example, the FilterEntry could contain a navigation property, for instance Country, that would specify a Country to include/exclude if it's specified. A null value in any of the properties of the FilterEntry means that a given filter field is not set. For instance, a FilterEntry with all the properties to null, but with Country means that only the Country will apply. That's why Age is nullable. Null determines when a filter to a member applies. — SuperJMN
– SuperJMN, Commented Dec 4, 2017 at 22:35

Eric Lease · Accepted Answer · 2017-12-04 22:45:24Z

6

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

answered Dec 4, 2017 at 22:45

Eric Lease

4,2041 gold badge31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SuperJMN Over a year ago

Thanks @Eric Lease. How would this change if we included a member Country member to the FilterEntry to include/exclude a Country? Will this be solution still be valid? And if there were nested filters like Couple.CountryOfBirth?

SuperJMN Over a year ago

Eric, which package should I install to use PredicateBuilder in ASP.NET Core 2.

SuperJMN Over a year ago

I have found it. It's LinqKit.Microsoft.EntityFrameworkCore

Mitch · Accepted Answer · 2017-12-04 22:46:23Z

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.

Mojtaba Nava · Accepted Answer · 2019-08-30 15:23:34Z

0

I used for my project:

  public async Task<SocialListViewModel> GetPagedListAsyncResultSearch(SocialSearchRequestResultSearch request, List<string> fields)
    {

        //Begin Conditions

        var predicate = PredicateBuilder.New<Social>();


        if (request.Id_parvandeh != 0)
        {
            if (request.logic_Id_parvandeh.Equals("And")) predicate = predicate.And(a => a.Id_parvandeh == request.Id_parvandeh);
            else predicate = predicate.Or(a => a.Id_parvandeh == request.Id_parvandeh);
        }
}

answered Aug 30, 2019 at 15:23

Mojtaba Nava

9087 silver badges18 bronze badges

Collectives™ on Stack Overflow

Complex query with filters in Entity Framework Core

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related