为什么使用案例而不是在何处从表达中生成查询？

Question

我正在使用一种工具，该工具将过滤字符串转换为Linq/efcore表达式。给出了以下过滤字符串：

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

使用EFCORE扩展程序I可以过滤类似的模型：

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

问题是，生成的查询使用case 而不是其中语句：

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)

而不是：

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

最后一个查询需要更少的时间。为什么使用case而不是在哪里？是否可以指示解析器在哪里使用？

扩展：

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

扩展程序试图将过滤器字符串作为JSON文档解析，并从中创建LINQ表达式。它的逻辑在于过滤器Expressexparser：

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

Answer 1

对于组合谓词，您已使用了位运算符expression.and and expression.or bitwise and shift operators

在C＃生成的结果中看起来像是

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

这样，EF也试图将位操作转换为SQL 。

而不是他们使用expression.andalso和expression.orelse 布尔逻辑运算符

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

用于分析生成的表达式，我建议使用 Realable expressions.visualizers.visualizers ，您可能会自己发现错误。