為什么使用case而不是where陳述句從運算式生成查詢？

我正在開發一種工具，它將過濾器字串轉換為 linq/efcore 運算式。假設給出了以下過濾器字串：

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

使用 efcore 擴展，我可以像這樣過濾模型：

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

問題是，生成的查詢使用case而不是where陳述句：

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使用而不是where？是否可以指示決議器使用where？

擴展名：

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 運算式。其邏輯在 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;
    }
}

uj5u.com熱心網友回復：

為了組合謂詞，您使用了按位運算子Expression.And和Expression.Or 按位和移位運算子

在 C# 中生成的結果看起來像

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

因此，EF 也在嘗試將位操作轉換為 SQL。

而不是他們使用Expression.AndAlsoand Expression.OrElsewhich 是 布爾邏輯運算子

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

對于分析生成的運算式，我建議使用ReadableExpressions.Visualizers，您可能會自己發現錯誤。

標籤：实体框架 林克

上一篇：如何撰寫一個LINQ查詢，該查詢將回傳其ID包含在特定串列中的物體串列？

下一篇：今天和明天日期的LambdaWhere子句