أرغب في مشاركة عكاز في حل مشكلة عادية إلى حد ما: كيفية تكوين أصدقاء MSSQL بنص كامل للبحث باستخدام Entity Framework. الموضوع متخصص للغاية ، لكن يبدو لي أن هذا الأمر مهم اليوم. للمهتمين ، أطلب القط.
بدأ كل شيء بالألم
أقوم بتطوير مشاريع في C # (ASP.NET) وأحيانًا أكتب خدمات microservices. في معظم الحالات ، أستخدم قاعدة بيانات MSSQL للعمل مع البيانات. يستخدم Entity Framework كحلقة وصل بين قاعدة البيانات ومشروعي. مع EF ، أحصل على فرص وافرة للعمل مع البيانات ، وإنشاء الاستعلامات الصحيحة ، وتنظيم الحمل على الخادم. آلية LINQ السحرية تجسد ببساطة قدراتها. على مر السنين ، لم أعد أتخيل طرقًا أسرع وأكثر ملاءمة للعمل مع قاعدة البيانات. ولكن مثل أي ORM تقريبًا ، لدى EF عدد من العيوب. أولاً ، هذا أداء ، لكن هذا موضوع مقالة منفصلة. وثانياً ، تغطي إمكانيات قاعدة البيانات نفسها.
لدى MSSQL بحث نص كامل مضمن يعمل خارج المربع. لتنفيذ استعلامات النص الكامل ، يمكنك استخدام المسندات المدمجة (CONTAINS و FREETEXT) أو الدالات (CONTAINSTABLE و FREETEXTTABLE). هناك مشكلة واحدة فقط: EF لا يدعم استعلامات النص الكامل ، من الكلمة على الإطلاق!
سأقدم مثالاً من التجربة الحقيقية. افترض أن لدي جدول مقالات - مقالة ، وأنشئ فصلاً لها يصف هذا الجدول:
/// 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; } }
ثم أحتاج إلى عمل مجموعة مختارة من هذه المقالات ، على سبيل المثال ، لإخراج آخر 10 مقالات منشورة:
/// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray();
كل شيء جميل للغاية حتى تظهر مهمة إضافة بحث النص الكامل. نظرًا لعدم وجود دعم لوظائف تحديد النص الكامل في EF (يحتوي .NET .NET 2.1 بالفعل جزئيًا على ذلك ) ، يبقى إما استخدام بعض مكتبة الجهات الخارجية أو كتابة استعلام في SQL خالص.
استعلام SQL من المثال أعلاه غير معقد للغاية:
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
في المشاريع الحقيقية ، الأمور ليست بهذه البساطة. استعلامات قاعدة البيانات أكثر تعقيدًا ومن الصعب الاحتفاظ بها يدويًا. نتيجة لذلك ، أول مرة كتبت فيها استعلامًا باستخدام LINQ ، ثم حصلت على النص الذي تم إنشاؤه لاستعلام SQL في قاعدة البيانات ، وأدخلت بالفعل شروط اختيار بيانات النص الكامل فيه. ثم أرسلتها إلى db.Database.SqlQuery وتلقيت البيانات التي db.Database.SqlQuery . هذا كل شيء جيد ، بالطبع ، طالما أن الطلب لا يحتاج إلى تعليق عشرات المرشحات المختلفة مع شروط انضمام معقدة لنا.
لذلك - لدي ألم محدد. يجب علينا حلها!
بحثا عن حل
مرة أخرى ، أثناء جلوسي في بحثي المفضل على أمل إيجاد حل على الأقل ، واجهت هذا المستودع . مع هذا الحل ، يمكن تنفيذ دعم LINQ الأصلي (CONTAINS و FREETEXT). بفضل دعم EF 6 لواجهة IDbCommandInterceptor الخاصة ، والتي تسمح لك باعتراض استعلام SQL النهائي ، تم تنفيذ هذا الحل قبل إرساله إلى قاعدة البيانات. يتم استبدال سلسلة علامة خاصة تم إنشاؤها في حقل Contains ، وبعد ذلك بعد إنشاء الطلب ، يتم استبدال هذا المكان بمثال أساسي:
/// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text));
ومع ذلك ، إذا كان اختيار البيانات بحاجة إلى فرز حسب ترتيب التطابقات ، فلن يكون هذا الحل مناسبًا بعد ذلك وسيكون عليك كتابة استعلام SQL يدويًا. في جوهرها ، يستبدل هذا الحل LIKE المعتاد باختيار أصلي.
لذا ، في هذه المرحلة ، كان لدي سؤال: هل من الممكن تنفيذ بحث نص كامل حقيقي باستخدام وظائف MS SQL المضمنة (CONTAINSTABLE و FREETEXTTABLE) بحيث يمكن إنشاء كل هذا عبر LINQ وحتى مع دعم فرز الاستعلام حسب ترتيب التطابقات؟ كما اتضح ، يمكنك!
تطبيق
بادئ ذي بدء ، كان من الضروري تطوير المنطق لكتابة الاستعلام نفسه باستخدام LINQ. نظرًا لأنه في استعلامات SQL الحقيقية مع تحديدات النص الكامل ، يتم استخدام JOIN غالبًا للانضمام إلى جدول افتراضي به صفوف ، فقد قررت الانتقال بنفس الطريقة في استعلام LINQ.
فيما يلي مثال لاستعلام LINQ:
/// 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);
لا يمكن بعد تجميع مثل هذا الكود ، لكنه حل بصريًا بالفعل مشكلة فرز البيانات الناتجة حسب الترتيب. بقي لوضعها موضع التنفيذ.
فئة إضافية FTS_Int المستخدمة في هذا الطلب:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
لم يتم اختيار الاسم عن طريق الصدفة ، نظرًا لأن عمود المفتاح في هذه الفئة يجب أن يتزامن مع عمود المفتاح في جدول البحث (في مثالي ، مع [Article].[Id] type int ). إذا احتجت إلى إجراء استعلامات على جداول أخرى مع أنواع أخرى من أعمدة المفاتيح ، فقد افترضت ببساطة نسخ فئة مماثلة وإنشاء مفتاح النوع المطلوب.
كان من المفترض أن يتم تمرير شرط تكوين استعلام النص الكامل في متغير queryText . لتكوين نص هذا المتغير ، تم تنفيذ وظيفة منفصلة:
/// c# string queryText = FtsSearch.Query( dbContext: db, // , ftsEnum: FtsEnum.CONTAINS, // : CONTAINS FREETEXT tableQuery: typeof(News), // tableFts: typeof(FTS_Int), // search: "text"); //
تلبية طلب جاهز والحصول على البيانات:
/// c# var result = FtsSearch.Execute(() => query.ToList());
يتم استخدام الدالة المجمّع FtsSearch.Execute النهائية للاتصال مؤقتاً واجهة IDbCommandInterceptor . في المثال المقدم من الرابط أعلاه ، فضل المؤلف استخدام خوارزمية استبدال الاستعلام باستمرار لجميع الطلبات. نتيجة لذلك ، بعد توصيل آلية استبدال الاستعلام ، يبحث كل طلب عن المجموعة الضرورية للاستبدال. بدا هذا الخيار مضيعة لي ، لذلك ، يتم تنفيذ طلب البيانات نفسه في الوظيفة المرسلة ، والتي قبل أن تتصل المكالمة بالاستعلام التلقائي للاستبدال ، وبعد المكالمة ، تقوم بفصله.
تطبيق
أنا أستخدم الإنشاء التلقائي لفئات نماذج البيانات من قاعدة بيانات باستخدام ملف edmx. نظرًا FTS_Int لا يمكنك ببساطة استخدام فئة FTS_Int تم إنشاؤها في EF نظرًا لعدم وجود البيانات DbContext اللازمة في DbContext ، فقد أنشأت جدولًا حقيقيًا وفقًا DbContext (ربما شخص ما يعرف طريقة أفضل ، سأكون سعيدًا بمساعدتك في التعليقات):
لقطة شاشة للجدول تم إنشاؤها في ملف 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) );
بعد ذلك ، عند تحديث ملف edmx من قاعدة البيانات ، أضف الجدول الذي تم إنشاؤه واحصل على الفئة التي تم إنشاؤها:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
لن يتم تقديم أي استعلامات في هذا الجدول ؛ فهي مطلوبة فقط حتى يتم تكوين بيانات التعريف بشكل صحيح لإنشاء الاستعلام. المثال الأخير لاستخدام استعلام قاعدة بيانات النص الكامل:
/// 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());
يوجد أيضًا دعم للطلبات غير المتزامنة:
/// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());
استعلام SQL الذي تم إنشاؤه قبل التصحيح التلقائي:
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
استعلام SQL الذي تم إنشاؤه بعد التصحيح التلقائي:
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
بشكل افتراضي ، يعمل البحث عن النص الكامل على جميع أعمدة الجدول:
CONTAINSTABLE([dbo].[Article],(*),'text')
إذا كنت بحاجة إلى تحديد حقول معينة فقط ، فيمكنك تحديدها في معلمة الحقول للدالة FtsSearch.Query .
في المجموع
والنتيجة هي دعم البحث عن النص الكامل في LINQ.
الفروق الدقيقة في هذا النهج.
FtsSearch.Query تستخدم المعلمة البحث في دالة FtsSearch.Query أو أغلفة للحماية من حقن SQL. يتم تمرير قيمة هذا المتغير كما هو في نص الطلب. إذا كان لديك أي أفكار حول هذا ، فاكتب في التعليقات. لقد استخدمت التعبير العادي المعتاد الذي يزيل كل الأحرف بخلاف الحروف والأرقام.
تحتاج أيضًا إلى مراعاة ميزات إنشاء التعبيرات لاستعلامات النص الكامل. المعلمة للعمل
CONTAINSTABLE([dbo].[News],(*),' ')
يحتوي على تنسيق غير صالح لأن MS SQL يتطلب فصل الكلمات عن طريق الحرفي المنطقي. لاستكمال الطلب بنجاح ، تحتاج إلى إصلاح مثل هذا:
CONTAINSTABLE([dbo].[News],(*),' and ')
أو تغيير وظيفة استرجاع البيانات
FREETEXTTABLE([dbo].[News],(*),' ')
لمزيد من المعلومات حول ميزات إنشاء الاستعلامات ، من الأفضل الرجوع إلى الوثائق الرسمية .
التسجيل القياسي مع هذا الحل لا يعمل بشكل صحيح. تمت إضافة مسجل خاص لهذا:
/// c# db.Database.Log = (val) => Console.WriteLine(val);
إذا نظرت إلى الاستعلام الذي تم إنشاؤه في قاعدة البيانات ، فسيتم إنشاؤه قبل معالجة وظائف الاستبدال التلقائي.
أثناء الاختبار ، راجعت استعلامات أكثر تعقيدًا مع اختيارات متعددة من جداول مختلفة ولم تكن هناك أية مشكلات.
مصادر جيثب