Enum ICollection Contains => TRUE = FALSE

[kevinvenclovas]

Hello,

i have a bug or need some help with my code.

await context.Users.Where(x => !x.IsSoftDeleted && (x.MessageGroups.Contains(group) || x.MessageGroups.Contains(MessageGroup.ALL))).ToListAsync();

With this code EfCore generate this SQL:

SELECT ... FROM .. AS a
      WHERE NOT (a."IsSoftDeleted") AND ((TRUE = FALSE) OR (TRUE = FALSE))

So why the Contains generate TRUE = FALSE ?

Databasefield has this Conversion:
entity.Property(e => e.MessageGroups).HasConversion(v => string.Join(',', v), v => v.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(x => x.ToEnum<MessageGroup>()).ToList());

Can someone help me. Thanks!

[ErikEJ]

Which provider and EF Core version?

[kevinvenclovas]

@ErikEJ
Npgsql.EntityFrameworkCore.PostgreSQL => 3.1.4
Microsoft.EntityFrameworkCore => 3.1.9

[ajcvickers]

/cc @roji for PostgreSQL

[ajcvickers]

Possible dupe of #20721, but waiting for @roji to take a look from the PostgreSQL side.

[smitpatel]

Not a dupe of #20721 (no property access in the query). Dupe of #10434

[roji]

As far as I can tell, there's nothing PostgreSQL-specific in the code above - the enum array/list is value-converted to a database string field (i.e. no usage of native PostgreSQL enums).

However, if you use Npgsql.EntityFrameworkCore.PostgreSQL 5.0.0-rc2 and remove the value conversion, everything will work just fine:

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

var group = MessageGroup.SomeGroup;
_ = await ctx.Users
    .Where(x => !x.IsSoftDeleted && (x.MessageGroups.Contains(group) || x.MessageGroups.Contains(MessageGroup.ALL)))
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<User> Users { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class User
{
    public int Id { get; set; }
    public bool IsSoftDeleted { get; set; }
    public List<MessageGroup> MessageGroups { get; set; }
}

public enum MessageGroup
{
    ALL,
    SomeGroup
}

SQL:

SELECT u."Id", u."IsSoftDeleted", u."MessageGroups"
FROM "Users" AS u
WHERE NOT (u."IsSoftDeleted") AND (u."MessageGroups" @> ARRAY[@__group_0]::integer[] OR u."MessageGroups" @> ARRAY[0]::integer[])

[smitpatel]

On main branch, I am not seeing any such SQL. We accurately generate translation failure error as we cannot translate Contains over a string column in database.