使用的EntityFramework，该条款 .OrderBy（X =＆GT; x.Title.StartsWith（富））导致SQL WHERE （标题LIKE'富％'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 

没有退出

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

使用逃生

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 到 LIKE ，而不是 CHARINDEX 是new在EF 4.0

Translating String.StartsWith, String.EndsWith and String.Contains to LIKE rather than CHARINDEX was new in EF 4.0

看着 System.Data.Entity的定义，版本= 4.0.0.0 反射器的相关功能似乎是（在系统。 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();
}

所以它只是很难codeD总是使用转义并返回被忽略的标志。

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

这样的版本EF只是追加 ESCAPE〜所有 LIKE 查询。

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

这似乎有什么东西已经在最近code碱基进行了改进。

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是因为已经显示了同样的code。需要注意的是，现在通过假作为第二个参数，并使用输出参数标志只追加在必要的 ESCAPE。