Bug optimizing string contains and string startswith for version 8.0.0

[stevenpua]

Greetings! Great job transitioning to .net 8. I got into an issue. Not sure if this is already written.

with Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0

TO REPLICATE: When doing a query

var normalizedSearchString = "my title".ToUpperInvariant();

var q=  from x in db.Blogs
where x.NormalizedName.StartsWith(normalizedSearchString) ||
x.NormalizedBody.Contains(normalizedSearchString)
select x;
q.ToList();

i get an optimized query:

select * from blogs where normalized_name like @normalized_searchstring_0_rewritten escape '\' or 
 normalized_body like @normalized_searchstring_0_rewritten escape '\' 

the desired behaviour should probably be using a different rewritten @normalized_searchstring_1_rewritten. For contains.

TEMPORARY FIX: is to use a different variable

var normalizedSearchString = "my title".ToUpperInvariant();
var normalizedSearchString1 = normalizedSearchString;

var q=  from x in db.Blogs
where x.NormalizedName.StartsWith(normalizedSearchString) ||
x.NormalizedBody.Contains(normalizedSearchString1)
select x;
q.ToList();

proposed code to fix:
in

efcore.pg/src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs

Line 452 in 8402038

var escapedPatternParameter =

replace

var escapedPatternParameter =
    _queryCompilationContext.RegisterRuntimeParameter(patternParameter.Name + "_rewritten", lambda);

with

// use different parameter name for StartsWith, EndsWith, Contains (sec)
var escapedPatternParameter =
    _queryCompilationContext.RegisterRuntimeParameter(patternParameter.Name + _rewritten_sec"+
(int)methodType, lambda);

[roji]

the desired behaviour should probably be using a different rewritten @normalized_searchstring_1_rewritten. For contains.

Why is that? Why would a a different parameter here be better?

[stevenpua]

Hi Roji, I think v8 has an optimization that converts the value of the parameter to be combined with the "%". so the SQL will no longer have to + '%'.

For some reason, the converter uses the same parameter name @normalized_searchstring_0_rewritten escape '' for both StartsWith and Contains. Which causes unwanted behaviour for the query above.

var normalizedSearchString = "my title".ToUpperInvariant();

var q=  from x in db.Blogs
where x.NormalizedName.StartsWith(normalizedSearchString) ||
x.NormalizedBody.Contains(normalizedSearchString)
select x;
q.ToList();

internally the above sql will look like

select * from blogs where normalized_name like 'MY TITLE%' '\' or 
 normalized_body like 'MY TITLE%' escape '\' 

because it is using the same parameter

[roji]

Thanks, I see now - that's indeed a regression in 8.0. I've opened dotnet/efcore#32432 for the EF side (the same bug exists there) and will fix this for EFCore.PG 8.0.1.