Linq dynamic expression for filtering navigation properties and collections

Question

I'm trying to add filtering functionality to my web api. I have two classes as base class

Global one is:

public abstract class GlobalDto<TKey, TCultureDtoKey, TCultureDto> :
    Dto<TKey>,
    IGlobalDto<TKey, TCultureDtoKey, TCultureDto>
    where TCultureDto : ICultureDto<TCultureDtoKey, TKey>, new()
{
    public virtual IList<TCultureDto> Globals { get; set; }        
}

and the cultured one is:

public abstract class CultureDto<TKey, TMasterDtoKey> :
    SubDto<TKey, TMasterDtoKey>,
    ICultureDto<TKey, TMasterDtoKey>
{
    public int CultureId { get; set; }
}

also SubDto class is:

public abstract class SubDto<TKey, TMasterDtoKey> : Dto<TKey>, ISubDto<TKey, TMasterDtoKey>
{
    public TMasterDtoKey MasterId { get; set; }
}

the scenario I'm trying is filtering the IQueryable GlobalDto dynamically and also filter by its

 IList<TCultureDto> Globals { get; set; }

eg:

public class CategoryDto : GlobalDto<int, int, CategoryCultureDto>, IDtoWithSelfReference<int>        
{
    public int? TopId { get; set; }

    [StringLength(20)]
    public string Code { get; set; }

    public IList<CategoryCoverDto> Covers { get; set; }

}

public class CategoryCultureDto : CultureDto<int, int>
{
    [Required]
    [StringLength(100)]
    public string Name { get; set; }        
}

I have tried this answer here and also lot of things but I couldn't make it.

I have property name, operation type (eg: contains, startswith) and comparing value from querystring so it has to be dynamic for various propertynames and various operation types like co(contains) and infinite values like foo.

http://localhost:5000/categories?search=name co foo

after this request

IQueryable<CategoryDto> q;//query
/* Expression building process equals to q.Where(p=>p.Globals.Any(c=>c.Name.Contains("foo")))*/
return q.Where(predicate);//filtered query

But I couldnt make it for globals

Edit: Code I used for doing this.

[HttpGet("/[controller]/Test")]
    public IActionResult Test()
    {
        var propName = "Name";
        var expressionProvider = new GlobalStringSearchExpressionProvider();
        var value = "foo";
        var op = "co";

        var propertyInfo = ExpressionHelper
            .GetPropertyInfo<CategoryCultureDto>(propName);
        var obj = ExpressionHelper.Parameter<CategoryCultureDto>();

        // Build up the LINQ expression backwards:
        // query = query.Where(x => x.Property == "Value");

        // x.Property
        var left = ExpressionHelper.GetPropertyExpression(obj, propertyInfo);
        // "Value"
        var right = expressionProvider.GetValue(value);

        // x.Property == "Value"
        var comparisonExpression = expressionProvider
            .GetComparison(left, op, right);

        // x => x.Property == "Value"
        var lambdaExpression = ExpressionHelper
            .GetLambda<CategoryCultureDto, bool>(obj, comparisonExpression);
        var q = _service.GetAll(); //this returns IQueryable<CategoryDto>

        var query = q.Where(p => p.Globals.CallWhere(lambdaExpression).Any());

        var list = query.ToList();

        return Ok(list);
    }


public class GlobalStringSearchExpressionProvider : DefaultSearchExpressionProvider
{
    private const string StartsWithOperator = "sw";
    private const string EndsWithOperator = "ew";
    private const string ContainsOperator = "co";

    private static readonly MethodInfo StartsWithMethod = typeof(string)
        .GetMethods()
        .First(m => m.Name == "StartsWith" && m.GetParameters().Length == 2);

    private static readonly MethodInfo EndsWithMethod = typeof(string)
        .GetMethods()
        .First(m => m.Name == "EndsWith" && m.GetParameters().Length == 2);

    private static readonly MethodInfo StringEqualsMethod = typeof(string)
        .GetMethods()
        .First(m => m.Name == "Equals" && m.GetParameters().Length == 2);

