How to build dynamic query with Where and OR using Expression

user_1160123 0
Viewed 134

I hope somebody can guide and help me with this. We have an inherited project that uses ExpressionHelper class. Basically, this Expression Helper will return an IQueryable that build a dynamic query base on the search term that the user provided.

For example, I have the below code where I pass 2 search terms.

    IQueryable<UserEntity> modifiedQuery = _uow.UserRepository.GetAll();;

    var searchTerms = new List<SearchTerm>
    {
        new SearchTerm { Name = "FirstName", Operator = "eq", Value = "Bob" },
        new SearchTerm { Name = "FirstName", Operator = "eq", Value = "John" }
    };

    foreach (var searchTerm in searchTerms)
    {
        var propertyInfo = ExpressionHelper
            .GetPropertyInfo<TEntity>(searchTerm.EntityName ?? searchTerm.Name);

        var obj = ExpressionHelper.Parameter<TEntity>();

        var left = ExpressionHelper.GetPropertyExpression(obj, propertyInfo);
        var right = searchTerm.ExpressionProvider.GetValue(searchTerm.Value);
        var comparisonExpression = searchTerm.ExpressionProvider
            .GetComparison(left, searchTerm.Operator, right);

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

        // query = query.Where...
        modifiedQuery = ExpressionHelper.CallWhere(modifiedQuery, lambdaExpression);
    }

With the code above and using the below ExpressionHelper class, this generate the below SQL query when I check using SQLProfiler. Please notice the AND in the query. What I actually what is OR.

Constructed QUERY in SQL Profiler

SELECT 
    [Extent1].[FirstName] AS [FirstName], 
    FROM [dbo].[tblUser] AS [Extent1]
WHERE ([Extent1].[Conatact1] = N'Bob') AND ([Extent1].[Contact2] = N'John')

ExpressionHelper.cs

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 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 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>(IQueryable<T> query, LambdaExpression predicate)
    {
        var whereMethodBuilder = QueryableMethods
            .First(x => x.Name == "Where" && x.GetParameters().Length == 2)
            .MakeGenericMethod(new[] { typeof(T) });

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

    public static IQueryable<TEntity> CallOrderByOrThenBy<TEntity>(
        IQueryable<TEntity> modifiedQuery,
        bool useThenBy,
        bool descending,
        Type propertyType,
        LambdaExpression keySelector)
    {
        var methodName = "OrderBy";
        if (useThenBy) methodName = "ThenBy";
        if (descending) methodName += "Descending";

        var method = QueryableMethods
            .First(x => x.Name == methodName && x.GetParameters().Length == 2)
            .MakeGenericMethod(new[] { typeof(TEntity), propertyType });

        return (IQueryable<TEntity>)method.Invoke(null, new object[] { modifiedQuery, keySelector });
    }
}

I have hard time understanding on how the query was created and how do I change it to become OR in the created query.

Hope someone can guide me and point to the right direction. Thank you!

asp.net-web-api2 lambda linq c#
1 Answers

Add to SearchTerm a new property (C# 6.0 syntax here):

    // This is quite wrong. We should have an enum here, but Operator is 
    // done as a string, so I'm maintaining the "style"
    // Supported LogicalConnector: and, or
    public string LogicalConnector { get; set; } = "and";
}

Then:

private static IQueryable<TEntity> BuildQuery<TEntity>(IQueryable<TEntity> modifiedQuery, List<SearchTerm> searchTerms)
{
    Expression comparisonExpressions = null;

    var obj = ExpressionHelper.Parameter<TEntity>();

    foreach (var searchTerm in searchTerms)
    {
        var propertyInfo = ExpressionHelper
            .GetPropertyInfo<TEntity>(searchTerm.EntityName ?? searchTerm.Name);

        var left = ExpressionHelper.GetPropertyExpression(obj, propertyInfo);
        var right = searchTerm.ExpressionProvider.GetValue(searchTerm.Value);
        var comparisonExpression = searchTerm.ExpressionProvider.GetComparison(left, searchTerm.Operator, right);

        if (comparisonExpressions == null)
        {
            comparisonExpressions = comparisonExpression;
        }
        else if (searchTerm.LogicalConnector == "and")
        {
            comparisonExpressions = Expression.AndAlso(comparisonExpressions, comparisonExpression);
        }
        else if (searchTerm.LogicalConnector == "or")
        {
            comparisonExpressions = Expression.OrElse(comparisonExpressions, comparisonExpression);
        }
        else
        {
            throw new NotSupportedException(searchTerm.LogicalConnector);
        }
    }

    if (comparisonExpressions != null)
    {
        // x => x.Property == "Value"
        var lambdaExpression = ExpressionHelper.GetLambda<TEntity, bool>(obj, comparisonExpressions);
        // query = query.Where...
        modifiedQuery = ExpressionHelper.CallWhere(modifiedQuery, lambdaExpression);
    }

    return modifiedQuery;
}

Use it like:

var searchTerms = new List<SearchTerm>
{
    new SearchTerm { Name = "PrimaryContact", Operator = "eq", Value = "Bob" },
    new SearchTerm { Name = "SecondaryContact", Operator = "eq", Value = "Bob" },
    new SearchTerm { Name = "PrimaryContact", Operator = "eq", Value = "John", LogicalConnector = "or", }
};

IQueryable<UserEntity> query = BuildQuery<UserEntity>(modifiedQuery, searchTerms);

Note that there is no way in this code to explicitly set brackets, that will be implicitly set as:

(((A opB b) opC C) opD D)

Where A, B, C, D are the SearchTerm[0], SearchTerm[1], SearchTerm[2], SearchTerm[3] and opB, opC, opD are the operators defined in SearchTerm[1].LogicalConnector, SearchTerm[2].LogicalConnector, SearchTerm[3].LogicalConnector.

While putting brackets is easy, choosing how to "describe" them is complex, unless you change significantly your SearchTerm collection (it couldn't be a "linear" array but it would need to be a tree).

P.S. I was wrong, you don't need an ExpressionVisitor. You need an ExpressionVisitor when you are trying to "merge" multiple LambdaExpressions that have distinct ParameterExpression. In this code we are able to have a single var obj = ExpressionHelper.Parameter<TEntity>() for all the query, so no problems merging the conditions. To make it clear: if you want to "merge" x1 => x1.Foo == "Foo1" with x2 => x2.Foo == "Foo2" then you need an ExpressionVisitor that replaces x2 with x1, otherwise you would get a wrong query like x1 => x1.Foo == "Foo1" || x2.Foo == "Foo2". In the code given we have only x1 (that is var obj = ExpressionHelper.Parameter<TEntity>()), so no problem.

user_6131300
Related