Bug #121034 Regression in ef core Contains using StringComparison.InvariantCultureIgnoreCase

Description:
Using Contains with StringComparison.InvariantCultureIgnoreCase causes "THREW System.InvalidOperationException Expression [SqlExpressions.SqlFunctionExpression]", was working fine on .net 8.

How to repeat:
using System.Reflection;
using Microsoft.EntityFrameworkCore;

namespace CollateRepro;

public class Car
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ReproContext : DbContext
{
    private readonly string _connectionString;

    public ReproContext(string connectionString) => _connectionString = connectionString;

    public DbSet<Car> Cars { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMySQL(_connectionString);

        // Set REPRO_SQL=1 to print the generated SQL. On net8.0 this shows the COLLATE
        // construct the provider emits for the StringComparison overload.
        if (Environment.GetEnvironmentVariable("REPRO_SQL") == "1")
            optionsBuilder.LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
                .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Car>(e =>
        {
            e.ToTable("cars");
            e.HasKey(x => x.Id);
            e.Property(x => x.Id).HasColumnName("id");
            // varchar under the schema's default (case-insensitive) collation.
            e.Property(x => x.Name).HasColumnName("name").HasColumnType("varchar(255)").IsRequired();
        });
    }
}

public static class Program
{
    public static int Main()
    {
        var connectionString = Environment.GetEnvironmentVariable("REPRO_CONNSTR")
            ?? "server=localhost;port=3399;user id=root;password=repro;database=repro_db;";

        var efVersion = typeof(DbContext).Assembly.GetName().Version;
        var providerVersion = typeof(MySQLDbContextOptionsExtensions).Assembly.GetName().Version;

        Console.WriteLine($"Runtime                       : {Environment.Version}");
        Console.WriteLine($"Microsoft.EntityFrameworkCore : {efVersion}");
        Console.WriteLine($"MySql.EntityFrameworkCore     : {providerVersion}");
        Console.WriteLine();

        using var db = new ReproContext(connectionString);
        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();
        db.Cars.Add(new Car { Id = 20, Name = "TEST2" });
        db.SaveChanges();

        // Search term is lower-case, stored value is upper-case, so a match proves the
        // comparison is case-insensitive.
        const string term = "test2";

        var plain = RunCase(
            "Contains(term)  -- no StringComparison",
            () => db.Cars.Where(f => f.Name.Contains(term)).Select(f => f.Name).ToList());

        var withComparison = RunCase(
            "Contains(term, StringComparison.InvariantCultureIgnoreCase)",
            () => db.Cars
                .Where(f => f.Name.Contains(term, StringComparison.InvariantCultureIgnoreCase))
                .Select(f => f.Name).ToList());

        Console.WriteLine();
        if (plain && withComparison)
        {
            Console.WriteLine("RESULT: both queries succeeded -- issue NOT reproduced.");
            return 0;
        }

        if (plain && !withComparison)
        {
            Console.WriteLine("RESULT: issue REPRODUCED -- only the StringComparison overload fails.");
            return 1;
        }

        Console.WriteLine("RESULT: unexpected -- the plain Contains query also failed.");
        return 2;
    }

    private static bool RunCase(string label, Func<List<string>> query)
    {
        Console.WriteLine($"--- {label}");
        try
        {
            var rows = query();
            Console.WriteLine($"    OK, matched {rows.Count} row(s): [{string.Join(", ", rows)}]");
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"    THREW {ex.GetType().FullName}");
            Console.WriteLine($"    {ex.Message}");
            return false;
        }
    }
}

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFrameworks>net8.0;net10.0</TargetFrameworks>
    <Nullable>disable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <AssemblyName>CollateRepro</AssemblyName>
    <RootNamespace>CollateRepro</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MySql.EntityFrameworkCore" Version="10.0.9" />
  </ItemGroup>

</Project>

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Documenting'