Efcore.pg: Exception when doing a Contains query against an enum field with conversion to string

[alfeg]

After upgrading to EFCore 3.1 we got an issue with filtering by multiple enums.

Repro:

Npgsql.EfCore.Repro.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.0" />
  </ItemGroup>
</Project>

Program.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Npgsql.Logging;

namespace Npgsql.EfCore.Repro
{
    class Program
    {
        static async Task Main(string[] args)
        {
            NpgsqlLogManager.Provider = new ConsoleLoggingProvider(NpgsqlLogLevel.Trace, true);
            NpgsqlLogManager.IsParameterLoggingEnabled = true;

            await using var dbContext = new MyContext();
            await dbContext.Database.ExecuteSqlRawAsync(@"CREATE table if not exists entity (
    id serial NOT NULL,
    status text NOT NULL,
    CONSTRAINT entity_pk PRIMARY KEY (id)
);");

            SomeEnum[] possibleValues = { SomeEnum.One, SomeEnum.Three };

            // fails with: Npgsql.PostgresException: '42883: operator does not exist: text = integer'
            // Generated SQL: 
            // DEBUG Executing statement(s):
            // SELECT e.id, e.status
            //   FROM entity AS e
            //    WHERE COALESCE(e.status = ANY($1), FALSE)
            // Parameters:     $1: System.Int32[]
            var result = await dbContext.My.Where(e => possibleValues.Contains(e.Status)).ToListAsync();
        }
    }

    public enum SomeEnum : int
    {
        One = 1,
        Two = 2,
        Three = 3
    }

    [Table("entity")]
    public class MyEntity
    {
        [Key][Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }

        [Column("status")]
        public SomeEnum Status { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<MyEntity> My { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
               optionsBuilder.UseNpgsql(@"Server=.");         
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MyEntity>()
                .Property(x => x.Status)
                .HasConversion(new EnumToStringConverter<SomeEnum>());
        }
    }
}

This code works fine in 3.0 netcore.

Now this line

var result = await dbContext.My.Where(e => possibleValues.Contains(e.Status)).ToListAsync();

Throws : Npgsql.PostgresException: '42883: operator does not exist: text = integer' with generated SQL:

--DEBUG Executing statement(s):
SELECT e.id, e.status
FROM entity AS e
WHERE COALESCE(e.status = ANY ($1), FALSE)
--        Parameters:     $1: System.Int32[]

P.S.: Repro is mostly copied from https://github.com/npgsql/efcore.pg/issues/1189 issue.

[roji]

It's currently not possible for method translation to work correctly on properties which use value converters, see https://github.com/dotnet/efcore/issues/10434. It's unlikely that this code actually worked before 3.0 (without triggering client evaluation).

As in #1189, instead of using value converters to map .NET enums to database strings, consider using PostgreSQL native enum support which would obviate the need for value converters.

[alfeg]

@roji - but it's easy to reproduce
If You change csproj dependencies from 3.1.0 to 3.0.1

   <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.0.1" />

Then following SQL will be generated. No code need to be changed

--DEBUG Executing statement(s):
SELECT e.id, e.status
FROM entity AS e
WHERE e.status IN ('One', 'Three')

I can't use native enum in postgres - we already have big tables with string enum's.

This issue block us from updating to 3.1 untill there is no simple workaround in mapping

[roji]

I missed that this worked in 3.0.

This is due to improved translation of array.Contains(value) in 3.1: we now translate this to value = ANY (array) when array is a parameter, which 3.0 did not do. The default EF Core translation for these cases would be to expand the array parameter into constants, generating different query SQL every time:

c# var values = new[] { 1, 2, 3 }; var result = await ctx.Blog.Where(b => values.Contains(b.Id)).ToList(); // Generates WHERE b.Id IN (1, 2, 3) values = new[] { 1, 2, 3, 4 }; var result = await ctx.Blog.Where(b => values.Contains(b.Id)).ToList(); // Generates WHERE b.Id IN (1, 2, 3, 4)

Generating the different SQL every time is heavier inside EF Core, and also doesn't allow reuse of prepared statements - it's all pretty bad for perf. Since 3.1 we generate WHERE b.Id = ANY (@values), which is much better - but unfortunately creates your specific issue because of the use of value converters.

I understand your predicament but I'm not sure what to tell you... Rolling this change back would hurt performance for anyone using contains on parameterized arrays - that doesn't seem like the right thing to do. If you can't change your database, another option is to remove the value converter and have string properties on your entities; you could still have another property exposing those strings as .NET enums (effectively doing the conversion yourself), for nicer access of loaded properties, but you wouldn't be able to use them in LINQ queries. I know it's not a good situation, but the combination of value converters and method translation simply is not supported at the EF Core level at this point...

Let me know your thoughts.

[alfeg]

but unfortunately creates your specific issue because of the use of value converters.

I'm not sure why enum arrays can't be converted using same converter provided by value converter in mapping before submitting to sql generation. This would fix issue. I expect for enums those arrays will never be very big.

Since EF 2.1 this were a good working solution. For me this is quite a breaking change for minor update from 3.0 to 3.1, but since issue is closed I assume this will not be fixed.

Thanks for Your time. I will replace generic array calls with concrete calls with specific enums, i.e. a.Status == State.A || a.Status == State.B etc.

[roji]

Reopening to take a deeper look at this. If you can, please hold off a bit with the upgrade - I may be able to do something about it.

[roji]

This has also been fixed by #1196 ~(will be released with 3.1.1)~.

[roji]

Apologies for the confusion, #1196 is too big and complex to be released in a patch release - and workarounds do exist. So this will only be fixed in 5.0.

[yehuda-alon]

I have come across the very same issue. The easy workaround I ended up using was very simple:
Cast the values to string within the query. I suppose your code will look like:
SELECT e.id, e.status
FROM entity AS e
WHERE e.status.ToString() IN ('One', 'Three')