Geekly articles weekly

Entity Framework 6 dengan Pencarian Teks Lengkap via LINQ

Saya ingin berbagi kruk saya dalam memecahkan masalah yang agak dangkal: bagaimana membuat teman pencarian MSSQL teks lengkap dengan Entity Framework. Topiknya sangat khusus, tetapi menurut saya itu relevan hari ini. Bagi yang berminat, saya minta kucing.


Semuanya dimulai dengan rasa sakit


Saya mengembangkan proyek dalam C # (ASP.NET) dan kadang-kadang menulis layanan microser. Dalam kebanyakan kasus, saya menggunakan database MSSQL untuk bekerja dengan data. Kerangka Entity digunakan sebagai tautan antara database dan proyek saya. Dengan EF, saya mendapatkan banyak peluang untuk bekerja dengan data, menghasilkan kueri yang tepat, mengatur beban di server. Mekanisme ajaib LINQ hanya memikat dengan kemampuannya. Selama bertahun-tahun, saya tidak lagi membayangkan cara yang lebih cepat dan nyaman untuk bekerja dengan database. Tetapi seperti hampir semua ORM, EF memiliki sejumlah kelemahan. Pertama, ini adalah kinerja, tetapi ini adalah topik dari artikel terpisah. Dan kedua, itu mencakup kemampuan dari database itu sendiri.


MSSQL memiliki pencarian teks lengkap bawaan yang berfungsi di luar kotak. Untuk melakukan kueri teks lengkap, Anda dapat menggunakan predikat bawaan (CONTAINS dan FREETEXT) atau fungsi (CONTAINSTABLE dan FREETEXTTABLE). Hanya ada satu masalah: EF tidak mendukung permintaan teks lengkap, dari kata sama sekali!


Saya akan memberikan contoh dari pengalaman nyata. Misalkan saya memiliki tabel artikel - Artikel, dan saya membuat kelas untuknya yang menggambarkan tabel ini:


/// c# public partial class Article { public int Id { get; set; } public System.DateTime Date { get; set; } public string Text { get; set; } public bool Active { get; set; } }

Maka saya perlu memilih artikel-artikel ini, katakanlah, hasilkan 10 artikel terakhir yang diterbitkan:


 /// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray();

Semuanya sangat indah sampai tugas menambahkan pencarian teks lengkap muncul. Karena tidak ada dukungan untuk fungsi pemilihan teks lengkap di EF (.NET core 2.1 sudah memilikinya ), masih menggunakan beberapa perpustakaan pihak ketiga atau menulis kueri dalam SQL murni.


Permintaan SQL dari contoh di atas tidak begitu rumit:


 SELECT TOP (10) [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Text] AS [Text], [Extent1].[Active] AS [Active] FROM [dbo].[Article] AS [Extent1] WHERE [Extent1].[Active] = 1 ORDER BY [Extent1].[Date] DESC

Dalam proyek nyata, banyak hal tidak begitu sederhana. Permintaan ke basis data jauh lebih rumit dan sulit untuk mempertahankannya secara manual. Akibatnya, pertama kali saya menulis kueri menggunakan LINQ, lalu saya mendapatkan teks yang dihasilkan dari kueri SQL ke database, dan sudah memperkenalkan kondisi pemilihan data teks lengkap ke dalamnya. Kemudian saya mengirimkannya ke db.Database.SqlQuery dan menerima data yang saya butuhkan. Ini semua baik, tentu saja, selama permintaan tidak perlu menggantung selusin filter yang berbeda dengan kondisi dan join-us yang kompleks.


Jadi - Saya memiliki rasa sakit tertentu. Kita harus menyelesaikannya!


Mencari solusi


Sekali lagi, duduk di pencarian favorit saya dengan harapan menemukan setidaknya beberapa solusi, saya menemukan repositori ini . Dengan solusi ini, dukungan predikat LINQ (CONTAINS dan FREETEXT) dapat diimplementasikan. Berkat dukungan EF 6 dari antarmuka IDbCommandInterceptor khusus, yang memungkinkan Anda untuk mencegat permintaan SQL yang sudah jadi, solusi ini diimplementasikan sebelum mengirimnya ke basis data. String penanda yang dibuat khusus diganti ke dalam bidang Contains , dan kemudian setelah membuat permintaan, tempat ini diganti dengan predikat Contoh:


 /// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text));

