Cannot query on converted enum property

[freemstr]

I am using https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions for documentation however
I am getting

where (Convert([messageRecord].Status) == Convert(__eventStatus_0))' could not be translated and will be evaluated locally

I should be able to get a WHERE clause
[messageRecord].Status = 'X'

Steps to reproduce

public class Event
{
 public int Id { get; set; }
 public EventStatus Status { get; set; }
}

public enum EventStatus { Booked = 0, Free = 1}

In the DB Status column is char(1) with either 'X' or NULL (3rd party legacy code)

public partial class EventDB: DbContext
{
...
entity.Property(e => e.Status).HasMaxLength(1).HasConversion(new ValueConverter<EventStatus, char?>(
                    i => i == EventStatus.Booked ? 'X' : null
                    s => s == 'X' ? EventStatus.Booked : EventStatus.Free 
                    ));
...
}

Further technical details

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.6-servicing-10079 initialized '****' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: NoTracking SensitiveDataLoggingEnabled
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'where (Convert([messageRecord].Status) == Convert(__eventStatus_0))' could not be translated and will be evaluated locally.

EF Core version:2.2.6-servicing-10079
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: netstandard2.0
Operating system: Win 10 64bit
IDE: Visual Studio 2019 16.3.5

[smitpatel]

Likely fixed in 3.0

[freemstr]

I do not have an option of going to 3.0, it is not backwards compatible with netstandard2.0/Full framework 4.8

[freemstr]

It was reported fixed/closed in 2.1 but it not working
##11211

[freemstr]

Update
This may be related to the status being defined as char(1) in the database
I added
public char? Status2 { get; set; }
and
entity.Property(e => e.Status2).IsRequired().HasMaxLength(1).HasColumnType("char(1)")

And filtering by Status2 also results in "could not be translated and will be evaluated locally"

from Event in db.EventTable
                             where Event .Status == 'X'
                             select Event 

This is what the Eval is attempting
where (Convert([messageRecord].Status) == 88)

Why is it trying to convert to ASCII code?

I also attempted

public string Status3 { get; set; }
mapped to the same database with char(1) on the DB side
entity.Property(e => e.Status3).IsRequired().HasMaxLength(1).HasColumnType("char(1)")

results in
'([messageRecord].Status == "X")' could not be translated

Any suggestions or workarounds?
Is MS SQL Server char(1) not supported in EF Core?

[freemstr]

If my previous comment is relevant is this the issue we should be looking at?
##15330

What would the solution be? is this going to be back-ported to EF Core 2 ?

[ajcvickers]

@freemstr I think the main issue here (at least in 3.0; this won't get fixed for a 2.x release) is that value converters don't support converting from null to some other value--see #13850. This means that

s => s == 'X' ? EventStatus.Booked : EventStatus.Free

will never get called with null, and instead an attempt will be made to set the property to null.

Things work for two non-null values:

public class Event
{
    public int Id { get; set; }
    public EventStatus Status { get; set; }
}

public enum EventStatus { Booked = 0, Free = 1}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger = LoggerFactory.Create(c => c.AddConsole());

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Event>()
            .Property(e => e.Status)
            .HasMaxLength(1)
            .HasConversion(new ValueConverter<EventStatus, char?>(
                i => i == EventStatus.Booked ? (char?) 'X' : 'Y',
                s => s == 'X' ? EventStatus.Booked : EventStatus.Free
            ));
    }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new Event(),
                new Event {Status = EventStatus.Booked},
                new Event {Status = EventStatus.Free});

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var r1 = context.Set<Event>().Where(e => e.Status == EventStatus.Booked).ToList();
            var r2 = context.Set<Event>().Where(e => e.Status == EventStatus.Free).ToList();
        }
    }
}

[freemstr]

Thank you for your time and detailed explanation
The main takeaway for me is unfortunately

this won't get fixed for a 2.x release

But I am glad that #13850 is still open and looking forward to seeing it implemented.
Hopefully as a separate "HasNullConversion" method which would simplify things a lot for non-nullable properties on the code side. Such as my case.