Query in json column and return parent

[massimolana]

I have a table "VAT" with a json field defined as List.
My achievement is to get the specific VAT which has a specific DeviceCode.

The table is defined as below

[Table(nameof(Vat))]
public class Vat
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
     public List<DeviceCode> DeviceCodes { get; set; } = new();
}

with this specific dbContext

public class BusinessRulesDbContext : DbContext
{
   public BusinessRulesDbContext(DbContextOptions<BusinessRulesDbContext> options) : base(options) {    }

    public DbSet<Vat> Vats { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Vat>().OwnsMany(vat => vat.DeviceCodes, builder => { builder.ToJson(); });
    }
}

the deviceCode object is defined as

public class DeviceCode
{
    public DeviceType DeviceType { get; set; }
    public string Code { get; set; } = string.Empty;
}

[JsonConverter(typeof(JsonStringEnumConverter))]
public enum DeviceType
{
    Web,
    Pos,
    Scale
}

I try to get the VAT rows that has a specific DEVICECODE value with this LINQ expression but it is not working

using ctx = new BusinessRulesContext();
var vat = ctx.Vats
    .Where(p => p.DeviceCodes.Any(c => c.Code == "04"))
    .ToList();

and i get the following exception

The LINQ expression 'DbSet<Vat>()
    .Where(v => EF.Property<List<DeviceCode>>(v, "DeviceCodes")
        .AsQueryable()
        .Any(o => o.Code == "04"))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

where am I wrong?

Packages versions:
Microsoft.EntityFrameworkCore: 7.0.5
Microsoft.EntityFrameworkCore.Relational: 7.0.5
Microsoft.EntityFrameworkCore.SqlServer: 7.0.5

Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: WIN 11 22H2
IDE: Visual Studio 2022 17.5

[maumar]

querying capabilities on JSON collections is currently not supported and tracked here: #28616