    private static readonly MethodInfo ContainsMethod = typeof(string)
        .GetMethods()
        .First(m => m.Name == "Contains" && m.GetParameters().Length == 1);

    private static readonly ConstantExpression IgnoreCase
        = Expression.Constant(StringComparison.OrdinalIgnoreCase);

    public override IEnumerable<string> GetOperators()
        => base.GetOperators()
            .Concat(new[]
            {
                StartsWithOperator,
                ContainsOperator,
                EndsWithOperator
            });

    public override Expression GetComparison(MemberExpression left, string op, ConstantExpression right)
    {
        switch (op.ToLower())
        {
            case StartsWithOperator:
                return Expression.Call(left, StartsWithMethod, right, IgnoreCase);

            // TODO: This may or may not be case-insensitive, depending
            // on how your database translates Contains()
            case ContainsOperator:
                return Expression.Call(left, ContainsMethod, right);

            // Handle the "eq" operator ourselves (with a case-insensitive compare)
            case EqualsOperator:
                return Expression.Call(left, StringEqualsMethod, right, IgnoreCase);

            case EndsWithOperator:
                return Expression.Call(left, EndsWithMethod, right);

            default: return base.GetComparison(left, op, right);
        }
    }
}


public static class ExpressionHelper
{
    private static readonly MethodInfo LambdaMethod = typeof(Expression)
        .GetMethods()
        .First(x => x.Name == "Lambda" && x.ContainsGenericParameters && x.GetParameters().Length == 2);

    private static readonly MethodInfo[] QueryableMethods = typeof(Queryable)
        .GetMethods()
        .ToArray();

    private static MethodInfo GetLambdaFuncBuilder(Type source, Type dest)
    {
        var predicateType = typeof(Func<,>).MakeGenericType(source, dest);
        return LambdaMethod.MakeGenericMethod(predicateType);
    }

    public static PropertyInfo GetPropertyInfo<T>(string name)
        => typeof(T).GetProperties()
        .Single(p => p.Name == name);

    public static ParameterExpression Parameter<T>()
        => Expression.Parameter(typeof(T));

    public static ParameterExpression ParameterGlobal(Type type)
        => Expression.Parameter(type);

    public static MemberExpression GetPropertyExpression(ParameterExpression obj, PropertyInfo property)
        => Expression.Property(obj, property);

    public static LambdaExpression GetLambda<TSource, TDest>(ParameterExpression obj, Expression arg)
        => GetLambda(typeof(TSource), typeof(TDest), obj, arg);

    public static LambdaExpression GetLambda(Type source, Type dest, ParameterExpression obj, Expression arg)
    {
        var lambdaBuilder = GetLambdaFuncBuilder(source, dest);
        return (LambdaExpression)lambdaBuilder.Invoke(null, new object[] { arg, new[] { obj } });
    }

    public static IQueryable<T> CallWhere<T>(this IEnumerable<T> query, LambdaExpression predicate)
    {
        var whereMethodBuilder = QueryableMethods
            .First(x => x.Name == "Where" && x.GetParameters().Length == 2)
            .MakeGenericMethod(typeof(T));

        return (IQueryable<T>)whereMethodBuilder
            .Invoke(null, new object[] { query, predicate });
    }

    public static IQueryable<T> CallAny<T>(this IEnumerable<T> query, LambdaExpression predicate)
    {
        var anyMethodBuilder = QueryableMethods
            .First(x => x.Name == "Any" && x.GetParameters().Length == 2)
            .MakeGenericMethod(typeof(T));
        return (IQueryable<T>) anyMethodBuilder
            .Invoke(null, new object[] {query, predicate});
    }


}

Exception is:

