Translation of string.Join overload used with List<string> parameter missing

[georg-jung]

For array type mapping, https://www.npgsql.org/efcore/mapping/array.html reads:

Simply define a regular .NET array or List<> property

In my experience, this works great with int[]s or List<int>. I recently came accross a strange inconsistency though, if T is string specificly (edit: if used with string.Join). The array type mapping seems to work fine for string[] but seems to be broken for List<string>. I noticed this when trying to translate a query that contains string.Join with a List<string>. I expected this to be translated as array_to_string, which isn't what happens.

This example hopefully clarifies what I experienced:

using Microsoft.EntityFrameworkCore;
using System.Text.Json;

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

Console.WriteLine(ctx.Database.GenerateCreateScript());
Console.WriteLine();

ctx.Blogs.Add(new() { Name = "FooBlog", TagsList = ["tag 1", "tag 2"], TagsArray = ["tag 3", "tag 4"], RatingsList = [1, 2], RatingsArray = [3, 4] });
await ctx.SaveChangesAsync();

var q = ctx.Blogs.Select(b => new
{
    b.Name,
    b.TagsList,
    b.TagsArray,
    ListTagsJoined = string.Join(", ", b.TagsList), // the translation of this seems wrong
    ArrayTagsJoined = string.Join(", ", b.TagsArray),
    b.RatingsList,
    b.RatingsArray,
    ListRatingsJoined = string.Join(", ", b.RatingsList),
    ArrayRatingsJoined = string.Join(", ", b.RatingsArray),
});

// The projection for ListTagsJoined = string.Join(", ", b.TagsList) is left out
// because it seems to be detected as a duplicate of b.TagsList (without any shaping function).
Console.WriteLine(q.ToQueryString());
Console.WriteLine();

// One might not notice the inconsistency with this query if just looking at the resulting entities,
// because what we queried above is actually what we get here - it seems to be done client side though.
var lst = await q.ToListAsync();
Console.WriteLine(JsonSerializer.Serialize(lst));
Console.WriteLine("----");

var qWorksAsExpected = q.Where(x => x.ArrayTagsJoined.Contains("tag"));
Console.WriteLine(qWorksAsExpected.ToQueryString());
Console.WriteLine();
var lst2 = await qWorksAsExpected.ToListAsync();
Console.WriteLine(JsonSerializer.Serialize(lst2));
Console.WriteLine("----");

var qShouldWorkButThrows = q.Where(x => x.ListTagsJoined.Contains("tag"));
// This throws! If I understand https://www.npgsql.org/efcore/mapping/array.html correctly, it should be the same as qWorksAsExpected.
Console.WriteLine(qShouldWorkButThrows.ToQueryString());
Console.WriteLine();
var lst3 = await qShouldWorkButThrows.ToListAsync();
Console.WriteLine(JsonSerializer.Serialize(lst3));

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql(@"Host=localhost;Username=postgres;Password=postgres;Database=npgsql_efcore_array_translation_repro");
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<string> TagsList { get; set; }
    public string[] TagsArray { get; set; }
    public List<int> RatingsList { get; set; }
    public int[] RatingsArray { get; set; }
}

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.2" />
  </ItemGroup>

</Project>

On my machine, this prints (before it throws and some of the Console.WriteLines aren't executed anymore):

CREATE TABLE "Blogs" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "TagsList" text[] NOT NULL,
    "TagsArray" text[] NOT NULL,
    "RatingsList" integer[] NOT NULL,
    "RatingsArray" integer[] NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id")
);




SELECT b."Name", b."TagsList", b."TagsArray", array_to_string(b."TagsArray", ', ', ''), b."RatingsList", b."RatingsArray", array_to_string(b."RatingsList", ', ', ''), array_to_string(b."RatingsArray", ', ', '')
FROM "Blogs" AS b

[{"Name":"FooBlog","TagsList":["tag 1","tag 2"],"TagsArray":["tag 3","tag 4"],"ListTagsJoined":"tag 1, tag 2","ArrayTagsJoined":"tag 3, tag 4","RatingsList":[1,2],"RatingsArray":[3,4],"ListRatingsJoined":"1, 2","ArrayRatingsJoined":"3, 4"}]
----
SELECT b."Name", b."TagsList", b."TagsArray", array_to_string(b."TagsArray", ', ', ''), b."RatingsList", b."RatingsArray", array_to_string(b."RatingsList", ', ', ''), array_to_string(b."RatingsArray", ', ', '')
FROM "Blogs" AS b
WHERE array_to_string(b."TagsArray", ', ', '') LIKE '%tag%'

[{"Name":"FooBlog","TagsList":["tag 1","tag 2"],"TagsArray":["tag 3","tag 4"],"ListTagsJoined":"tag 1, tag 2","ArrayTagsJoined":"tag 3, tag 4","RatingsList":[1,2],"RatingsArray":[3,4],"ListRatingsJoined":"1, 2","ArrayRatingsJoined":"3, 4"}]
----

Details of the exception thrown:

System.InvalidOperationException
  HResult=0x80131509
  Nachricht = The LINQ expression 'DbSet<Blog>()
    .Where(b => string.Join(
        separator: ", ", 
        values: EF.Property<List<string>>(b, "TagsList")).Contains("tag"))' could not be translated. Additional information: Translation of method 'string.Join' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'string.Join' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Quelle = Microsoft.EntityFrameworkCore
  Stapelüberwachung:
   bei Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   bei Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   bei Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   bei Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   bei Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   bei Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   bei Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   bei Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   bei Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   bei Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   bei Program.<<Main>$>d__0.MoveNext() in D:\git\NpgsqlEfCoreArrayTranslationRepro\Program.cs: Zeile47
   bei Program.<<Main>$>d__0.MoveNext() in D:\git\NpgsqlEfCoreArrayTranslationRepro\Program.cs: Zeile50

