Entity Framework Core Dynamic Where Clause

Question

I have been searching and I have not seen an example like this.

I want to query a Model table to retrieve all Models from German manufacturers.

My data structure is Model Table has a Navigation Property called "Manufacturer" and an FK called "IdManufacturer"

A Manufacturer Has a Navigation Property called "Origin" and an FK called IdOrigin.

I would like to have a query that includes a where clause something like this:

(m => m.Manufacturer.Origin.Name == "German");

I would like this to be dynamically created at run time. Not only the lookup value might change, but the next time even the fields may change to, for example: (m.Type.Name == "SUV");

Finally, there will not necessarily be a UI associated with this request, it may be generated in code.

Please don't get too hung up on the business of it, I am hoping this made up example will be simple to understand.

Any suggestions on how to handle this would be greatly appreciated. Any general thoughts on performance would be created.

Answer 1

My solution:
I have a model RouteList, in this model I created Expression:

public static Expression<Func<RouteList, bool>> FilterByDateOrShiftCode(RoutesFilterViewModel filter)
{
    Expression<Func<RouteList, bool>> result = rl => true;

    if (string.IsNullOrEmpty(filter.ShiftCode))
    {
        result = rl => rl.RouteListDate.Date >= filter.From.Date && rl.RouteListDate.Date <= filter.To.Date && rl.User.Id == filter.User.Id;
    }
    else
    {
        result = rl => rl.ShiftCode == filter.ShiftCode && rl.User.Id == filter.User.Id;
    }  

Place where I use it:

model.RouteLists = await _context.RouteLists
    .Include(rl => rl.Status)
    .Include(rl => rl.User)
    .Include(rl => rl.RouteListRows).ThenInclude(rlr => rlr.Address)
    .Where(RouteList.FilterByDateOrShiftCode(filter))
    .ToListAsync();

Speed of executing grow in several times.

Answer 2

Try the following simple extension method. Note that this realization will work only with sting properties.

Usage:

query = query.Where("Manufacturer.Origin.Name", "German");

Realization:

public static class QueryableExtensions
{
    public static IQueryable<T> Where<T>(this IQueryable<T> query, string propPath, string value)
    {
        var param = Expression.Parameter(typeof(T), "e");

        // e.Prop1.Prop2 == value
        var body = Expression.Equal(MakePropPath(param, propPath), Expression.Constant(value));
        // e => e.Prop1.Prop2 == value
        var predicateLambda = Expression.Lambda<Func<T, bool>>(body, param);

        return query.Where(predicateLambda);
    }

    static Expression MakePropPath(Expression objExpression, string path)
    {
        return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
    }
}