Translate ToString on enums with number store types as a CASE/WHEN expression

[Danevandy99]

This is a spin-off of #20604, addressing a specific scenario related to the translation of ToString() as a database-side cast.

The goal of #20604 is to translate ToString() as a database-side cast. Let's say I have an entity:

public class Order
{
    public int OrderId { get; set; }
    public OrderStatus Status { get; set; }
}

WIth an OrderStatus enum:

public enum OrderStatus
{
    New,
    Processing,
    Shipped,
    Delivered
}

And a DbContext, where the Status property is stored in the database as a string:

public class MyContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Order>()
            .Property(o => o.Status)
            .HasConversion<string>();
    }
}

Currently, I can cast the Status enum property to a string and call .Contains off of that if I want the ability to search statuses:

db.Orders.Where(x => ((string)(object)x.Status).Contains("Del"));

Which gets translated to the following SQL:

SELECT [o].[OrderId], [o].[Status]
FROM [Order] AS [o]
WHERE CHARINDEX(N'Del', CAST([o].[Status] AS nvarchar(max))) > 0

After #20604 is resolved, this will allow the following C# code:

db.Orders.Where(x => x.Status.ToString().Contains("Del"));

To also translate to the SQL above.

The results of this translated database query match what a client-side evaluation of that LINQ query would return when the Status property is stored in the database as a string, but will return different results from a client-side evaluation if the property is stored in the database as a number.

In order to align the results of both the client-side evaluation and database query (similar to the reasoning behind #14205), when the Status property is stored as a number, I would like to see the following C# code:

db.Orders.Where(x => x.Status.ToString().Contains("Del"));

Translated to the following SQL:

SELECT [o].[OrderId], [o].[Status]
FROM [Order] AS [o]
WHERE CHARINDEX(N'Del', CASE
    WHEN [o].[Status]= 0 THEN 'New'
    WHEN [o].[Status] = 1 THEN 'Processing'
    WHEN [o].[Status] = 2 THEN 'Shipped'
    WHEN [o].[Status] = 3 THEN 'Delivered'
    ELSE ''
END) > 0

[roji]

This is probably a duplicate of server-side value converters (#10861), since CASE/WHEN is applied in SQL.

[Danevandy99]

@roji In the second scenario above where I'm looking for a CASE/WHEN expression in the translation of the LINQ query, the Status property is stored in the database as number with no value converter specified on the property in the OnModelCreating function. I still want the value to be stored as a number and the Status property as the OrderStatus type, but when I call ToString(), I would like to use the CASE/WHEN expression to convert the number in the database to the correct string representation ("New", "Processing", ... rather than "0", "1", ...).

#10861 seems like it would fit this issue if I wanted access to the column to ALWAYS be mapped to a CASE/WHEN expression, storing the value as a number in the database and specifying the type on the Status property as string instead of OrderStatus.