通过的EntityFramework StartsWith（）生成的SQL包含计划改变ESCAPE“〜”（波浪）

问题描述

使用的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.

的<一个定义href="http://entityframework.$c$cplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlGen/SqlFunctionCallHandler.cs">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 + "'");
        }
    }

<一个href="https://entityframework.$c$cplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlProviderManifest.cs">SqlProviderManifest.EscapeLikeText是因为已经显示了同样的code。需要注意的是，现在通过假作为第二个参数，并使用输出参数标志只追加在必要的 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 StartsWith（）生成的SQL包含计划改变ESCAPE“〜”（波浪）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！