Entity Framework: StartsWith to SQL

Question

Entity Framework (Core 2.0.2) translate

.Where(t => t.Name.StartsWith(term))

into this SQL

([t].[Name] LIKE @__term_1 + N''%'' AND (LEFT([t].[Name], LEN(@__term_1)) = @__term_1))

Don't the left and right parts (of this SQL splitted by AND) do the same and can't each of them be used independently?

Answer 1

The following EF Core issue tracker thread could shed some light on why it is implemented this way - Query: Improve translation of String's StartsWith, EndsWith and Contains #474 . Here are some important excerpts:

Linq translation for methods Contains, EndsWith and StartsWith that we have in the Relational package uses LIKE operator, which may return incorrect results if the value parameter (what we are searching for) contains wildcard characters, e.g. '%' or '_'.

and then

In general for cases in which LIKE doesn't work well we can fall back to alternative translations that don't rely on LIKE, e.g. for String.StartsWith():

var underscoreAThings = Things.Where(t => t.Name.StartsWith(t.Prefix));

SELECT * FROM Things WHERE CHARINDEX(Prefix, Name) = 1 OR Prefix='';

Note that CHARINDEX() won't match an empty string but String.StartsWith("") always return true, that's why we add the Prefix ='' condition. The main disadvantage of this translation is that it is not sargable. That can be addressed with a hybrid translation, e.g.:

SELECT * FROM Things WHERE Name LIKE Prefix+'%' AND (CHARINDEX(Prefix, Name) = 1 OR Prefix = '');

Shortly, with the current translation they address SQL query sargeability as well as the CLR string.StartsWith method compatibility. In different phases of the EF Core development they used only first or only second approach, and finally get to this hybrid approach.