When upgrading to 8.0.2, using LINQ's [all] and [contains] will cause translation failure.

[Fatorin]

Hi, I'm getting an error message when using version 8.0.2.

System.InvalidOperationException : The LINQ expression '__param_MapTags_0
    .All(e => StructuralTypeShaperExpression: 
        MapInfo
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .MapTags
        .Contains(e))' could not be translated. Additional information: Translation of method 'System.Linq.Enumerable.Contains' failed.

Data Model

public class MapInfo
{
    [Key]
    public Guid Guid { get; set; }
    [Required]
    public string UserId { get; set; } = null!;
    public List<MapTag>? MapTags { get; set; }
}
public enum MapTag
{
	Group1 = 1000,
	Group2 = 1001,
	Group3 = 1002,
}

Here is my query code, it's work on Microsoft.EntityFrameworkCore 8.0.1 Npgsql 8.0.1 Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0.

IQueryable<MapInfo> query = dbContext.MapInfo.AsNoTracking();
query = query.Where(map => map.MapTags != null && param.MapTags.All(tag => map.MapTags.Contains(tag)));

But I not sure this issue is cause by EFCORE or npgsql, so I ask this question here first.

Update Add exception error for trace.

  訊息: 
System.InvalidOperationException : The LINQ expression '__param_MapTags_0
    .All(e => StructuralTypeShaperExpression: 
        MapInfo
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .MapTags
        .Contains(e))' could not be translated. Additional information: Translation of method 'System.Linq.Enumerable.Contains' 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 'System.Linq.Enumerable.Contains' 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.

  堆疊追蹤: 
QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
NpgsqlSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression, Boolean applyDefaultTypeMapping)
RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression, Boolean applyDefaultTypeMapping)
RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
Database.CompileQuery[TResult](Expression query, Boolean async)
QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
EntityFrameworkQueryableExtensions.LongCountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
MapService.StartQuery(String queryUserId, IQueryable`1 query, Nullable`1 sortType, Int32 pageIndex, Int32 pageSize) 行 1123
MapService.GetQueryResult(String queryUserId, GetMapInfoCardsBySearch_InvokeParameter param) 行 104
MapService.GetMapInfoCardsBySearch(String queryUserId, GetMapInfoCardsBySearch_InvokeParameter param) 行 59
MapService_Test.GetMapInfoCardsBySearch_WithTags_ShouldReturnSuccess() 行 728
--- End of stack trace from previous location ---

[Fatorin]

I follow this branch source of test case, and I found my OnModelCreating setting is wrong. But I don't know, why wrong setting can work on old version.... Wrong Setting

modelBuilder.Entity<MapInfo>().Property(e => e.MapTags).HasConversion<List<int>>();

Correct Setting

modelBuilder.Entity<MapInfo>(info =>
{
	info.PrimitiveCollection(c => c.MapTags).ElementType(e => e.HasConversion(typeof(EnumToNumberConverter<MapTag, int>)));
});

[WhatzGames]

Could you provide a minimal repro? Also, if you want to know if it is a more general issue, you could just change the provider and see if the same issue occurs.

[Fatorin]

Sure, here is minimal repro. https://github.com/Fatorin/TestEFCoreIssue I will change other provider to test later, thanks your reply.

[WhatzGames]

Minimal Console repro:

using Microsoft.EntityFrameworkCore;

await using TestContext context = new();

await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

await context.MapInfo
    .Where(x => x.Tags != null && x.Tags.All(tag => x.Tags.Contains(tag)))
    .ToArrayAsync();

public class TestContext : DbContext
{
    public DbSet<MapInfo> MapInfo => Set<MapInfo>();
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Database=efcore;UserName=postgres");
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MapInfo>(x =>
        {
            x.Property(y => y.Tags).HasConversion<List<int>>();
        });
        base.OnModelCreating(modelBuilder);
    }
}

public class MapInfo
{
    public int Id { get; set; }
    public string Type { get; set; } = null!;
    public List<MapTag>? Tags { get; set; }
}

public enum MapTag
{
    Group1,
    Group2,
    Group3
}

Leaving Npgsql.EntityFrameworkCore.PostgreSQL at 8.0.0 and Npgsql at 8.0.1, but upgrading Microsoft.EntityFrameworkCore from 8.0.1 to 8.0.2 breaks the query. The problem persists when upgrading it to 8.0.5.

@roji I'm not to sure about this one, but to me it looks like either it's an issue for dotnet/efcore or there was a bug before efcore 8.0.2 that efcore.pg relied on. I did find some issues in the 8.0.2 milestone related to Contains and a specific one related to conversions

[EatSleepBitcoin]

I'm sure you're already aware, but in case not: this same error occurs when calling .Any() on a List<string> property that's stored in the database as text[].

[roji]

Sorry for not answering this earlier.

The cause of the error here is the value conversion, i.e. .HasConversion<List<int>>(); as a general rule, the moment you configure value conversion, you can no longer (reliably) call methods on the property inside LINQ queries: EF has no way of knowing what it is that you're doing in your value conversion, and operations expressed in LINQ (on the pre-converted value) can't be reliably applied in the database (on the post-converted value).

Note that above specifically, the value conversion is completely superfluous and can be removed: Lists of enums are already automatically mapped to int arrays in the database. I've confirmed that once the value conversion is removed, the test query works properly.

In any case, I'll put this on the backlog to check the exception at some point, but this definitely won't happen any time soon, and you should generally avoid composing operators (All, Contains) on value-converted collections.

Slightly simplified repro code

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

await context.MapInfo
    // .Where(x => x.Tags.All(tag => x.Tags.Contains(tag)))
    .Where(x => x.Tags.Contains(MapTag.Group2))
    .ToArrayAsync();

public class BlogContext : DbContext
{
    public DbSet<MapInfo> MapInfo => Set<MapInfo>();

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<MapInfo>().Property(y => y.Tags).HasConversion<List<int>>();
}

public class MapInfo
{
    public int Id { get; set; }
    public List<MapTag>? Tags { get; set; }
}

public enum MapTag { Group1, Group2, Group3 }