How to construct a dynamic where filter in EF.Core to handle equals, LIKE, gt, lt, etc

user_44080 0
Viewed 9477

Please how do we construct a dynamic where filter in EF.Core to handle:

Query.Where(fieldName, compareMode, value)

I basically Expect to use it like below:

    [HttpGet(Name = nameof(GetStaff))]
    public IActionResult GetStaffAsync([FromQuery] QueryParams p)
    {
      var s = db.Staff.AsNoTracking()
   .Where(p.filter_field, p.filter_mode, p.filter_value)
   .OrderByMember(p.sortBy, p.descending);

      var l = new Pager<Staff>(s, p.page, p.rowsPerPage);

      return Ok(l);
    }

//Helpers
      public class QueryParams
      {
        public bool descending { get; set; }
        public int page { get; set; } = 1;
        public int rowsPerPage { get; set; } = 5;

        public string sortBy { get; set; }

        public onject filter_value { get; set; }
        public string filter_field { get; set; }
        public string filter_mode { get; set; }
      }

  public class Pager<T>
  {
    public int pages { get; set; }
    public int total { get; set; }
    public IEnumerable<T> Items { get; set; }

    public Pager(IEnumerable<T> items, int offset, int limit)
    {
      Items = items.Skip((offset - 1) * limit).Take(limit).ToList<T>();
      total = items.Count();
      pages = (int)Math.Ceiling((double)total / limit);
    }
  }
.net-core dynamic entity-framework
3 Answers

Assuming all you have is the entity type and strings representing the property, comparison operator and the value, building dynamic predicate can be done with something like this:

public static partial class ExpressionUtils
{
    public static Expression<Func<T, bool>> BuildPredicate<T>(string propertyName, string comparison, string value)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        var left = propertyName.Split('.').Aggregate((Expression)parameter, Expression.Property);
        var body = MakeComparison(left, comparison, value);
        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }

    private static Expression MakeComparison(Expression left, string comparison, string value)
    {
        switch (comparison)
        {
            case "==":
                return MakeBinary(ExpressionType.Equal, left, value);
            case "!=":
                return MakeBinary(ExpressionType.NotEqual, left, value);
            case ">":
                return MakeBinary(ExpressionType.GreaterThan, left, value);
            case ">=":
                return MakeBinary(ExpressionType.GreaterThanOrEqual, left, value);
            case "<":
                return MakeBinary(ExpressionType.LessThan, left, value);
            case "<=":
                return MakeBinary(ExpressionType.LessThanOrEqual, left, value);
            case "Contains":
            case "StartsWith":
            case "EndsWith":
                return Expression.Call(MakeString(left), comparison, Type.EmptyTypes, Expression.Constant(value, typeof(string)));
            default:
                throw new NotSupportedException($"Invalid comparison operator '{comparison}'.");
        }
    }

    private static Expression MakeString(Expression source)
    {
        return source.Type == typeof(string) ? source : Expression.Call(source, "ToString", Type.EmptyTypes);
    }

    private static Expression MakeBinary(ExpressionType type, Expression left, string value)
    {
        object typedValue = value;
        if (left.Type != typeof(string))
        {
            if (string.IsNullOrEmpty(value))
            {
                typedValue = null;
                if (Nullable.GetUnderlyingType(left.Type) == null)
                    left = Expression.Convert(left, typeof(Nullable<>).MakeGenericType(left.Type));
            }
            else
            {
                var valueType = Nullable.GetUnderlyingType(left.Type) ?? left.Type;
                typedValue = valueType.IsEnum ? Enum.Parse(valueType, value) :
                    valueType == typeof(Guid) ? Guid.Parse(value) :
                    Convert.ChangeType(value, valueType);
            }
        }
        var right = Expression.Constant(typedValue, left.Type);
        return Expression.MakeBinary(type, left, right);
    }
}

Basically building property accessor (with nested property support), parsing the comparison operator and calling the corresponding operator/method, dealing with from/to string and from/to nullable type conversions. It can be extended to handle EF Core specific functions like EF.Functions.Like by adding the corresponding branch.

It can be used directly (in case you need to combine it with other predicates) or via custom extension method like this:

public static partial class QueryableExtensions
{
    public static IQueryable<T> Where<T>(this IQueryable<T> source, string propertyName, string comparison, string value)
    {
        return source.Where(ExpressionUtils.BuildPredicate<T>(propertyName, comparison, value));
    }
}
user_52025630

based on Ivans answer this is what i came up with

public static class ExpressionUtils
{
    public static Expression<Func<T, bool>> BuildPredicate<T>(string propertyName, string comparison, object value)
    {
        var parameter = Expression.Parameter(typeof(T));
        var left = propertyName.Split('.').Aggregate((Expression)parameter, Expression.PropertyOrField);
        var body = MakeComparison(left, comparison, value);
        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }

    static Expression MakeComparison(Expression left, string comparison, object value)
    {
        var constant = Expression.Constant(value, left.Type);
        switch (comparison)
        {
            case "==":
                return Expression.MakeBinary(ExpressionType.Equal, left, constant);
            case "!=":
                return Expression.MakeBinary(ExpressionType.NotEqual, left, constant);
            case ">":
                return Expression.MakeBinary(ExpressionType.GreaterThan, left, constant);
            case ">=":
                return Expression.MakeBinary(ExpressionType.GreaterThanOrEqual, left, constant);
            case "<":
                return Expression.MakeBinary(ExpressionType.LessThan, left, constant);
            case "<=":
                return Expression.MakeBinary(ExpressionType.LessThanOrEqual, left, constant);
            case "Contains":
            case "StartsWith":
            case "EndsWith":
                if (value is string)
                {
                    return Expression.Call(left, comparison, Type.EmptyTypes, constant);
                }
                throw new NotSupportedException($"Comparison operator '{comparison}' only supported on string.");
            default:
                throw new NotSupportedException($"Invalid comparison operator '{comparison}'.");
        }
    }
}