When executing the same code while depeding on 7.0.11 instead, the behaviour is equivalent, so this doesn't seem to be a recent regression. (qWorksAsExpected is translated slightly different, but I don't think that has anything to do with this issue.) Output with 7.0.11:

CREATE TABLE "Blogs" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "TagsList" text[] NOT NULL,
    "TagsArray" text[] NOT NULL,
    "RatingsList" integer[] NOT NULL,
    "RatingsArray" integer[] NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id")
);




SELECT b."Name", b."TagsList", b."TagsArray", array_to_string(b."TagsArray", ', ', ''), b."RatingsList", b."RatingsArray", array_to_string(b."RatingsList", ', ', ''), array_to_string(b."RatingsArray", ', ', '')
FROM "Blogs" AS b

[{"Name":"FooBlog","TagsList":["tag 1","tag 2"],"TagsArray":["tag 3","tag 4"],"ListTagsJoined":"tag 1, tag 2","ArrayTagsJoined":"tag 3, tag 4","RatingsList":[1,2],"RatingsArray":[3,4],"ListRatingsJoined":"1, 2","ArrayRatingsJoined":"3, 4"}]
----
SELECT b."Name", b."TagsList", b."TagsArray", array_to_string(b."TagsArray", ', ', ''), b."RatingsList", b."RatingsArray", array_to_string(b."RatingsList", ', ', ''), array_to_string(b."RatingsArray", ', ', '')
FROM "Blogs" AS b
WHERE strpos(array_to_string(b."TagsArray", ', ', ''), 'tag') > 0

[{"Name":"FooBlog","TagsList":["tag 1","tag 2"],"TagsArray":["tag 3","tag 4"],"ListTagsJoined":"tag 1, tag 2","ArrayTagsJoined":"tag 3, tag 4","RatingsList":[1,2],"RatingsArray":[3,4],"ListRatingsJoined":"1, 2","ArrayRatingsJoined":"3, 4"}]
----

Please let me know if there's anything I should further clarify, answer or test.

Not sure if this is related to #3074 or #3092. Probably not, given #3105 (comment).

Same as above but cloneable: https://github.com/georg-jung/NpgsqlEfCoreArrayTranslationRepro

[georg-jung]

I'm not that experienced with EF Core's inner workings, but looking at

efcore.pg/src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs

Lines 91 to 115 in 2382b8b

	private static readonly MethodInfo String_Join1 =
	typeof(string).GetMethod(nameof(string.Join), [typeof(string), typeof(object[])])!;
	private static readonly MethodInfo String_Join2 =
	typeof(string).GetMethod(nameof(string.Join), [typeof(string), typeof(string[])])!;
	private static readonly MethodInfo String_Join3 =
	typeof(string).GetMethod(nameof(string.Join), [typeof(char), typeof(object[])])!;
	private static readonly MethodInfo String_Join4 =
	typeof(string).GetMethod(nameof(string.Join), [typeof(char), typeof(string[])])!;
	private static readonly MethodInfo String_Join_generic1 =
	typeof(string).GetTypeInfo().GetMethods(BindingFlags.Public | BindingFlags.Static | BindingFlags.DeclaredOnly)
	.Single(
	m => m is { Name: nameof(string.Join), IsGenericMethod: true }
	&& m.GetParameters().Length == 2
	&& m.GetParameters()[0].ParameterType == typeof(string));
	private static readonly MethodInfo String_Join_generic2 =
	typeof(string).GetTypeInfo().GetMethods(BindingFlags.Public | BindingFlags.Static | BindingFlags.DeclaredOnly)
	.Single(
	m => m is { Name: nameof(string.Join), IsGenericMethod: true }
	&& m.GetParameters().Length == 2
	&& m.GetParameters()[0].ParameterType == typeof(char));

the reason for this might be:

string[]

ArrayTagsJoined = string.Join(", ", b.TagsArray) uses this overload of string.Join: public static string Join (string? separator, params string?[] value); which is handled by String_Join2

efcore.pg/src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs

Lines 94 to 95 in 2382b8b

	private static readonly MethodInfo String_Join2 =
	typeof(string).GetMethod(nameof(string.Join), [typeof(string), typeof(string[])])!;

---

List<int>

ArrayRatingsJoined = string.Join(", ", b.RatingsArray) uses this overload: public static string Join<T> (string? separator, System.Collections.Generic.IEnumerable<T> values); which is handled by String_Join_generic1

efcore.pg/src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs

Lines 103 to 108 in 2382b8b

	private static readonly MethodInfo String_Join_generic1 =
	typeof(string).GetTypeInfo().GetMethods(BindingFlags.Public | BindingFlags.Static | BindingFlags.DeclaredOnly)
	.Single(
	m => m is { Name: nameof(string.Join), IsGenericMethod: true }
	&& m.GetParameters().Length == 2
	&& m.GetParameters()[0].ParameterType == typeof(string));

---

List<string>

ListTagsJoined = string.Join(", ", b.TagsList) uses this overload: public static string Join (string? separator, System.Collections.Generic.IEnumerable<string?> values);

This overload does not seem to be covered in NpgsqlStringMethodTranslator.

[roji]

Thanks for the good PR @georg-jung!