Projection not working correctly when using the EnumToStringConverter

[jgveire]

When making use of the EnumToStringConverter the projection of enum properties does not work correctly for DTO's. The following linq query creates an incorrect sql query.

var query = from p in dbContext.Products
            join c in dbContext.Categories on p.CategoryId equals c.Id into temp
            from c in temp.DefaultIfEmpty()
            select new ProductDto
            {
                Id = p.Id,
                Name = p.Name,
                CategoryName = c == null ? "Other" : c.Name,
                CategoryStatus = c == null ? CategoryStatus.Active : c.Status
            };

It creates the query:

SELECT [p].[Id], [p].[Name], CASE
    WHEN [c].[Id] IS NULL
    THEN N'Other' ELSE [c].[Name]
END AS [CategoryName], CASE
    WHEN [c].[Id] IS NULL
    THEN 0 ELSE [c].[Status]
END AS [CategoryStatus]
FROM [Products] AS [p]
LEFT JOIN [Categories] AS [c] ON [p].[CategoryId] = [c].[Id]

But this should be:

-- Note the 'Active' string instead of zero.
SELECT [p].[Id], [p].[Name], CASE
    WHEN [c].[Id] IS NULL
    THEN N'Other' ELSE [c].[Name]
END AS [CategoryName], CASE
    WHEN [c].[Id] IS NULL
    THEN 'Active' ELSE [c].[Status]
END AS [CategoryStatus]
FROM [Products] AS [p]
LEFT JOIN [Categories] AS [c] ON [p].[CategoryId] = [c].[Id]

Below you can find a unit test to reproduce the issue.

Steps to reproduce

Run the unit test below to reproduce the problem.

namespace EnumConverter
{
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;

    [TestClass]
    public class ConverterTest
    {
        [TestMethod]
        public void TestQuery()
        {
            var builder = new DbContextOptionsBuilder();
            builder.UseSqlServer("Server=.\\SQLExpress;Database=EnumTest;Trusted_Connection=True;MultipleActiveResultSets=true");
            var dbContext = new TestContext(builder.Options);

            InitDatabase(dbContext);

            var query = from p in dbContext.Products
                        join c in dbContext.Categories on p.CategoryId equals c.Id into temp
                        from c in temp.DefaultIfEmpty()
                        select new ProductDto
                        {
                            Id = p.Id,
                            Name = p.Name,
                            CategoryName = c == null ? "Other" : c.Name,
                            CategoryStatus = c == null ? CategoryStatus.Active : c.Status
                        };
            var result = query.ToList();
            Assert.AreEqual(2, result.Count);
        }

        private void InitDatabase(TestContext dbContext)
        {
            dbContext.Database.EnsureCreated();
            if (!dbContext.Products.Any())
            {
                dbContext.Products.Add(new Product
                {
                    Name = "Apple",
                    Category = new Category
                    {
                        Name = "Fruit",
                        Status = CategoryStatus.Active
                    }
                });


                dbContext.Products.Add(new Product
                {
                    Name = "Bike"
                });

                dbContext.SaveChanges();
            }
        }
    }

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

        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder
                .Entity<Category>()
                .Property(e => e.Status)
                .HasConversion(new EnumToStringConverter<CategoryStatus>());
        }
    }

    public class Product
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public int? CategoryId { get; set; }

        public Category Category { get; set; }
    }

    public class Category
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public CategoryStatus Status { get; set; }
    }

    public class ProductDto
    {
        public string CategoryName { get; set; }
        public CategoryStatus CategoryStatus { get; set; }
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public enum CategoryStatus
    {
        Active = 0,
        Removed = 1,
    }
}

###Exception

Exception message: Conversion failed when converting the nvarchar value 'Active' to data type int.
Stack trace: 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EnumConverter.ConverterTest.TestQuery() in C:\Users\Jorrit\Source\Repos\EnumConverter\EnumConverter\UnitTest1.cs:line 31

Further technical details

EF Core version: 2.2.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
SQL server: Microsoft SQL Express Server 2016 (SP1) - 13.0.4001.0
IDE: Visual Studio 2019 16.0.3)