由EntityFramework生成的SQL StartsWith（）包含计划更改ESCAPE'〜'（波浪号） [英] SQL generated by EntityFramework StartsWith() contains plan altering ESCAPE '~' (tilde)

问题描述

使用EntityFramework，子句 .OrderBy（x => x.Title.StartsWith（foo））导致SQL WHERE （标题LIKE'foo％'ESCAPE'〜'）。

Using EntityFramework, the clause .OrderBy(x => x.Title.StartsWith("foo")) results in the SQL WHERE (Title LIKE 'foo%' ESCAPE '~').

查看完整查询的执行计划，我看到我得到一个不同的计划（一个使用列的非聚集索引），当我删除 ESCAPE'〜'。

Looking at the execution plan for the full query I see that I get a different plan (one making use of the column's non clustered index) when I remove the ESCAPE '~'.

为什么EF尝试转义不需要的字符串，我该如何停止？

Why is EF trying to escape a string which doesn't need it, and how can I make it stop?

推荐答案

多余的 ESCAPE 可以改变基数估计，并给出不同的计划。虽然有趣，我发现它使它更准确，而不是更少在这个测试！

The superfluous ESCAPE can certainly alter cardinality estimates and give a different plan. Though funnily enough I found it make it more accurate rather than less in this test!

CREATE TABLE T
(
Title VARCHAR(50),
ID INT IDENTITY,
Filler char(1) NULL,
UNIQUE NONCLUSTERED (Title, ID)
)

INSERT INTO T
            (Title)
SELECT TOP 1000 CASE
                  WHEN ROW_NUMBER() OVER (ORDER BY @@SPID) < 10 THEN 'food'
                  ELSE LEFT(NEWID(), 10)
                END
FROM   master..spt_values 

没有 Escape

SELECT *
FROM T 
WHERE (Title LIKE 'foo%')

使用 Escape

SELECT *
FROM T 
WHERE (Title LIKE 'foo%' ESCAPE '~')

升级到更新版本的EF或编写自己的自定义 DbProviderManifest 实现我认为你尝试删除 ESCAPE 。

Short of upgrading to a more recent version of EF or writing your own custom DbProviderManifest implementation I think you are out of luck in your attempt at removing ESCAPE.

翻译 String.StartsWith ， String.EndsWith 和 String.Contains 到喜欢而不是 CHARINDEX 是新的EF 4.0

看看定义 System.Data.Entity，Version = 4.0.0.0 在反射器中相关函数似乎是（在$ code> System.Data.SqlClient.SqlProviderManifest ）

Looking at the definition of System.Data.Entity, Version=4.0.0.0 in reflector the relevant function seems to be (in System.Data.SqlClient.SqlProviderManifest)

public override string EscapeLikeArgument(string argument)
{
    bool flag;
    EntityUtil.CheckArgumentNull<string>(argument, "argument");
    return EscapeLikeText(argument, true, out flag);
}

该方法的签名是

internal static string EscapeLikeText(string text, 
                                      bool alwaysEscapeEscapeChar, 
                                      out bool usedEscapeChar)
{

    usedEscapeChar = false;
    if (((!text.Contains("%") && !text.Contains("_")) && (!text.Contains("[") && !text.Contains("^"))) && (!alwaysEscapeEscapeChar || !text.Contains("~")))
    {
        return text;
    }
    StringBuilder builder = new StringBuilder(text.Length);
    foreach (char ch in text)
    {
        switch (ch)
        {
            case '%':
            case '_':
            case '[':
            case '^':
            case '~':
                builder.Append('~');
                usedEscapeChar = true;
                break;
        }
        builder.Append(ch);
    }
    return builder.ToString();
}

所以它只是硬编码，总是使用escape，返回的标志是忽略。

So it is just hardcoded to always use escape and the flag that is returned is ignored.

所以这个版本的EF只是将$ code> ESCAPE'〜'添加到所有的 LIKE 查询。

So that version of EF just appends the ESCAPE '~' to all LIKE queries.

这似乎是在最新的代码库中得到改进的东西。

This seems to be something that has been improved in the most recent code base.

SqlFunctionCallHandler.TranslateConstantParameterForLike的定义是

// <summary>
    // Function to translate the StartsWith, EndsWith and Contains canonical functions to LIKE expression in T-SQL
    // and also add the trailing ESCAPE '~' when escaping of the search string for the LIKE expression has occurred
    // </summary>
    private static void TranslateConstantParameterForLike(
        SqlGenerator sqlgen, DbExpression targetExpression, DbConstantExpression constSearchParamExpression, SqlBuilder result,
        bool insertPercentStart, bool insertPercentEnd)
    {
        result.Append(targetExpression.Accept(sqlgen));
        result.Append(" LIKE ");

        // If it's a DbConstantExpression then escape the search parameter if necessary.
        bool escapingOccurred;

        var searchParamBuilder = new StringBuilder();
        if (insertPercentStart)
        {
            searchParamBuilder.Append("%");
        }
        searchParamBuilder.Append(
            SqlProviderManifest.EscapeLikeText(constSearchParamExpression.Value as string, false, out escapingOccurred));
        if (insertPercentEnd)
        {
            searchParamBuilder.Append("%");
        }

        var escapedSearchParamExpression = constSearchParamExpression.ResultType.Constant(searchParamBuilder.ToString());
        result.Append(escapedSearchParamExpression.Accept(sqlgen));

        // If escaping did occur (special characters were found), then append the escape character used.
        if (escapingOccurred)
        {
            result.Append(" ESCAPE '" + SqlProviderManifest.LikeEscapeChar + "'");
        }
    }

SqlProviderManifest.EscapeLikeText 与已显示的代码相同。请注意，它现在通过 false 作为第二个参数，并使用输出参数标志仅在必要时附加 ESCAPE 。

SqlProviderManifest.EscapeLikeText is the same code as already shown. Note that it now passes false as the second parameter and uses the output parameter flag to only append the ESCAPE where necessary.

这篇关于由EntityFramework生成的SQL StartsWith（）包含计划更改ESCAPE'〜'（波浪号）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！