Array indexing over SqlExpression not translated

[kyberias]

Using Npgsql.EntityFrameworkCore.PostgreSQL 9.0.1.

I have the following code:

context.MyTable.Select(m => (m.FolderPath != null && m.FolderPath != "") ? EF.Functions.StringToArray(m.FolderPath, "/")[0] : null)
            .Distinct()
            .ToArray();

This gets translated into the following SQL:

SELECT DISTINCT m.folder_path IS NOT NULL AND m.folder_path <> '', string_to_array(m.folder_path, '/')
      FROM MyTable AS m
      WHERE m.storage = @__storage_0

I'm expecting the Select() to return the first element of the split string and the Distinct() to process that but the translated SQL seems to omit the array index [0] altogether and DISTINCT targets the array. This seems incorrect.

Is this a bug and/or is there a way to circumvent it?

[roji]

When trying to repro the above, I'm seeing a translation failure exception rather than the indexing getting omitted. Can you please look at the code below and tweak it to show the omission, or submit a similar repro?

Attempted repro

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

_ = await context.Blogs.Where(b => EF.Functions.StringToArray(b.Name, "/")[0] == "foo").ToListAsync();

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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

[kyberias]

Here is a complete repro. Note the usage of Select() and Distinct() and the comments.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

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

context.Blogs.Add(new Blog
{
    Name = "a/b/c"
});
context.Blogs.Add(new Blog
{
    Name = "a/c/d"
});
await context.SaveChangesAsync();

var a = await context.Blogs.Select(b => EF.Functions.StringToArray(b.Name, "/")[0]).Distinct().ToListAsync();

// Expect to see a single 'a' (due to Distinct()), but the result set has two 'a's
// SQL executed seems to perform DISTINCT on the whole array, not for first elements of the array.

foreach (var str in a)
{
    Console.WriteLine(str);
}

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=postgres;Password=foobar;Database=BlogTest")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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