Efcore: Enum with string converstion and projection

[macwier]

I have an entity with an enum property. That property is a VARCHAR in db. EF is configured to use the string converter.
All that works ok. But when I try to have a query, where I cast the enum type to another enum type, I get an exception:
Microsoft.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'Whatever' to data type int.

Steps to reproduce

``` c#
public class Foo
{
public int Id { get; set; }
public FooStatus FooStatus { get; set; }
}

public enum FooStatus
{
Value1,
Value2
}

public class FooDto
{
public int Id { get; set; }
public FooStatusDto FooStatus { get; set; }
}

public class FooStatusDto
{
Value1,
Value2
}

public class FooDbContext : DbContext
{
public DbSet Foos { get; set; }
}

Query:
```c#
var query = dbContext.Foos.Select(foo => new FooDto()
{
    Id = foo.Id,
    FooStatus = (FooStatusDto)foo.FooStatus
});

var result = query.ToList(); // <-- exception

I understand that this is a gimmicky way of doing this, but it does work in EF Core 2.x.
Is there any way I can have an IQueryable<FooDto> with a different enum type?

For the context, I have an OData endpoint, where I expose Dto's (and expose an internal nugget package with the DTO types).
I don't want to have the entity types in that package. I could just use the FooStatusDto in the EF model, but I"m a bit hesitant to rewrite everything (while upgrading from EF 2.X to EF 3.x).

Stack trace:

 SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    SqlDataReader.TryHasMoreRows(Boolean& moreRows)
    SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
    SqlDataReader.Read()
    RelationalDataReader.Read()
    Enumerator.MoveNext()

Further technical details

EF Core version: 3.1.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .Net 4.72

[ajcvickers]

@macwier I have not been able to reproduce this--see my code below. Please attach a small, runnable project or post a small, runnable code listing (like below) that reproduces what you are seeing so that we can investigate.

```C#
public class Foo
{
public int Id { get; set; }
public FooStatus FooStatus { get; set; }
}

public enum FooStatus
{
Value1,
Value2
}

public class FooDto
{
public int Id { get; set; }
public FooStatusDto FooStatus { get; set; }
}

public enum FooStatusDto
{
Value1,
Value2
}

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

        context.Add(new Foo {FooStatus = FooStatus.Value2});

        context.SaveChanges();
    }

    using (var context = new SomeDbContext())
    {
        var query = context.Foos.Select(foo => new FooDto()
        {
            Id = foo.Id,
            FooStatus = (FooStatusDto)foo.FooStatus
        });

        var result = query.ToList();
    }
}

}

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

public DbSet<Foo> Foos { get; set; }

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

}
```

[macwier]

Sorry for the delay, was on vacation without access to PC.
Below an example that fails. The missing piece was adding the .HasConversion<string>() on the enum property.

    public enum FooStatus
    {
        Value1,
        Value2
    }

    public enum FooStatusDto
    {
        Value1,
        Value2
    }

    public class Foo
    {
        public int Id { get; set; }
        public FooStatus FooStatus { get; set; }
    }

    public class FooDto
    {
        public int Id { get; set; }
        public FooStatusDto FooStatus { get; set; }
    }

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

        public DbSet<Foo> Foos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseLoggerFactory(Logger)
                .EnableSensitiveDataLogging()
                .UseSqlServer("data source=.; initial catalog='EfCoreEnumIssue'; integrated security=true;");           
        }

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

        }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            using (var context = new SomeDbContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.Add(new Foo { FooStatus = FooStatus.Value2 });

                context.SaveChanges();
            }

            using (var context = new SomeDbContext())
            {
                var query = context.Foos.Select(foo => new FooDto()
                {
                    Id = foo.Id,
                    FooStatus = (FooStatusDto)foo.FooStatus
                });

                var result = query.ToList();
            }
        }
    }

[ajcvickers]

@smitpatel Still fails on latest daily build. Query DebugView:

```C#
DbSet()
.Select(foo => new FooDto{
Id = foo.Id,
FooStatus = (FooStatusDto)foo.FooStatus
}
)

```sql
SELECT [f].[Id], CAST([f].[FooStatus] AS int) AS [FooStatus]
FROM [Foos] AS [f]

[ajcvickers]

Closing this as a duplicate of #21956 #10434 with a note there to consider this case.

[gojanpaolo]

@ajcvickers Do you mean dup of #10434 ?

[macwier]

@ajcvickers I understand that this is the same area, but from the looks of it, it involves a lot more complex scenarios (thus, won't be comming anytime soon). Is there any chance this gets handled separately? I wouldn't ask if it wasn't for the fact that this did work in 2.x and now it creates a pretty big problem for me to upgrade to 3.x (and later 5.x, since it's a strech goal there).

If its not possible to handle separately- does anyone see other alternatives/workarounds other than:

• Using the FooStatusDto in EF Model
• Droping the value conversion
  ?

[ajcvickers]

@smitpatel Any workaround for this case?

[smitpatel]

```C#
var query = context.Foos.Select(foo => new
{
Id = foo.Id,
FooStatus = foo.FooStatus
}).AsEnumerable()
.Select(foo => new FooDto()
{
Id = foo.Id,
FooStatus = (FooStatusDto)foo.FooStatus
});
````

[ajcvickers]

@smitpatel Right. It was client-eval in 2.x; should have realized that.

[macwier]

That won't do the trick for me, as I need filtering/ordering to work after the projection.
I'm not sure how it used to work in 2.x internally, but I could do this:

using (var context = new SomeDbContext())
{
    var query = context.Foos.Select(foo => new FooDto()
    {
        Id = foo.Id,
        FooStatus = (FooStatusDto)foo.FooStatus
    });

    var result = query
        .Where(fooDto => fooDto.FooStatus == FooStatusDto.Value1)
        .ToList();
}

And it would result in filtering in database.

[gojanpaolo]

@macwier You're code needs a client-side evaluation (i.e. .Select before .Where) that is not supported starting EF Core 3. https://docs.microsoft.com/en-us/ef/core/querying/client-eval
You can move the .Select statement at the very end and EF Core 3 should work as expected:
```c#
context.Foos
.Where(...)
.Select(foo => new FooDto()
{
...
});

If you want to mimic how EF Core 2 handled your original code, you could add a `.ToList()` after the `.Select` statement:

var query = context.Foos.Select(...).ToList();
var result = query.Where ...
```

[macwier]

@gojanpaolo
That's my whole point :) In EF Core 2.x I could do that cast and still do filtering which would be executed on SQL Server side.

I cannot do the filtering before the projection since after the projection I return IQueryable<Whatever> from controller. The filtering/ordering is applied dynamically by odata.