Cosmos SQL query enum array (Contains) to string conversion not working properly

[Kevenvz]

I have an issue with the .Contains query translation in Cosmos. The problem is as follows: My entity has an enum discriminator type, I want to serialize this to a string for better readability. Now when I want to apply .Contains in my query the enum gets serialized as an array of integers instead of an array of strings.

The logs show the following query

info: Microsoft.EntityFrameworkCore.Database.Command[30100] Executing SQL query for container 'Products' in partition 'None' [Parameters=[@__types_0='[1,2]']] SELECT VALUE c FROM root c WHERE (c["Type"] IN ("Menu", "Regular", "Supplement") AND ARRAY_CONTAINS(@__types_0, c["Type"]))

Snippet of the related code

public enum ProductType
{
    Supplement,
    Menu,
    Regular
}

public abstract class Product
{
    public ProductType Type { get; init; }
}

public class MenuProduct : Product
{
    public MenuProduct()
    {
        Type = ProductType.Menu;
    }
}

public class Supplement : Product
{
    public MenuProduct()
    {
        Type = ProductType.Supplement;
    }
}

public class RegularProduct : Product
{
    public RegularProduct()
    {
        Type = ProductType.Regular;
    }
}

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasDiscriminator(x => x.Type)
            .HasValue<RegularProduct>(ProductType.Regular)
            .HasValue<Supplement>(ProductType.Supplement)
            .HasValue<MenuProduct>(ProductType.Menu);

        builder.Property(x => x.Type)
            .HasConversion<string>();
        // Rest is left out
    }
}

ProductType[] types = [ProductType.Menu, ProductType.Regular];
Product[] results = await dbContext.Products
    .Where(p => types.Contains(p.Type))
    .ToArrayAsync();

EF Core version: 9.0.0 Database provider: Microsoft.EntityFrameworkCore.Cosmos Target framework: .NET 9 Operating system: Windows and Linux (Docker) IDE: JetBrains Rider 2024.2.6

[ajcvickers]

Confirmed this is a bug on EF9--full code below. This is a regression from EF8 where we were still inlining constants for Contains:

Query generated by EF8:

info: 11/13/2024 14:22:50.123 CosmosEventId.ExecutingSqlQuery[30100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing SQL query for container 'SomeDbContext' in partition '(null)' [Parameters=[]]
      SELECT c
      FROM root c
      WHERE (c["Type"] IN ("Menu", "Regular", "Supplement") AND c["Type"] IN ("Menu", "Regular"))

Query generated by EF9:

info: 11/13/2024 14:24:44.949 CosmosEventId.ExecutingSqlQuery[30100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing SQL query for container 'SomeDbContext' in partition 'None' [Parameters=[@__types_0='[1,2]']]
      SELECT VALUE c
      FROM root c
      WHERE (c["Type"] IN ("Menu", "Regular", "Supplement") AND ARRAY_CONTAINS(@__types_0, c["Type"]))

Code

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

    context.AddRange(new MenuProduct { Id = "A" }, new Supplement { Id = "B" }, new RegularProduct { Id = "C" });

    await context.SaveChangesAsync();
}

using (var context = new SomeDbContext())
{
    ProductType[] types = [ProductType.Menu, ProductType.Regular];
    Product[] results = await context.Products
        .Where(p => types.Contains(p.Type))
        .ToArrayAsync();
}

public class SomeDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .EnableSensitiveDataLogging()
            .LogTo(Console.WriteLine)
            .UseCosmos(
                "https://localhost:8081",
                "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==",
                "PrimitiveCollections");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(b =>
        {
            b.HasDiscriminator(x => x.Type)
                .HasValue<RegularProduct>(ProductType.Regular)
                .HasValue<Supplement>(ProductType.Supplement)
                .HasValue<MenuProduct>(ProductType.Menu);

            b.Property(x => x.Type)
                .HasConversion<string>();
        });
    }
}

public enum ProductType
{
    Supplement,
    Menu,
    Regular
}

public abstract class Product
{
    public string Id { get; set; }
    public ProductType Type { get; init; }
}

public class MenuProduct : Product
{
}

public class Supplement : Product
{
}

public class RegularProduct : Product
{
}

[Kevenvz]

If anyone is looking for a workaround:

ProductType[] types = [ProductType.Menu, ProductType.Regular];
string[] stringTypes = types.Select(t => t.ToString()).ToArray();
Product[] results = await dbContext.Products
    .Where(p => stringTypes.Contains(EF.Property<string>(p, "Type")))
    .ToArrayAsync();

[ajcvickers]

Note for anyone looking for a workaround, using an object array for the parameters also works:

    object[] types = ["Menu", "Regular"];
    Puffin[] results = await context.Products
        .Where(p => types.Contains(p.Type))
        .ToArrayAsync();

[ajcvickers]

Note for team: a proper fix for this requires implementation of #34026. Initial investigation of this issue is not showing any promising paths for a patch-safe fix, but there is a pretty good workaround.