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

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