O PostgreSQL possui um tipo de dados Jsonb que permite adicionar propriedades adicionais ao modelo relacional padrão, com a capacidade de pesquisar por eles.
O EntityFramework Core com extensão Npgsql pode extrair dados de campo para o tipo System.String
No entanto, para filtrar pelas propriedades Json por meio do EF no nível da consulta, é necessário usar SQL puro, o que não é muito conveniente, pois é necessário mapear (se não for automático), procurar os nomes dos campos que correspondem às propriedades dos modelos, dar suporte a essa nomeação. A flexibilidade que o ORM nos dá está perdida.
Se isso deprime você, assim como eu, bem-vindo ao gato.
No final do artigo, há um link para a fonte!
Indique as tarefas
Como desenvolvedor, quero ter uma ferramenta para acessar os campos Jsonb com o objetivo de filtrar e classificar por eles, que:
- Será compatível com o EntityFramework Core 2 (nós o usamos)
- Não será necessário escrever o SQL você mesmo ao trabalhar com ele
- Funcionará com estrutura Json plana (dentro de json, existem apenas propriedades json)
Acrescentarei que existe o Npgsql.Json.NET , que pode projetar os valores Json e Jsonb nos tipos de CLR. Para ser sincero, não entendo para que serve, porque, como precisávamos de um campo Json em um banco de dados relacional, provavelmente possuímos entidades com um conjunto dinâmico de campos.
O algoritmo para resolver o problema
- Defina um método (ou métodos) que cubra nossas necessidades.
- Crie um tradutor que participe da geração do código SQL.
- Dane-se tudo ao Npgsql.
Solução
Primeiro, definimos um método. Eu quero que seja usado algo como isto:
context.Entity.Where(x => JsonbMethods.Value<string>(x.JsonbField, "jsonPropertyName") == "value")
Portanto, aqui está o nosso método:
public static TSource Value<TSource>(object jsonbProperty, string jsonbPropertyName) { throw new NotSupportedException(); }
Por várias horas eu estava escolhendo as fontes do EF Core, Npgsql e não apenas em busca de maneiras de estender a funcionalidade básica da geração SQL. Cheguei a este artigo , mas não gostei da abordagem do autor ao método de conexão do tradutor, pois redefine a ferramenta padrão, o que significa que pode entrar em conflito com outra ferramenta semelhante.
Como resultado, cheguei à fonte do Net Topology Suite. Tudo o que eu precisava era de uma maneira de conectar um tradutor de métodos.
Mas na maioria das vezes gastei na geração do fragmento sql que eu precisava.
Aqui está a sintaxe necessária
tableAlias."JsonField"->>"insideProperty"
No começo, tentei no tradutor retornar o ColumnExpression. Ao criá-lo, o primeiro parâmetro é o nome da coluna (string). Eu apenas cozinhei a partir dos parâmetros que me chegam no método. Lançado, verificado, erro. Acontece que o que eu passo como nome está entre aspas. Como resultado, o SQL acabou sendo tableAlias.""JsonField"->>"insideProperty"" .
No código-fonte do gerador, encontrei o método VisitColumn no qual esse comportamento era codificado e não dependia de nenhum parâmetro. Ou seja, eu não poderia afetá-lo. Era necessário procurar outra solução.
Em seguida, criei minha própria Expression - JsonbPropertyAccessorExpression: Expression
Resta substituir o método Accept para ISqlExpressionVisitor .
Mas o problema é que nessa interface não existe um método que um operador personalizado possa segmentar. Ocorreu-me visitar não um método, mas vários. Primeiro visitei o VisitColumn , que criou o acesso ao tableAlias. "JsonField", depois VisitSqlFragment , no qual joguei "->>'insideFieldName'" .
Eu não esperava, mas funcionou. Quase.
Quando tentei filtrar por texto por coincidência exata, por algum motivo, esse filtro tableAlias."JsonField"->>"insideProperty" = JSONB "value" foi tableAlias."JsonField"->>"insideProperty" = JSONB "value" , que causou um erro, pois é impossível converter o texto em tipo JSONB se ele não contiver Json válido . E por que preciso levar algo a algo quando quero um texto?
Até tomei a decisão de remover a marca da coluna Jsonb do modelo de mapeamento, que é Jsonb, adicionando apenas essa marca ao MigrationContext para gerar as migrações corretas. E até decolou, mas a abordagem me pareceu uma muleta. No entanto, parei por aí.
Depois disso, defino como CAST, porque o método Value é universal e pode haver diferentes tipos de dados nas propriedades Json, que também precisam ser classificadas e filtradas.
Como resultado, comecei a retornar ExplicitCastExpression do meu tradutor, para o qual passei minha Expression personalizada e o tipo que estava contido nos argumentos universais do método Value .
quando examinei o SQL resultante ao pesquisar por data, descobri que o valor comparado foi convertido no tipo de carimbo de data e hora. timestamp 'some date value' . E então me dei conta. O problema anterior, que resolvi com uma muleta, desapareceu por si só. Quando o acessador foi convertido no texto no campo Json, o gerador não adicionou mais uma conversão explícita ao JSONB, porque já havia texto à esquerda da operação de comparação e, por padrão, o acessador do campo Jsonb retorna o tipo Jsonb.
No final
Concluindo, quero acrescentar que não encontrei documentação sobre como adicionar tradutores personalizados de propriedades e métodos. provavelmente parecendo mal. Se alguém tiver comentários sobre a abordagem, o código etc., escreva nos comentários.
Se alguém quiser expandir a biblioteca em garfos, escreva em uma carta pessoal, tentarei ajudar. Bem, ou jogue pullrequests.
Aqui está o link para a fonte