and some tests

public class Tests
{
    [Fact]
    public void Nested()
    {
        var list = new List<Target>
        {
            new Target
            {
                Member = "a"
            },
            new Target
            {
                Member = "bb"
            }
        };

        var result = list.AsQueryable()
            .Where(ExpressionUtils.BuildPredicate<Target>("Member.Length", "==", 2))
            .Single();
        Assert.Equal("bb", result.Member);
    }

    [Fact]
    public void Field()
    {
        var list = new List<TargetWithField>
        {
            new TargetWithField
            {
                Field = "Target1"
            },
            new TargetWithField
            {
                Field = "Target2"
            }
        };

        var result = list.AsQueryable()
            .Where(ExpressionUtils.BuildPredicate<TargetWithField>("Field", "==", "Target2"))
            .Single();
        Assert.Equal("Target2", result.Field);
    }

    [Theory]
    [InlineData("Name", "==", "Person 1", "Person 1")]
    [InlineData("Name", "!=", "Person 2", "Person 1")]
    [InlineData("Name", "Contains", "son 2", "Person 2")]
    [InlineData("Name", "StartsWith", "Person 2", "Person 2")]
    [InlineData("Name", "EndsWith", "son 2", "Person 2")]
    [InlineData("Age", "==", 13, "Person 2")]
    [InlineData("Age", ">", 12, "Person 2")]
    [InlineData("Age", "!=", 12, "Person 2")]
    [InlineData("Age", ">=", 13, "Person 2")]
    [InlineData("Age", "<", 13, "Person 1")]
    [InlineData("Age", "<=", 12, "Person 1")]
    public void Combos(string name, string expression, object value, string expectedName)
    {
        var people = new List<Person>
        {
            new Person
            {
                Name = "Person 1",
                Age = 12
            },
            new Person
            {
                Name = "Person 2",
                Age = 13
            }
        };

        var result = people.AsQueryable()
            .Where(ExpressionUtils.BuildPredicate<Person>(name, expression, value))
            .Single();
        Assert.Equal(expectedName, result.Name);
    }
}
user_531580

I modified the answer I found here: Linq WHERE EF.Functions.Like - Why direct properties work and reflection does not?

I chucked together a version for those using NpgSQL as their EF Core provider as you will need to use the ILike function instead if you want case-insensitivity, also added a second version which combines a bunch of properties into a single Where() clause:

public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, string propertyName, string searchTerm)
    {
        // Check property name
        if (string.IsNullOrEmpty(propertyName))
        {
            throw new ArgumentNullException(nameof(propertyName));
        }

        // Check the search term
        if(string.IsNullOrEmpty(searchTerm))
        {
            throw new ArgumentNullException(nameof(searchTerm));
        }

        // Check the property exists
        var property = typeof(T).GetProperty(propertyName);
        if (property == null)
        {
            throw new ArgumentException($"The property {typeof(T)}.{propertyName} was not found.", nameof(propertyName));
        }

        // Check the property type
        if(property.PropertyType != typeof(string))
        {
            throw new ArgumentException($"The specified property must be of type {typeof(string)}.", nameof(propertyName));
        }

        // Get expression constants
        var searchPattern = "%" + searchTerm + "%";
        var itemParameter = Expression.Parameter(typeof(T), "item");
        var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
        var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });

        // Build the property expression and return it
        Expression selectorExpression = Expression.Property(itemParameter, property.Name);
        selectorExpression = Expression.Call(null, likeFunction, functions, selectorExpression, Expression.Constant(searchPattern));
        return source.Where(Expression.Lambda<Func<T, bool>>(selectorExpression, itemParameter));
    }

    public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, IEnumerable<string> propertyNames, string searchTerm)
    {
        // Check property name
        if (!(propertyNames?.Any() ?? false))
        {
            throw new ArgumentNullException(nameof(propertyNames));
        }

        // Check the search term
        if (string.IsNullOrEmpty(searchTerm))
        {
            throw new ArgumentNullException(nameof(searchTerm));
        }

        // Check the property exists
        var properties = propertyNames.Select(p => typeof(T).GetProperty(p)).AsEnumerable();
        if (properties.Any(p => p == null))
        {
            throw new ArgumentException($"One or more specified properties was not found on type {typeof(T)}: {string.Join(",", properties.Where(p => p == null).Select((p, i) => propertyNames.ElementAt(i)))}.", nameof(propertyNames));
        }

        // Check the property type
        if (properties.Any(p => p.PropertyType != typeof(string)))
        {
            throw new ArgumentException($"The specified properties must be of type {typeof(string)}: {string.Join(",", properties.Where(p => p.PropertyType != typeof(string)).Select(p => p.Name))}.", nameof(propertyNames));
        }

        // Get the expression constants
        var searchPattern = "%" + searchTerm + "%";
        var itemParameter = Expression.Parameter(typeof(T), "item");
        var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
        var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });

        // Build the expression and return it
        Expression selectorExpression = null;
        foreach (var property in properties)
        {
            var previousSelectorExpression = selectorExpression;
            selectorExpression = Expression.Property(itemParameter, property.Name);
            selectorExpression = Expression.Call(null, likeFunction, functions, selectorExpression, Expression.Constant(searchPattern));
            if(previousSelectorExpression != null)
            {
                selectorExpression = Expression.Or(previousSelectorExpression, selectorExpression);
            }
        }
        return source.Where(Expression.Lambda<Func<T, bool>>(selectorExpression, itemParameter));
    }
user_4784780
Related