Namun, jika pemilihan data perlu diurutkan berdasarkan peringkat pertandingan, maka solusi ini tidak akan berfungsi lagi dan Anda harus menulis kueri SQL secara manual. Intinya, solusi ini menggantikan LIKE seperti biasa dengan pemilihan predikat.


Jadi, pada tahap ini, saya punya pertanyaan: apakah mungkin untuk mengimplementasikan pencarian teks lengkap nyata menggunakan fungsi MS SQL bawaan (CONTAINSTABLE dan FREETEXTTABLE) sehingga semua ini dapat dihasilkan melalui LINQ dan bahkan dengan dukungan untuk menyortir kueri berdasarkan peringkat pertandingan? Ternyata, Anda bisa!


Implementasi


Untuk memulainya, perlu mengembangkan logika untuk menulis permintaan itu sendiri menggunakan LINQ. Karena dalam query SQL nyata dengan pilihan teks lengkap, BERGABUNG paling sering digunakan untuk bergabung dengan tabel virtual dengan peringkat, saya memutuskan untuk pergi dengan cara yang sama dalam permintaan LINQ.


Berikut adalah contoh dari permintaan LINQ tersebut:


 /// c# var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText)); var query = db.Article .Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new { article.Id, article.Text, fts.Key, fts.Rank, }) .OrderByDescending(n => n.Rank);

Kode seperti itu belum bisa dikompilasi, tetapi sudah secara visual memecahkan masalah mengurutkan data yang dihasilkan berdasarkan peringkat. Tetap mempraktikkannya.


Kelas tambahan FTS_Int digunakan dalam permintaan ini:


 /// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }

Nama tidak dipilih secara kebetulan, karena kolom kunci di kelas ini harus bertepatan dengan centang pada kolom kunci di tabel pencarian (dalam contoh saya, dengan [Article].[Id] ketik int ). Jika Anda perlu membuat kueri di tabel lain dengan jenis kolom kunci lainnya, saya berasumsi untuk hanya menyalin kelas yang sama dan membuat Kunci dari jenis yang diperlukan.


Kondisi untuk pembentukan kueri teks lengkap seharusnya diteruskan dalam variabel queryText . Untuk membentuk teks variabel ini, fungsi terpisah diimplementasikan:


 /// c# string queryText = FtsSearch.Query( dbContext: db, //   ,       ftsEnum: FtsEnum.CONTAINS, //  : CONTAINS  FREETEXT tableQuery: typeof(News), //       tableFts: typeof(FTS_Int), //    search: "text"); //

Pemenuhan permintaan yang siap dan akuisisi data:


 /// c# var result = FtsSearch.Execute(() => query.ToList());

Fungsi akhir FtsSearch.Execute wrapper digunakan untuk menghubungkan sementara antarmuka IDbCommandInterceptor . Dalam contoh yang disediakan oleh tautan di atas, penulis lebih suka menggunakan algoritma substitusi kueri secara konstan untuk semua permintaan. Akibatnya, setelah menghubungkan mekanisme penggantian permintaan, setiap permintaan mencari kombinasi yang diperlukan untuk penggantian. Opsi ini tampak boros bagi saya, oleh karena itu, pelaksanaan permintaan data itu sendiri dilakukan dalam fungsi yang dikirimkan, yang sebelum panggilan menghubungkan permintaan penggantian otomatis dan, setelah panggilan, memutusnya.


Aplikasi


Saya menggunakan kelas model data generasi otomatis dari database menggunakan file edmx. Karena Anda tidak bisa menggunakan kelas FTS_Int dibuat di EF karena kurangnya metadata yang diperlukan di DbContext , saya membuat tabel nyata sesuai dengan modelnya (mungkin seseorang tahu cara yang lebih baik, saya akan senang atas bantuan Anda dalam komentar):


Cuplikan layar dari tabel yang dibuat di file edmx



 CREATE TABLE [dbo].[FTS_Int] ( [Key] INT NOT NULL, [Rank] INT NOT NULL, [Query] NVARCHAR (1) NOT NULL, CONSTRAINT [PK_FTS_Int] PRIMARY KEY CLUSTERED ([Key] ASC) );

Setelah itu, ketika memperbarui file edmx dari database, tambahkan tabel yang dibuat dan dapatkan kelas yang dihasilkan:


 /// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }

