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

[fingers10]

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.

I modified the query to query.Where(x => x.Position.Equals(Position.Accountant.ToString())).ToListAsync(). Now the query executes but I get zero results.

Expected Result:

I should get one record from the database.

[fingers10]

After some analysis it looks like there is a bug in expression formation. I can see my coding breaking in EF Core 3.1.3 where as it was working in EF Core 2.2.6.

I'm forming the expression dynamically in the run time and that works with EF Core 2.2.6 but breaks in EF Core 3.1.3. I'll share the expression formed in both version here in a day or two

[fingers10]

Here is what I have got.

x.Position.Equals(Position.Accountant) builds expression like WHERE [d].[Position] = 0 instead of WHERE [d].[Position] = N'Accountant'.

But using == like x.Position == Position.Accountant builds correct expression.

Is this is the expected behavior? I need to use == instead of Equals?

[ajcvickers]

@fingers10 I'm not really following your comments about the expression building--are you referring to expressions EF is building, the compiler is building, or that you are building manually?

Looking at your original code, mapping the enum to a string is working for me. Note that you should still use the enum value in the query--EF will convert it to a string via the value converter.

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

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
}

public static class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new DemoEntity { Position = Position.IntegrationSpecialist },
                new DemoEntity { Position = Position.Accountant }
            );
            
            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var test = context
                .Set<DemoEntity>()
                .Where(x => x.Position == Position.Accountant)
                .ToList();

            Console.WriteLine(test[0].Position);
        }
    }
}

public class SomeDbContext : DbContext
{
    private static readonly ILoggerFactory
        Logger = LoggerFactory.Create(x => x.AddConsole()); //.SetMinimumLevel(LogLevel.Debug));

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DemoEntity>().Property(e => e.Position).HasConversion<string>();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(Your.SqlServerConnectionString);
}

[fingers10]

@ajcvickers I'm referring to the expression formed by EF Core. In your above code, you're using == operator in your .Where() condition but if you use .Equals() then the result will be empty. We need to use == always instead of .Equals()?

[ajcvickers]

@fingers10 Thanks!

@smitpatel Enum mapped to a string column. This works:

var test = context
    .Set<DemoEntity>()
    .Where(x => x.Position == Position.Accountant)
    .ToList();

This throws:

var test = context
    .Set<DemoEntity>()
    .Where(x => x.Position.Equals(Position.Accountant))
    .ToList();

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id], [d].[FirstName], [d].[LastName], [d].[Position]
      FROM [DemoEntity] AS [d]
      WHERE [d].[Position] = 0
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'SomeDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value 'IntegrationSpecialist' to data type int.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
         at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
         at Microsoft.Data.SqlClient.SqlDataReader.Read()
         at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
      ClientConnectionId:8005ae9a-5c3c-4779-95d4-eb879babd01c
      Error Number:245,State:1,Class:16
Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value 'IntegrationSpecialist' to data type int.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
ClientConnectionId:8005ae9a-5c3c-4779-95d4-eb879babd01c
Error Number:245,State:1,Class:16
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value 'IntegrationSpecialist' to data type int.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main() in /home/ajcvickers/AllTogetherNow/Daily/Daily.cs:line 61
ClientConnectionId:8005ae9a-5c3c-4779-95d4-eb879babd01c
Error Number:245,State:1,Class:16

[fingers10]

@ajcvickers You're welcome. I'm happy that I'm able to contribute by finding issues.