PostgreSQL verfügt über einen Jsonb-Datentyp, mit dem Sie dem relationalen Standardmodell zusätzliche Eigenschaften hinzufügen und diese durchsuchen können.
EntityFramework Core mit der Erweiterung Npgsql kann System.String Typ System.String ziehen
Um jedoch nach Json-Eigenschaften über EF auf Abfrageebene zu filtern, müssen Sie reines SQL verwenden, was nicht sehr praktisch ist, da Sie in die Zuordnung gehen müssen (wenn dies nicht automatisch erfolgt), nach den Namen der Felder suchen müssen, die den Modelleigenschaften entsprechen, und diese Benennung unterstützen. Die Flexibilität, die uns ORM bietet, geht verloren.
Wenn es Sie und mich deprimiert, willkommen bei cat.
Am Ende des Artikels befindet sich ein Link zur Quelle!
Bezeichnen Sie die Aufgaben
Als Entwickler möchte ich ein Tool für den Zugriff auf Jsonb-Felder haben, mit dem Ziel, nach diesen zu filtern und zu sortieren.
- Es wird mit EntityFramework Core 2 kompatibel sein (wir verwenden es)
- Sie müssen SQL nicht selbst schreiben, während Sie damit arbeiten
- Funktioniert mit flacher Json-Struktur (innerhalb von Json gibt es nur JSON-Eigenschaften)
Ich möchte hinzufügen, dass es Npgsql.Json.NET gibt , das Json- und Jsonb-Werte in CLR-Typen projizieren kann. Um ehrlich zu sein, verstehe ich nicht, wofür es sein könnte, da wir ein Json-Feld in einer relationalen Datenbank benötigen und höchstwahrscheinlich Entitäten mit einem dynamischen Satz von Feldern haben.
Der Algorithmus zur Lösung des Problems
- Definieren Sie eine Methode (oder Methoden), die unsere Anforderungen abdeckt.
- Erstellen Sie einen Übersetzer, der an der Generierung von SQL-Code beteiligt ist.
- Schrauben Sie alles an Npgsql.
Lösung
Zunächst definieren wir eine Methode. Ich möchte, dass es so verwendet wird:
context.Entity.Where(x => JsonbMethods.Value<string>(x.JsonbField, "jsonPropertyName") == "value")
Daher ist hier unsere Methode:
public static TSource Value<TSource>(object jsonbProperty, string jsonbPropertyName) { throw new NotSupportedException(); }
Mehrere Stunden lang suchte ich nach den Quellen von EF Core, Npgsql und nicht nur nach Möglichkeiten, die grundlegenden Funktionen der SQL-Generierung zu erweitern. Ich bin zu diesem Artikel gekommen , aber der Ansatz des Autors zur Verbindung des Übersetzers hat mir nicht gefallen, da er das Standardwerkzeug neu definiert, was bedeutet, dass es zu Konflikten mit einem anderen ähnlichen Werkzeug kommen kann.
Als Ergebnis kam ich zur Quelle der Net Topology Suite. Alles, was ich von dort brauchte, war eine Möglichkeit, einen Methodenübersetzer anzuschließen.
Aber die meiste Zeit habe ich damit verbracht, das benötigte SQL-Fragment zu generieren.
Hier ist die erforderliche Syntax
tableAlias."JsonField"->>"insideProperty"
Zuerst habe ich im Übersetzer versucht, ColumnExpression zurückzugeben. Beim Erstellen ist der erste Parameter der Spaltenname (Zeichenfolge). Ich habe es nur aus den Parametern gekocht, die mir in der Methode einfallen. Gestartet, geprüft, Fehler. Es stellt sich heraus, dass das, was ich als Namen übergebe, in Anführungszeichen gesetzt wird. Infolgedessen stellte sich heraus, dass SQL tableAlias.""JsonField"->>"insideProperty"" .
Im Quellcode des Generators fand ich die VisitColumn Methode, bei der dieses Verhalten VisitColumn war und nicht von Parametern VisitColumn . Das heißt, ich konnte es nicht beeinflussen. Es war notwendig, nach einer anderen Lösung zu suchen.
Dann habe ich meinen eigenen Expression - JsonbPropertyAccessorExpression: Expression
Die Accept Methode für ISqlExpressionVisitor .
Das Problem ist jedoch, dass es in dieser Schnittstelle keine Methode gibt, die ein benutzerdefinierter Operator segmentieren könnte. Dann kam mir der Gedanke, nicht eine, sondern mehrere Methoden zu besuchen. Zuerst besuchte VisitColumn , wodurch der Zugriff auf die tableAlias. "JsonField" -Spalte erstellt wurde, dann VisitSqlFragment , in das ich "->>'insideFieldName'" warf.
Ich habe nicht gehofft, aber es hat funktioniert. Fast.
Als ich aus irgendeinem Grund versuchte, nach Text zu filtern, um eine genaue Übereinstimmung zu tableAlias."JsonField"->>"insideProperty" = JSONB "value" , wurde ein solcher tableAlias."JsonField"->>"insideProperty" = JSONB "value" Filter tableAlias."JsonField"->>"insideProperty" = JSONB "value" , was einen Fehler verursachte, da es unmöglich ist, den Text in den JSONB-Typ zu konvertieren, wenn er keinen gültigen Json enthält . Und warum muss ich etwas zu etwas führen, wenn ich einen Text möchte?
Ich habe sogar beschlossen, die Marke aus der Jsonb-Spalte aus dem Mapping-Modell zu entfernen, da es sich um Jsonb handelt, und nur diese Marke zum MigrationContext hinzuzufügen, damit die richtigen Migrationen generiert werden. Und es ging sogar los, aber die Annäherung schien mir eine Krücke zu sein. Trotzdem blieb ich dort stehen.
Danach habe ich auf CAST gesetzt, da die Value Methode universell ist und die Json-Eigenschaften verschiedene Datentypen enthalten können, die ebenfalls sortiert und gefiltert werden müssen.
Infolgedessen gab ich ExplicitCastExpression von meinem Übersetzer zurück, an den ich meinen benutzerdefinierten Expression und den Typ übergab, der in den universellen Argumenten der Value Methode enthalten war.
Als ich mir die resultierende SQL bei der Suche nach Datum ansah, stellte ich fest, dass der verglichene Wert in den Zeitstempeltyp umgewandelt wurde. timestamp 'some date value' . Und dann dämmerte es mir. Das vorherige Problem, das ich mit einer Krücke gelöst habe, ging von selbst weg. Wenn der Accessor in den Text des Json-Felds umgewandelt wurde, fügte der Generator keine explizite Konvertierung mehr zu JSONB hinzu, da der Vergleichsvorgang bereits links Text enthielt und der Accessor des Jsonb-Felds standardmäßig den Jsonb-Typ zurückgibt.
Am Ende
Abschließend möchte ich hinzufügen, dass ich keine Dokumentation zum Hinzufügen benutzerdefinierter Übersetzer von Eigenschaften und Methoden gefunden habe. wahrscheinlich schlecht aussehen. Wenn jemand Kommentare zum Ansatz, zum Code usw. hat, schreiben Sie diese in die Kommentare.
Wenn jemand die Bibliothek in Gabeln erweitern möchte, schreibe in einen persönlichen Brief, ich werde versuchen zu helfen. Nun, oder Pullrequests werfen.
Hier ist der Link zur Quelle