Tidak ada permintaan akan dibuat untuk tabel ini, itu hanya diperlukan agar metadata terbentuk dengan benar untuk membuat kueri. Contoh terakhir menggunakan kueri basis data teks lengkap:


 /// c# string queryText = FtsSearch.Query( dbContext: db, ftsEnum: FtsEnum.CONTAINS, tableQuery: typeof(Article), tableFts: typeof(FTS_Int), search: "text"); var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText)); var query = db.Article .Where(n => n.Active) .Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new { article, fts.Rank, }) .OrderByDescending(n => n.Rank) .Take(10) .Select(n => n.article); var result = FtsSearch.Execute(() => query.ToList());

Ada juga dukungan untuk permintaan asinkron:


 /// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());

Permintaan SQL yang dihasilkan sebelum AutoCorrect:


 SELECT TOP (10) [Project1].[Id] AS [Id], [Project1].[Date] AS [Date], [Project1].[Text] AS [Text], [Project1].[Active] AS [Active] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Text] AS [Text], [Extent1].[Active] AS [Active], [Extent2].[Rank] AS [Rank] FROM [dbo].[Article] AS [Extent1] INNER JOIN [dbo].[FTS_Int] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key] WHERE ([Extent1].[Active] = 1) AND ([Extent2].[Query] LIKE @p__linq__0 ESCAPE N'~') ) AS [Project1] ORDER BY [Project1].[Rank] DESC

Permintaan SQL yang dihasilkan setelah koreksi otomatis:


 SELECT TOP (10) [Project1].[Id] AS [Id], [Project1].[Date] AS [Date], [Project1].[Text] AS [Text], [Project1].[Active] AS [Active] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Text] AS [Text], [Extent1].[Active] AS [Active], [Extent2].[Rank] AS [Rank] FROM [dbo].[Article] AS [Extent1] INNER JOIN CONTAINSTABLE([dbo].[Article],(*),'text') AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key] WHERE ([Extent1].[Active] = 1) AND (1=1) ) AS [Project1] ORDER BY [Project1].[Rank] DESC

Secara default, pencarian teks lengkap bekerja pada semua kolom tabel:


 CONTAINSTABLE([dbo].[Article],(*),'text')

Jika Anda hanya perlu memilih bidang tertentu, Anda bisa menentukannya dalam parameter bidang fungsi FtsSearch.Query .


Total


Hasilnya adalah dukungan pencarian teks lengkap di LINQ.


Nuansa pendekatan ini.


  1. Parameter pencarian di fungsi FtsSearch.Query tidak menggunakan pemeriksaan atau pembungkus apa pun untuk melindungi dari injeksi SQL. Nilai variabel ini diteruskan seperti halnya ke teks permintaan. Jika Anda punya ide tentang ini, tulis di komentar. Saya menggunakan ekspresi reguler yang biasanya hanya menghapus semua karakter selain huruf dan angka.


  2. Anda juga perlu mempertimbangkan fitur membangun ekspresi untuk kueri teks lengkap. Parameter berfungsi


     /*    */ CONTAINSTABLE([dbo].[News],(*),' ')

    Ini memiliki format yang tidak valid karena MS SQL membutuhkan pemisahan kata dengan literal logis. Agar permintaan berhasil diselesaikan, Anda harus memperbaikinya seperti ini:


     /*   */ CONTAINSTABLE([dbo].[News],(*),' and ')

    atau ubah fungsi pengambilan data


     /*   */ FREETEXTTABLE([dbo].[News],(*),' ')

    Untuk informasi lebih lanjut tentang fitur membuat kueri, lebih baik merujuk ke dokumentasi resmi .


  3. Pencatatan standar dengan solusi ini tidak berfungsi dengan benar. Logger khusus telah ditambahkan untuk ini:


     /// c# db.Database.Log = (val) => Console.WriteLine(val);

    Jika Anda melihat kueri yang dihasilkan ke database, maka itu akan dihasilkan sebelum memproses fungsi penggantian otomatis.



Selama pengujian, saya memeriksa pertanyaan yang lebih kompleks dengan beberapa pilihan dari tabel yang berbeda dan tidak ada masalah.


Sumber GitHub

Source: https://habr.com/ru/post/id455160/

More articles:


All Articles