PostgreSQL memiliki tipe data Jsonb yang memungkinkan Anda untuk menambahkan properti tambahan ke model relasional standar dengan kemampuan untuk mencari melalui mereka.
EntityFramework Core dengan ekstensi Npgsql dapat menarik data bidang ke tipe System.String
Namun, untuk memfilter oleh properti Json melalui EF di tingkat kueri, Anda harus menggunakan SQL murni, yang sangat tidak nyaman, karena Anda perlu masuk ke pemetaan (jika tidak otomatis), cari nama bidang yang sesuai dengan properti model, dukung penamaan ini. Fleksibilitas yang diberikan ORM kepada kami hilang.
Jika itu membuat Anda tertekan, juga saya, selamat datang ke kucing.
Di akhir artikel ada tautan ke sumber!
Nyatakan tugas
Sebagai pengembang, saya ingin memiliki alat untuk mengakses bidang Jsonb dengan tujuan memfilter dan menyortirnya, yang:
- Ini akan kompatibel dengan EntityFramework Core 2 (kami menggunakannya)
- Anda tidak perlu menulis SQL sendiri saat bekerja dengannya
- Akan bekerja dengan struktur Json datar (di dalam json hanya ada properti json)
Saya akan menambahkan bahwa ada Npgsql.Json.NET , yang dapat memproyeksikan nilai Json dan Jsonb ke dalam tipe CLR. Sejujurnya, saya tidak mengerti untuk apa itu, karena karena kami membutuhkan bidang Json dalam basis data relasional, kemungkinan besar kami memiliki entitas dengan rangkaian bidang yang dinamis.
Algoritma untuk memecahkan masalah
- Tetapkan metode (atau metode) yang akan memenuhi kebutuhan kita.
- Buat penerjemah yang akan berpartisipasi dalam pembuatan kode SQL.
- Persetan dengan Npgsql.
Solusi
Pertama, kita mendefinisikan suatu metode. Saya ingin digunakan sesuatu seperti ini:
context.Entity.Where(x => JsonbMethods.Value<string>(x.JsonbField, "jsonPropertyName") == "value")
Karena itu, inilah metode kami:
public static TSource Value<TSource>(object jsonbProperty, string jsonbPropertyName) { throw new NotSupportedException(); }
Selama beberapa jam saya memilih sumber-sumber EF Core, Npgsql dan tidak hanya mencari cara untuk memperluas fungsi dasar generasi SQL. Saya sampai di artikel ini , tetapi saya tidak suka pendekatan penulis dengan metode menghubungkan penerjemah, karena itu mendefinisikan ulang alat standar, yang berarti dapat bertentangan dengan alat serupa lainnya.
Akibatnya, saya sampai ke sumber Net Topology Suite. Yang saya butuhkan dari sana adalah cara untuk menghubungkan penerjemah metode.
Tetapi sebagian besar waktu yang saya habiskan untuk menghasilkan fragmen sql yang saya butuhkan.
Berikut ini sintaks yang diperlukan
tableAlias."JsonField"->>"insideProperty"
Awalnya saya mencoba di penerjemah untuk mengembalikan ColumnExpression. Saat membuatnya, parameter pertama adalah nama kolom (string). Saya hanya memasaknya dari parameter yang datang kepada saya dalam metode ini. Diluncurkan, dicentang, kesalahan. Ternyata apa yang saya sampaikan sebagai nama dibungkus tanda kutip. Akibatnya, SQL berubah menjadi tableAlias.""JsonField"->>"insideProperty"" .
Dalam kode sumber generator, saya menemukan metode VisitColumn di mana perilaku ini adalah hardcode dan tidak bergantung pada parameter apa pun. Artinya, saya tidak bisa memengaruhinya. Itu perlu untuk mencari solusi lain.
Kemudian saya membuat Expression saya sendiri - JsonbPropertyAccessorExpression: Expression
Masih menimpa metode Accept untuk ISqlExpressionVisitor .
Tetapi masalahnya adalah, dalam antarmuka ini tidak ada metode yang dapat disegmentasikan oleh operator kustom. Kemudian terpikir olehku untuk mengunjungi bukan satu metode, tetapi beberapa metode. Pertama mengunjungi VisitColumn , yang menciptakan akses ke tableAlias. "JsonField" kolom, kemudian VisitSqlFragment , di mana saya melemparkan "->>'insideFieldName'" .
Saya tidak berharap, tetapi itu berhasil. Hampir.
Ketika saya mencoba memfilter menurut teks untuk kebetulan yang tepat, untuk beberapa alasan, filter tableAlias."JsonField"->>"insideProperty" = JSONB "value" , yang menyebabkan kesalahan, karena tidak mungkin untuk membuang teks ke tipe JSONB jika tidak mengandung Json yang valid . Dan mengapa saya perlu mengarahkan sesuatu ke sesuatu ketika saya ingin teks?
Saya bahkan membuat keputusan untuk menghapus tanda dari kolom Jsonb dari model pemetaan, bahwa itu adalah Jsonb, hanya menambahkan tanda ini ke MigrationContext sehingga menghasilkan migrasi yang benar. Dan itu bahkan lepas landas, tetapi pendekatan itu bagi saya terasa seperti penopang. Meskipun demikian, saya berhenti di sana.
Setelah itu, saya menetapkan ke CAST, karena metode Value bersifat universal dan mungkin ada berbagai jenis data di properti Json, yang juga perlu disortir dan difilter.
Sebagai hasilnya, saya mulai mengembalikan ExplicitCastExpression dari penerjemah saya, di mana saya melewati Expression kustom saya dan jenis yang terkandung dalam argumen universal metode Value .
ketika saya melihat hasil SQL ketika mencari berdasarkan tanggal, saya menemukan bahwa nilai yang dibandingkan dilemparkan ke tipe timestamp. timestamp 'some date value' . Dan kemudian saya sadar. Masalah sebelumnya, yang saya selesaikan dengan tongkat penyangga, hilang dengan sendirinya. Ketika accessor dilemparkan ke dalam teks ke bidang Json, generator tidak lagi menambahkan konversi eksplisit ke JSONB, karena sudah ada teks di sebelah kiri operasi perbandingan, dan secara default accessor dari bidang Jsonb mengembalikan tipe Jsonb.
Pada akhirnya
Sebagai kesimpulan, saya ingin menambahkan bahwa saya tidak menemukan dokumentasi tentang cara menambahkan penerjemah khusus untuk properti dan metode. mungkin terlihat buruk. Jika seseorang memiliki komentar tentang pendekatan, pada kode, dll, tulis di komentar.
Jika seseorang ingin memperluas perpustakaan dalam garpu, menulis dalam surat pribadi, saya akan mencoba membantu. Nah, atau membuang pullrequests.
Berikut ini tautan ke sumbernya