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.
Accepted Answer
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);
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow