Entity Framework Core 3.1 Enum Conversion failed when converting the nvarchar value 'EnumValue' to data type int

Question

I'm using Entity Framework Core 3.1 and trying to do a simple query on an enum property in my entity in my localdb and I keep getting this error:

Enum conversion failed when converting the nvarchar value 'Accountant' to data type int

Entity:

public class DemoEntity
{
    [Key]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Position Position { get; set; }
}

Enum - Position:

public enum Position
{
    [Display(Name = "Accountant")]
    Accountant,
    [Display(Name = "Chief Executive Officer (CEO)")]
    ChiefExecutiveOfficer,
    [Display(Name = "Integration Specialist")]
    IntegrationSpecialist,
    [Display(Name = "Junior Technical Author")]
    JuniorTechnicalAuthor,
    [Display(Name = "Pre Sales Support")]
    PreSalesSupport,
    [Display(Name = "Sales Assistant")]
    SalesAssistant,
    [Display(Name = "Senior Javascript Developer")]
    SeniorJavascriptDeveloper,
    [Display(Name = "Software Engineer")]
    SoftwareEngineer
}

DbContext:

public class DemoDbContext : DbContext
{
    public DemoDbContext(DbContextOptions options)
        : base(options) { }

    public DbSet<DemoEntity> Demos { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //var converter = new ValueConverter<Position, string>(
        //                    v => v.ToString(),
        //                    v => (Position)Enum.Parse(typeof(Position), v));

        //var converter = new EnumToStringConverter<Position>();

        modelBuilder
            .Entity<DemoEntity>()
            .Property(e => e.Position);
            //.HasColumnType("nvarchar(max)")
            //.HasConversion<string>();
            //.HasConversion(converter);
    }
}

I tried adding value conversions:

1. .HasConversion<string>() - didn't work
2. .HasConversion(converter) - didn't work

When I query the table as follows I'm getting the conversion error

try
{
    var test = await query.Where(x => x.Position.Equals(Position.Accountant)).ToListAsync();
}
catch (System.Exception e)
{
    //throw;
}

The Position is of type NVARCHAR(MAX) in my database.

Am I missing anything simple? Not able to figure where I'm going wrong. Please assist.

Answer 1

Since the underlying database column type is string, association value converter is the correct mapping (by default EF Core maps enums to int).

So something like this is a must

modelBuilder.Entity<DemoEntity>()
    .Property(e => e.Position)
    .HasConversion<string>();

Which leads to the original problem. Looks like you are hitting EF Core query translation bug - the expression

x.Position.Equals(Position.Accountant)

is wrongly translated to something like

WHERE [d].[Position] = 0

instead of expected

WHERE [d].[Position] = N'Accountant'

The solution is to use comparison operators (== and !=) instead of Equals:

x.Position == Position.Accountant

which translates correctly.

In general avoid Equals (and Compare, CompareTo etc.) methods when there is corresponding C# operator.