How to construct a dynamic where filter in EF.Core

Question

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

Answer 1

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

Answer 2

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

Answer 3

more than one and, or how to use it?

var test = ExpressionUtils.BuildPredicate<T>("YetkiAdi", "==", "Mustafa").And("YetkiAciklama","!=","Mustafa2");
var testData = _repository.GetAll().Where(test);