SQL generated by EntityFramework StartsWith() contains plan altering ESCAPE '~' (tilde)

Question

Using EntityFramework, the clause .OrderBy(x => x.Title.StartsWith("foo")) results in the SQL WHERE (Title LIKE 'foo%' 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 '~'.

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

Answer 1

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 

Without Escape

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

With Escape

SELECT *
FROM T 
WHERE (Title LIKE 'foo%' 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.

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

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

The signature for that method is

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

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

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.

The definition of SqlFunctionCallHandler.TranslateConstantParameterForLike is

// <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 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.

Answer 2

As of Entity Framework 6.2, there is the added support for .Like() as part of DbFunctions.

So now you can do this:

var query = db.People.Where(p => DbFunctions.Like(p.Name, "w%"));

For more info: https://github.com/aspnet/EntityFramework6/issues/241