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?

user_80434 · Answer

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

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

2 Answers