Início / user-7571526

Hille's questions

Martin Hope
Hille
Asked: 2025-04-23 18:26:46 +0800 CST
  • 6

Tenho uma tabela com o campo string Data { get; set; }do tipo jsonb:

modelBuilder.Entity<LayoutEntity>(b =>
{
    b.Property(p => p.Data).IsRequired().HasColumnName("data").HasColumnType("jsonb");
    ...
}

Agora estou tentando consultar com base em algum subvalor do valor jsonb:

SELECT *
FROM layouts
WHERE jsonb_path_exists(data, '$.** ? (@."$type" == "DataMatrix")')
  AND jsonb_path_exists(data, '$.** ? (@."$type" == "Text")');

O problema é que parece não haver tradução de nenhuma consulta relacionada a subvalor para o ef core, pois estou criando um filtro com base em diferentes fatores.

public ActionResult GetFilteredLayouts(
    [FromQuery] string? name,
    [FromQuery] string? description,
    [FromQuery(Name = "barcodeTypes")] string? barcodeTypesQuery)
{
    var query = DbSetEntity.AsNoTracking().AsQueryable();
    if (name.IsNotNullOrEmpty())
        query = query.Where(x =>
            EF.Functions.ILike(x.DisplayName, "%" + name + "%"));

    if (description.IsNotNullOrEmpty())
        query = query.Where(x =>
            EF.Functions.ILike(x.Description, "%" + description + "%"));

    var barcodeTypes = barcodeTypesQuery?.Split(',').ToArray();
    if (barcodeTypes is not null)
    {
        foreach (var barcodeType in barcodeTypes)
        {
            query = query.Where(x =>
                PgJsonbFunctions.JsonbPathExists(x.Data, $"$.** ? (@.\"$type\" == \"{barcodeType}\")"));
            ...

Já tentei mapeá-lo com:

modelBuilder
    .HasDbFunction(typeof(PgJsonbFunctions).GetMethod(nameof(PgJsonbFunctions.JsonbPathExists),new[] { typeof(string), typeof(string) }))
    .HasName("jsonb_path_exists")
    .IsBuiltIn();

// Error:
> Npgsql.PostgresException (0x80004005): 42883: function jsonb_path_exists(jsonb, text) does not exist

// and/or

[DbFunction("jsonb_path_exists", IsBuiltIn = true)]
public static bool JsonbPathExists(string jsonb, string path)
    => throw new NotSupportedException("This method is for use with EF Core LINQ only.");

// Error:
System.InvalidOperationException: The LINQ expression 'DbSet<LayoutEntity>()
    .OrderBy(l => l.Id)
    .Where(l => PgJsonbFunctions.JsonbPathExists(
        jsonb: l.Data, 
        path: __Format_0))' could not be translated. Additional information: Translation of method 'Sanofi.AMEF5.Service.RESTApiServer.DataContexts.PgJsonbFunctions.JsonbPathExists' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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.

// or

public class JsonbPathExistsTranslator : IMethodCallTranslator
public class CustomNpgsqlMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
// No error as nothing happened

mas nenhuma delas funcionou.

Existe alguma outra solução para simplesmente fazer uma consulta no JSON em uma cadeia de múltiplas consultas?

c#