DEV Community

Cover image for EF Core 3.1: dynamic WHERE clause
Timur Kh
Timur Kh

Posted on • Updated on • Originally published at blog.wiseowls.co.nz

EF Core 3.1: dynamic WHERE clause

Every now and then we get tasked with building a backend for filtering arbitrary queries. Usually, clients would like to have a method of sending over an array of fields, values, and comparisons operations in order to retrieve their data. For simplicity we’ll assume that all conditions are joining each other with an AND operator.

public class QueryableFilter {
    public string Name { get; set; }
    public string Value { get; set; }
    public QueryableFilterCompareEnum? Compare { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

With a twist

There’s however one slight complication to this problem – filters must apply to fields on dependent entities (possible multiple levels of nesting as well). This can become a problem not only because we’d have to traverse model hierarchy (we’ll touch on that later), but also because of ambiguity this requirement introduces. Sometimes we’re lucky to only have unique column names across the hierarchy. However more often than not this needs to be resolved one way or another. We can, for example, require filter fields to use dot notation so we know which entity each field relates to. For example, Name -eq "ACME Ltd" AND Name -eq "Cloud Solutions" becomes company.Name -eq "ACME Ltd" AND team.Name -eq "Cloud Solutions"

Building an expression

It is pretty common that clients already have some sort of data querying service with EF Core doing the actual database comms. And since EF relies on LINQ Expressions a lot – we can build required filters dynamically.

public static IQueryable<T> BuildExpression<T>(this IQueryable<T> source, DbContext context, string columnName, string value, QueryableFilterCompareEnum? compare = QueryableFilterCompareEnum.Equal)
{
    var param = Expression.Parameter(typeof(T));

    // Get the field/column from the Entity that matches the supplied columnName value
    // If the field/column does not exists on the Entity, throw an exception; There is nothing more that can be done
    MemberExpression dataField;

    var model = context.Model.FindEntityType(typeof(T)); // start with our own entity
    var props = model.GetPropertyAccessors(param); // get all available field names including navigations
    var reference = props.First(p => RelationalPropertyExtensions.GetColumnName(p.Item1) == columnName); // find the filtered column - you might need to handle cases where column does not exist

    dataField = reference.Item2 as MemberExpression; // we happen to already have correct property accessors in our Tuples  

    ConstantExpression constant = !string.IsNullOrWhiteSpace(value)
        ? Expression.Constant(value.Trim(), typeof(string))
        : Expression.Constant(value, typeof(string));

    BinaryExpression binary = GetBinaryExpression(dataField, constant, compare);
    Expression<Func<T, bool>> lambda = (Expression<Func<T, bool>>)Expression.Lambda(binary, param);
    return source.Where(lambda);
}
Enter fullscreen mode Exit fullscreen mode

Most of the code above is pretty standard for building property accessor lambdas, but GetPropertyAccessors is the key:

private static IEnumerable<Tuple<IProperty, Expression>> GetPropertyAccessors(this IEntityType model, Expression param)
{
    var result = new List<Tuple<IProperty, Expression>>();

    result.AddRange(model.GetProperties()
                                .Where(p => !p.IsShadowProperty()) // this is your chance to ensure property is actually declared on the type before you attempt building Expression
                                .Select(p => new Tuple<IProperty, Expression>(p, Expression.Property(param, p.Name)))); // Tuple is a bit clunky but hopefully conveys the idea

    foreach (var nav in model.GetNavigations().Where(p => p is Navigation))
    {
        var parentAccessor = Expression.Property(param, nav.Name); // define a starting point so following properties would hang off there
        result.AddRange(GetPropertyAccessors(nav.ForeignKey.PrincipalEntityType, parentAccessor)); //recursively call ourselves to travel up the navigation hierarchy
    }

    return result;
}
Enter fullscreen mode Exit fullscreen mode

this is where we interrogate EF as-built data model, traverse navigation properties and recursively build a list of all properties we can ever filter on!

Testing it out

Talk is cheap, let’s run a complete example here:

public class Entity
{
    public int Id { get; set; }
}
class Company : Entity
{
    public string CompanyName { get; set; }
}

class Team : Entity
{
    public string TeamName { get; set; }
    public Company Company { get; set; }
}

class Employee : Entity
{
    public string EmployeeName { get; set; }
    public Team Team { get; set; }
}

class DynamicFilters<T> where T : Entity
{
    private readonly DbContext _context;

    public DynamicFilters(DbContext context)
    {
        _context = context;
    }

    public IEnumerable<T> Filter(IEnumerable<QueryableFilter> queryableFilters = null)
    {
        IQueryable<T> mainQuery = _context.Set<T>().AsQueryable().AsNoTracking();
        // Loop through the supplied queryable filters (if any) to construct a dynamic LINQ-to-SQL queryable
        foreach (var filter in queryableFilters ?? new List<QueryableFilter>())
        {
            mainQuery = mainQuery.BuildExpression(_context, filter.Name, filter.Value, filter.Compare);
        }

        mainQuery = mainQuery.OrderBy(x => x.Id);

        return mainQuery.ToList();
    }
}
// --- DbContext
class MyDbContext : DbContext
{
    public DbSet<Company> Companies { get; set; }
    public DbSet<Team> Teams { get; set; }
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=.\\SQLEXPRESS;Database=test;Trusted_Connection=true");
        base.OnConfiguring(optionsBuilder);
    }
}
// ---
static void Main(string[] args)
{
    var context = new MyDbContext();
    var someTableData = new DynamicFilters<Employee>(context).Filter(new
    List<QueryableFilter> { new QueryableFilter { Name = "CompanyName", Value = "ACME Ltd" }, new QueryableFilter { Name = "TeamName", Value = "Cloud Solutions" } });
}
Enter fullscreen mode Exit fullscreen mode

The above block should produce following SQL:

SELECT [e].[Id], [e].[EmployeeName], [e].[TeamId]
FROM [Employees] AS [e]
LEFT JOIN [Teams] AS [t] ON [e].[TeamId] = [t].[Id]
LEFT JOIN [Companies] AS [c] ON [t].[CompanyId] = [c].[Id]
WHERE [c].[CompanyName] = N'ACME Ltd'
 AND [t].[TeamName] = N'Cloud Solutions'
ORDER BY [e].[Id]
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
arminops profile image
Armin Tor

I know how fun that is.. Congrats.. Before i was trying to make a dynamic one for EF.Functions.Like expression... .