Incorrect parameter rewriting for string StartsWith/EndsWith/Contains

[roji]

When a parameter is used as the pattern for StartsWith/EndsWith/Contains, in 8.0.0 we rewrite that parameter to escape any wildchars. However, when the same parameter is reused in two different such methods (e.g. StartsWith and Contains), the same parameter name is used, with the second overwriting the first.

Repro:

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var s = "foo";
_ = await ctx.Blogs.Where(b => b.Name.StartsWith(s) || b.Body.Contains(s)).ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Body { get; set; }
}

This produces the following query:

Executed DbCommand (48ms) [Parameters=[@__s_0_rewritten='%foo%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [b].[Id], [b].[Body], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Name] LIKE @__s_0_rewritten ESCAPE N'\' OR [b].[Body] LIKE @__s_0_rewritten ESCAPE N'\'

The StartsWith here is translated to the first LIKE, which incorrectly uses the same parameter as the Contains, with %foo% instead of foo%.

The workaround is to use two separate variables.

Introduced in #31482. Originally flagged by @stevenpua for PostgreSQL in npgsql/efcore.pg#2994.

[roji]

Reopening to consider patching.