The data types varchar and nvarchar are incompatible in the '^' operator

[miqueljuan]

Projection to Boolean of Equals comparator of a Enum property (that has custom ValueConverter to string) with one of possibles values of Enum translates to SQL with the new XOR ('^') operator implementation, causing 'The data types varchar and nvarchar are incompatible in the '^' operator'.

dbContext.Orders.Select(order=> new OrderModel {  Paid = order.State == OrderState.Paid }).ToList();

enum OrderState { Unpaid, Paid, ... }

class OrderStateConverter : ValueConverter<OrderState, string> 
{
  private static string ConvertToString(OrderState state)
  {
      return stateswitch
      {
          OrderState.Unpaid=> "U",
          OrderState.Paid => "P",
          ....
          _ => throw new InvalidEnumArgumentException(nameof(state), (int)state, typeof(OrderState))
      };
  }
  private static OrderState ConvertToEnum(string state)
  {
       return state.ToUpperInvariant() switch
       {
           "U" => OrderState.Unpaid,
           "P" => OrderState.Paid,
            ....
           _ => throw new InvalidEnumArgumentException(...)
       };
  }
}

Translate to SQL as

CAST([p0].[State] ^ N''S'' AS bit) AS [Paid]

EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows 11 10.0.22631
IDE: Visual Studio 2022 17.11.6

[roji]

Confirmed regression from 8.0 to 9.0; the new boolean XOR logic likely needs to be applied only when the store type is bit (whereas here we have a value converter).

For the following query with a value-converted enum, we now generate incorrect SQL with XOR logic (with a regular non-converted string the SQL is OK):

_ = await context.Orders
    .Select(order => new { Paid = order.State == OrderState.Paid })
    // The following is OK (non-value-converted string)
    // .Select(order => new { IsNameFoo = order.Name == "Foo" })
    .ToListAsync();

9.0 SQL:

SELECT ~CAST([o].[State] ^ N'Paid' AS bit) AS [Paid]
FROM [Orders] AS [o]

8.0 SQL:

SELECT CASE
    WHEN [o].[State] = N'Paid' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Paid]
FROM [Orders] AS [o]

Full repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

_ = await context.Orders
    .Select(order => new { Paid = order.State == OrderState.Paid })
    // The following is OK (non-value-converted string)
    // .Select(order => new { IsNameFoo = order.Name == "Foo" })
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().Property(o => o.State).HasConversion<OrderStateConverter>();
    }

    private class OrderStateConverter() : ValueConverter<OrderState, string>(
        os => os.ToString(),
        s => Enum.Parse<OrderState>(s));
}

public class Order
{
    public int Id { get; set; }
    public OrderState State { get; set; }
    public string Name { get; set; }
}

public enum OrderState { Unpaid, Paid }

/cc @ranma42 - let me know if this is something you want to take a look at (and have time), as you worked on the original change.

[ranma42]

I will try and tackle this next weekend (aka it's unlikely I will have a PR ready before next week).

(the same issue is likely also affecting the ~ operator)

[ChrisJollyAU]

@roji @ranma42 integer data types are fine as well https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-exclusive-or-transact-sql?view=sql-server-ver16

[ranma42]

@ChrisJollyAU they are fine in that they support the operators (^ and ~), but they might not match the expected Boolean semantics.
For example one could have mapped true to 2 and false as 4, in which case a ^ b would result in a value that is neither true/2 nor false/4 (same goes for ~).

[JamesHill3]

Is there a workaround or a setting that will change this default behavior? All of the enums that we use are stored as strings in the DB for readability. Any query we run in the application that filters by/compares equality of an enum now fails with .Net 9.

For example, the query below now fails (edited for brevity):

PlantsData = await _dbContext
    .PlantItem
    .AsNoTracking()
    .Select(x => new PlantsData
    {
        PlantItemId = x.Id,
        IsProduced = x.InventoryTrackingMethod == InventoryTrackingMethod.ProducedItem, // Fails
        IsProduced2 = x.InventoryTrackingMethod.Equals(InventoryTrackingMethod.ProducedItem), // This doesn't work either
        InventoryTrackingMethod = x.InventoryTrackingMethod
    })
    .Where(x => x.ItemId == itemId)
    .ToArrayAsync();

To bypass the error, I have to modify the query like so (adding .ToString() to each enum):

PlantsData = await _dbContext
    .PlantItem
    .AsNoTracking()
    .Select(x => new PlantsData
    {
        PlantItemId = x.Id,
        IsProduced = x.InventoryTrackingMethod.ToString() == InventoryTrackingMethod.ProducedItem.ToString(),
        InventoryTrackingMethod = x.InventoryTrackingMethod
    })
    .Where(x => x.ItemId == itemId)
    .ToArrayAsync();

If there is no workaround, I'll be required to modify hundreds of queries that compare enums, and since the compiler provides no warning of this issue, if I miss a query among the hundreds, my users will get runtime errors.

Any help would be appreciated!