{
"message": "Could not parse expression 'p.Globals.CallWhere(Param_0 => Param_0.Name.Contains(\"stil\"))': This overload of the method 'ImjustCore.CrossCutting.Extensions.Expressions.ExpressionHelper.CallWhere' is currently not supported.",
"detail": "   at Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.GetNodeType(MethodCallExpression expressionToParse)\n   at Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.Parse(String associatedIdentifier, IExpressionNode source, IEnumerable`1 arguments, MethodCallExpression expressionToParse)\n   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, String associatedIdentifier)\n   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseNode(Expression expression, String associatedIdentifier)\n   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, String associatedIdentifier)\n   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseTree(Expression expressionTree)\n   at Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)\n   at Remotion.Linq.Parsing.ExpressionVisitors.SubQueryFindingExpressionVisitor.Visit(Expression expression)\n   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)\n   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\n   at Remotion.Linq.Parsing.ExpressionVisitors.SubQueryFindingExpressionVisitor.Visit(Expression expression)\n   at Remotion.Linq.Parsing.ExpressionVisitors.SubQueryFindingExpressionVisitor.Process(Expression expressionTree, INodeTypeProvider nodeTypeProvider)\n   at Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.ProcessArgumentExpression(Expression argumentExpression)\n   at System.Linq.Enumerable.SelectListPartitionIterator`2.ToArray()\n   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)\n   at Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.Parse(String associatedIdentifier, IExpressionNode source, IEnumerable`1 arguments, MethodCallExpression expressionToParse)\n   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, String associatedIdentifier)\n   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseTree(Expression expressionTree)\n   at Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_0`1.<Execute>b__0()\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)\n   at Remotion.Linq.QueryableBase`1.GetEnumerator()\n   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\n   at ImjustCore.Presentation.Api.Controllers.CategoriesController.Test() in /Users/apple/Desktop/Development/Core/ImjustCore/ImjustCore/ImjustCore.Presentation.Api/Controllers/CategoriesController.cs:line 87\n   at lambda_method(Closure , Object , Object[] )\n   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)\n   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\n   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()\n   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)\n   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\n   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\n   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()\n   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__23.MoveNext()"
}

When I apply the lambda expression directly to IQueryable of CategoryDto with same extension classes above

with:

[HttpGet("/[controller]/Test")]
    public IActionResult Test()
    {
        var propName = "Code";
        var expressionProvider = new StringSearchExpressionProvider();
        var value = "foo";
        var op = "co";

        var propertyInfo = ExpressionHelper
            .GetPropertyInfo<CategoryDto>(propName);
        var obj = ExpressionHelper.Parameter<CategoryCultureDto>();

        // Build up the LINQ expression backwards:
        // query = query.Where(x => x.Property == "Value");

        // x.Property
        var left = ExpressionHelper.GetPropertyExpression(obj, propertyInfo);
        // "Value"
        var right = expressionProvider.GetValue(value);

        // x.Property == "Value"
        var comparisonExpression = expressionProvider
            .GetComparison(left, op, right);

        // x => x.Property == "Value"
        var lambdaExpression = ExpressionHelper
            .GetLambda<CategoryDto, bool>(obj, comparisonExpression);
        var q = _service.GetAll();

        var query = q.CallWhere(lambdaExpression);

        var list = query.ToList();

        return Ok(list);
    }

It works fine. because there is no filtering on child collection and results are filtering properly.

Answer 1

I'll hope this will be useful to you, pseudo coded.

When you call

var query = q.Where(p => p.Globals.CallWhere(lambdaExpression).Any());

You're passing the Function CallWhere to EntityFramework, which attempts resolve the functions you call into SQL Code.

Entity Framework does not know about your custom function. So instead of calling CallWhere in your expresion, you need to build the expression that calls the where itself.

First build your expression to it's casted type using Expression.Lambda this will cast it to your expression from lambda Expression to Expression but since you don't have your type at runtime you need to call the where clause through reflection because you never have your concrete TKey.

so you want to do this:

var castedExpression = Expression.Lambda<Func<TKey, bool>>(lambdaExpression, lambdaExpression.Parameters);
 x => x.Globals.Where(castedExpression)

But you can't since you don't know the TKey at Compile time,

and you will never be able to pass your lambdaExpression directly to your where because of type saferty, you only know it's base class. so you need to use reflection to build the expression.

use reflection to invoke the where method on the globals

to build the lambda like so:

var propertyInfo = ExpressionHelper.GetProperty("globals");
var castedExpression = Expression.Lambda(typeof(propertyInfo.PropertyType), lambdaExpression, Paramters)

