PgNameAttribute is ignored for enums in non-null SQL literal generation

[JuanZamudioGBM]

I'm using EF Core 2.1.2 and Npgsql.EntityFrameworkCore.PostgreSQL 2.1.1.1

The table is defined like this:

create type party_country_type as enum ('CITIZENSHIP', 'RESIDENCE', 'BIRTH');

create table party_country (
 party_id uuid not null,
 country_code char(3) null,
 type party_country_type not null
);

I have an enum defined like this

public enum CountryType
{
 [PgName("CITIZENSHIP")]
 Citizenship = 0,

 [PgName("RESIDENCE")]
 Residence = 1,

 [PgName("BIRTH")]
 Birth = 2,
}

The context like this:

public class CrmContext : DbContext
{
 static CrmContext()
 {
 NpgsqlConnection.GlobalTypeMapper.MapEnum<CountryType>("party_country_type");
 }

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
 modelBuilder.ForNpgsqlHasEnum(
 "party_country_type", 
 new[] { "CITIZENSHIP", "RESIDENCE", "BIRTH" });
 }
}

And finally the class like this:

[Table("party_country")]
public class PartyCountry : EntityBase
{
 [Required]
 [Column("party_id")]
 public Guid PartyId { get; set; }

 [Required]
 [StringLength(3)]
 [MinLength(3)]
 [Column("country_code")]
 public string CountryCode { get; set; }

 [Required]
 [Column("type")]
 public CountryType CountryType { get; set; }
}

The above code works OK when inserting or updating, but when trying to search for an existing one it fails with the above message, for example the query in the code is

var parties = this.CrmContext
 .PartyCountry
 .Where(x => x.CountryType == CountryType.Birth)
 .FirstOrDefault();

Npgsql.PostgresException: '22P02: invalid input value for enum party_country_type: "birth"'

Its something wrong wit my code? Is there a workaround to run this? Or do i have to change my enums to lowercase?

Thanks

[austindrenski]

@JuanZamudioGBM Could you try updating the provider to 2.1.2 and report back?

Also, to keep package versions aligned, you can reference just the provider, which itself brings in the proper EF Core dependency:

<Project Sdk="Microsoft.NET.Sdk">
 <PropertyGroup>
 <LangVersion>latest</LangVersion>
 <TargetFramework>netstandard2.0</TargetFramework>
 </PropertyGroup>
 <ItemGroup>
 <!-- In most cases, only the provider's package needs to be listed -->
 <!-- <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.2" /> -->
 <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.1.2" />
 </ItemGroup>
</Project>

[JuanZamudioGBM]

@JuanZamudioGBM Could you try updating the provider to 2.1.2 and report back?

Also, to keep package versions aligned, you can reference just the provider, which itself brings in the proper EF Core dependency:

<Project Sdk="Microsoft.NET.Sdk">
 <PropertyGroup>
 <LangVersion>latest</LangVersion>
 <TargetFramework>netstandard2.0</TargetFramework>
 </PropertyGroup>
 <ItemGroup>
 <!-- In most cases, only the provider's package needs to be listed -->
 <!-- <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.2" /> -->
 <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.1.2" />
 </ItemGroup>
</Project>

@austindrenski done, but i got the same error, anything else I can try?

[austindrenski]

After taking another look, I think this is a bug on our side. Basically, PgNameAttribute isn't being picked up in NpgsqlEnumTypeMapping.GenerateNonNullSqlLiteral.

This went unnoticed by our test suite because our tests use equivalent name casing in both the CLR and PostgreSQL enums (e.g. Happy to happy or ABC_1 to ABC_1).

I'm starting on a patch, but in the meantime I think you should be able to work around this by parametrizing the literal in your query like so:

var birth = CountryType.Birth;
var parties = 
 this.CrmContext
 .PartyCountry
 .Where(x => x.CountryType == birth)
 .FirstOrDefault();

Alternatively, you could also work around it by renaming your CLR enum's members to match the casing of their database counterparts, thus eliminating the need for the PgNameAttribute.

Additional information

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/blob/e7bea847e458f9aa68ff14c663ba0f9f46359537/src/EFCore.PG/Storage/Internal/Mapping/NpgsqlEnumTypeMapping.cs#L61-L64

[JuanZamudioGBM]

Thanks a lot for your time

[austindrenski]

Blocked by #685.
Scratch that—no need to block.