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?

@TiesonT. _Entity Framework Core 2.0.2_, I will add it to the question. — Serg, Apr 02 '18 at 18:34
Why do you ask here? EF-core has an excellent support forum. Questions about third-party implementations are always opinion-based because everybody has to guess. — Gert Arnold, Apr 02 '18 at 19:54
@GertArnold Is there a question on this site that isn't about third party implementations? — NetMage, Apr 02 '18 at 21:54
@NetMage What I mean is: questions like "why did product X implement feature Y that way?" We can only say, because.... — Gert Arnold, Apr 02 '18 at 21:57
The only thing I'm interested in is whether it makes sense to use both parts of the expression and whether using one of them wouldn't be enough. I will change the beginning of the question so as not to cause unnecessary disputes. — Serg, Apr 03 '18 at 02:31
@NetMage you're right, I missed one closing parenthesis, already fixed. The full version of that SQL contains additional OR-part, but it does not raise any question: _OR (@__term_1 = N'''')_ — Serg, Apr 03 '18 at 02:36
Still, if you ask at the EF-core support site you'll get an answer from people that build the stuff. What can we say? Yes, it's useless, EF6 doesn't do this. By the way, you also forgot an equality in the second predicate. — Gert Arnold, Apr 03 '18 at 07:27
@GertArnold Thank for the correction! Ok, I'll ask there. But which part (`LIKE` or `LEFT+LEN`) is preferable for using alone? I mean which one is more effective? — Serg, Apr 03 '18 at 07:35
I don't know. That would require benchmarking. Generally, in view of sargeability, it's not recommended to convert a column value before filtering (which `LEFT` does). OTOH, `LIKE` doesn't perform too well either. One consolation: the predicates will shortcut, so if the `LIKE` is very selective not many `LEFT` comparisons will be executed. — Gert Arnold, Apr 03 '18 at 07:47
Ok, thank you! I'll also consider these examples: https://stackoverflow.com/questions/9493844/is-there-startswith-or-contains-in-t-sql-with-variables — Serg, Apr 03 '18 at 08:30
Is the translation provider dependent? If so, I would assume the database in question has been tested to verify the `LIKE` is worth it to reduce the testing needed. — NetMage, Apr 03 '18 at 15:34

score 3 · Accepted Answer · answered Apr 04 '18 at 17:50

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.

Well spotted! Sometimes I think they should add a comment to the generated SQL code. Would have been really helpful in this case. — Gert Arnold, Apr 04 '18 at 19:07
It's a really clever solution, I liked it and learnt something new about SQL. Although it's not still clear to me why the empty string can't be filtered by the client. — Serg, Apr 05 '18 at 02:59
@Sergey I think they do that in the final implementation (using static column nullability and parameter value analysis). The thread just shows their process of thinking and addressing the potential issues/use cases. The important is, `LIKE` is used to quickly filter the result using index (when available), but potentially producing false positives. Then the right part is implementing the actual filter. One of the ORM benefits is that it can do such optimizations rather than doing them by hand writing the SQL query. — Ivan Stoev, Apr 05 '18 at 05:50
_[LIKE is used to quickly filter the result using index (when available)]_ thank you, Ivan, I noticed that, in particular, only if the string comparison starts from the beginning, iow it's useful only for _StartWith_. — Serg, Apr 05 '18 at 11:24

Entity Framework: StartsWith to SQL

1 Answers1