I am working on a tool, that translates filter strings into linq/efcore expressions. Say following filter string is given:

{
  'condition': 'OR',
  'rules': [
     { 'column': 'Foo', 'value': 'Bar' },
     { 'column': 'Foo', 'value': 'Baz' },
   ]
}

Using an efcore extension I can filter a model like so:

_dbContext.MyModel
    .Filter(filterString)
    .ToList();

The issue is, that the generated query uses case instead of where statements:

SELECT
    *
FROM
    [MyModel] AS [c]
WHERE
    (
        CASE
            WHEN [c].[Foo] = "Bar" THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
        END | CASE
            WHEN [c].[Foo] = "Baz" THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
        END
    ) = CAST(1 AS bit)

instead of:

SELECT
    *
FROM
    [MyModel] AS [c]
WHERE ([c].[Foo] = "Bar" OR [c].[Foo] = "Baz")

The last query takes much less time. Why is case used instead of where? Is it possible to instruct the parser to use where?

Extension:

public static IQueryable<T> Filter<T>(this IQueryable<T> query, string? filter)
{
    if (string.IsNullOrEmpty(filter))
    {
        return query;
    }

    Expression<Func<T, bool>>? predicate;

    try
    {
        predicate = new FilterExpressionParser().ParseExpressionOf<T>(JsonDocument.Parse(filter));
    }
    catch (Exception ex)
    {
        throw new FilterException($"Filter \"{filter}\" could not be parsed into a predicate.", ex);
    }

    return query.Where(predicate);
}

The extension tries to parse the filter string as a json document and create an linq expression from it. The logic for that is inside the FilterExpressionParser:

using System.Linq.Expressions;
using System.Reflection;
using System.Text.Json;

public enum FilterConditionType
{
    AND,
    OR
}

public class FilterExpressionParser
{
    public Expression<Func<T, bool>> ParseExpressionOf<T>(JsonDocument json)
    {
        var param = Expression.Parameter(typeof(T));

        var conditions = ParseTree<T>(json.RootElement, param)!;

        if (conditions.CanReduce)
        {
            conditions = conditions.ReduceAndCheck();
        }

        return Expression.Lambda<Func<T, bool>>(conditions, param);
    }

    private delegate Expression Binder(Expression? left, Expression? right);

    private Expression? ParseTree<T>(JsonElement condition, ParameterExpression parameterExpression)
    {
        Expression? left = null;

        var conditionString = condition.GetProperty(nameof(condition)).GetString()?.ToUpper();

        if (!Enum.TryParse(conditionString, out FilterConditionType gate))
        {
            throw new ArgumentOutOfRangeException(nameof(condition), $"Not expected condition type: {condition}.");
        }

        JsonElement rules = condition.GetProperty(nameof(rules));

        Binder binder = gate == FilterConditionType.AND ? Expression.And! : Expression.Or!;

        Expression? bind(Expression? left, Expression? right) => left == null ? right : binder(left, right);

        foreach (var rule in rules.EnumerateArray())
        {
            string? column = rule.GetProperty(nameof(column)).GetString();
            object? toCompare = value.GetString().GetProperty(nameof(value));

            Expression property = Expression.Property(parameterExpression, column);

            BinaryExpression? right = Expression.Equal(property, Expression.Constant(toCompare, property.Type))

            left = bind(left, right);
        }

        return left;
    }
}

CodePudding user response：

For combining predicates you have used bitwise operators Expression.And and Expression.Or Bitwise and shift operators

In C# generated result looks like

e => (e.Some > 1) & (e.Some < 10) | (e.Some == -1)

So, EF is also trying to convert bit operations to the SQL.

Instead of them use Expression.AndAlso and Expression.OrElse which are Boolean logical operators

e => (e.Some > 1) && (e.Some < 10) || (e.Some == -1)

For analysis generated expressions, I would suggest to use ReadableExpressions.Visualizers and probably you will find mistake by yourself.