Collection parameter translation fails with nontrivial expressions

[bachratyg]

See marked row below

Repro:

using Microsoft.EntityFrameworkCore;

var codes = new string[] { "a", "b", "c" };

using var db = new AppDb();
var q = from s in db.Stuffs
        where codes.Any(c => c.StartsWith(s.Code))  // <--- this causes the problem
        select s;

Console.WriteLine(q.ToQueryString());

class AppDb : DbContext
{
    public DbSet<Stuff> Stuffs => this.Set<Stuff>();
    protected override void OnConfiguring(DbContextOptionsBuilder builder)
        => builder.UseOracle("Data Source=whatever");
}
class Stuff
{
    public int Id { get; set; }
    public required string Code { get; set; }
}

Exception thrown:

System.InvalidOperationException: 'Query root of type 'ParameterQueryRootExpression' wasn't handled by provider code. This issue happens when using a provider specific method on a different provider where it is not supported.'

This works in MSSQL via json arrays (i.e. OPENJSON) and could have a semantically equivalent translation via JSON_TABLE (should be supported in 19+)

If the indicated line is quasi-trivial e.g. codes.Contains(s.Code) or codes.Any(c => c == s.Code) then translation succeeds.

Versions:

• Oracle.EntityFrameworkCore 8.23.50
• Oracle.ManagedDataAccess.Core 23.5.1

[alexkeh]

This feature requires primitive collections, which is request #347. Thus, I've marked this as a duplicate request.