LINQ to Entities string based dynamic Where

Question

I have an MVC site that utilizes the Kendo Grid and I'm attempting to implement dynamic filters. The data I'm displaying contains several 1-to-many tables. For example, I have a row of people, each person can have 0 or more Items assigned to them. I'm displaying a flattened list in the grid:

Bob  | Item 1, Item 2
Jane | Item 3

If I were to hard-code the filter on the Items column, it would look like:

people.Where(p=> p.Items.Any(i=>i.name.contains("Item 1"))).ToList()

I want to come up with a generic way to build the expression tree so I can filter on different 1-to-many fields and also perform different comparisons (e.g. contains, startswith, equals, etc.). Ideally I would have an extension method with the following syntax:

public static IQueryable<TEntity> Where( this IQueryable<TEntity> source, string tableName, string fieldName, string comparisonOperator, string searchVal) where TEntity : class

Then I could query on multiple one-to-many tables:

if(searchOnItems)
    persons = persons.Where("Items", "name", "Contains", "item 1);
if(searchOnOtherTableName)
    persons = persons.Where("OtherTableName", "name", "Equals", "otherSearchValue);
persons.ToList();

I'm attempting to use LINQ to Entities string based dynamic OrderBy as a starting point since the concept is similar, but I can't figure out how to modify the GenerateSelector method. Any ideas would be greatly appreciated.

Edit - My code is on a closed network, so I'll do my best replicating what I'm trying. Here is the code I'm attempting to modify. The comment block is where I'm stuck. The examples on calling the "Where" extension method above are still valid.

public static IQueryable<TEntity> Where<TEntity>(this IQueryable<TEntity> source, string tableName, string fieldName, string comparisonOperator, string searchVal) where TEntity : class
{
    MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "Where", tableName, fieldName, comparisonOperator, searchVal);
    return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
}

private static MethodCallExpression GenerateMethodCall<TEntity>(IQueryable<TEntity> source, string methodName, string tableName, String fieldName, string comparisonOperator, string searchVal) where TEntity : class
{
    Type type = typeof(TEntity);
    Type selectorResultType;
    LambdaExpression selector = GenerateSelector<TEntity>(tableName, fieldName, comparisonOperator, searchVal, out selectorResultType);
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
                    new Type[] { type, selectorResultType },
                    source.Expression, Expression.Quote(selector));
    return resultExp;
}

private static LambdaExpression GenerateSelector<TEntity>(string tableName, String fieldName, string comparisonOperator, string searchVal, out Type resultType) where TEntity : class
{
    // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
    var parameter = Expression.Parameter(typeof(TEntity), "Entity");

    PropertyInfo property = typeof(TEntity).GetProperty(tableName, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);;
    Expression propertyAccess = Expression.MakeMemberAccess(parameter, property);;

    /************************************************/
    //property is now "TEntity.tableName"
    //how do I go another step further so it becomes "TEntity.tableName.comparisonOperator(searchVal)"
    /************************************************/

    resultType = property.PropertyType;
    // Create the order by expression.
    return Expression.Lambda(propertyAccess, parameter);
}       

Answer 1

I'm attempting to use LINQ to Entities string based dynamic OrderBy as a starting point since the concept is similar, but I can't figure out how to modify the GenerateSelector method.

There is a significant difference between methods that expect selector like Select, OrderBy, ThenBy etc. versus the methods that expect predicate like Where, Any etc. The later cannot use the above GenerateMethodCall because it assumes 2 generic arguments (new Type[] { type, selectorResultType }) while the predicate methods use just 1 generic argument.

Here is how you can achieve your goal. I've tried to make it in a way so you can follow each step of the expression building.

public static IQueryable<TEntity> Where<TEntity>(this IQueryable<TEntity> source, string collectionName, string fieldName, string comparisonOperator, string searchVal) where TEntity : class
{
    var entity = Expression.Parameter(source.ElementType, "e");
    var collection = Expression.PropertyOrField(entity, collectionName);
    var elementType = collection.Type.GetInterfaces()
        .Single(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IEnumerable<>))
        .GetGenericArguments()[0];
    var element = Expression.Parameter(elementType, "i");
    var elementMember = Expression.PropertyOrField(element, fieldName);
    var elementPredicate = Expression.Lambda(
        GenerateComparison(elementMember, comparisonOperator, searchVal),
        element);
    var callAny = Expression.Call(
        typeof(Enumerable), "Any", new[] { elementType },
        collection, elementPredicate);
    var predicate = Expression.Lambda(callAny, entity);
    var callWhere = Expression.Call(
        typeof(Queryable), "Where", new[] { entity.Type },
        source.Expression, Expression.Quote(predicate));
    return source.Provider.CreateQuery<TEntity>(callWhere);
}

private static Expression GenerateComparison(Expression left, string comparisonOperator, string searchVal)
{
    var right = Expression.Constant(searchVal);
    switch (comparisonOperator)
    {
        case "==":
        case "Equals":
            return Expression.Equal(left, right);
        case "!=":
            return Expression.NotEqual(left, right);
    }
    return Expression.Call(left, comparisonOperator, Type.EmptyTypes, right);
}