// now write a function which build an expression at runtime
// x => x.Globals.Where(castedExpression)

the return type with be you Expression<Func<TEntity, bool>> EntityType (not your propertyType)

to sum it all up this line

// var query = q.Where(p => p.Globals.CallWhere(lambdaExpression).Any());

needs to look more like this //We know you need the Globals Property grab it internally

var expression = BuildGlobalExpression<CategoryDto>(lambdaExpression,  "Any")
q.Where(expression);  

Answer 2

This solution worked. Special thanks to @(johnny 5) for his attentions and support.

    [HttpGet("/[controller]/test/{searchTerm}")]
    public IActionResult Test(string searchTerm)
    {                     
        var stringSearchProvider = new StringSearchExpressionProvider();
        var cid = 1;

        //turns IQueryable<CategoryDto>
        var q = _service.GetAll();

        //c
        var parameter = Expression.Parameter(typeof(CategoryCultureDto), "c");
        var property = typeof(CategoryCultureDto).GetTypeInfo().DeclaredProperties
            .Single(p => p.Name == "Name");

        //c.Name
        var memberExpression = Expression.Property(parameter, property);
        //searchTerm = Foo
        var constantExpression = Expression.Constant(searchTerm);

        //c.Name.Contains("Foo")
        var containsExpression = stringSearchProvider.GetComparison(
            memberExpression,
            "co",
            constantExpression);

        //cultureExpression = (c.CultureId == cultureId)
        var cultureProperty = typeof(CategoryCultureDto)
            .GetTypeInfo()
            .GetProperty("CultureId");

        //c.CultureId
        var cultureMemberExp = Expression.Property(parameter, cultureProperty);

        //1
        var cultureConstantExp = Expression.Constant(cid, typeof(int));

        //c.CultureId == 1
        var equalsCulture = (Expression) Expression.Equal(cultureMemberExp, cultureConstantExp);

        //(c.CultureId == 1) && (c.Name.Contains("Foo"))
        var bothExp = (Expression) Expression.And(equalsCulture, containsExpression);

        // c => ((c.CultureId == 1) && (c.Name.Contains("Foo"))
        var lambda = Expression.Lambda<Func<CategoryCultureDto, bool>>(bothExp, parameter);

        //x
        var categoryParam = Expression.Parameter(typeof(CategoryDto), "x");

        //x.Globals.Any(c => ((c.CultureId == 1) && (c.Name.Contains("Foo")))
        var finalExpression = ProcessListStatement(categoryParam, lambda);

        //x => (x.Globals.Any(c => ((c.CultureId == 1) && (c.Name.Contains("Foo"))))
        var finalLambda = Expression.Lambda<Func<CategoryDto, bool>>(finalExpression, categoryParam);

        var query = q.Where(finalLambda);

        var list = query.ToList();

        return Ok(list);
    }


    public Expression GetMemberExpression(Expression param, string propertyName)
    {
        if (!propertyName.Contains(".")) return Expression.Property(param, propertyName);
        var index = propertyName.IndexOf(".");
        var subParam = Expression.Property(param, propertyName.Substring(0, index));
        return GetMemberExpression(subParam, propertyName.Substring(index + 1));
    }

    private Expression ProcessListStatement(ParameterExpression param, LambdaExpression lambda)
    {
        //you can inject this as a parameter so you can apply this for any other list property
        const string basePropertyName = "Globals";
        //getting IList<>'s generic type which is CategoryCultureDto in this case
        var type = param.Type.GetProperty(basePropertyName).PropertyType.GetGenericArguments()[0];
        //x.Globals
        var member = GetMemberExpression(param, basePropertyName);
        var enumerableType = typeof(Enumerable);
        var anyInfo = enumerableType.GetMethods()
        .First(m => m.Name == "Any" && m.GetParameters().Length == 2);
        anyInfo = anyInfo.MakeGenericMethod(type);
        //x.Globals.Any(c=>((c.Name.Contains("Foo")) && (c.CultureId == cid)))
        return Expression.Call(anyInfo, member, lambda);
    }