मैं अपनी बैसाखी को एक सामान्य समस्या के समाधान में साझा करना चाहता हूं: एंटिटी फ्रेमवर्क के साथ दोस्तों को पूर्ण-पाठ MSSQL खोज कैसे करें। विषय बहुत विशिष्ट है, लेकिन यह मुझे आज भी प्रासंगिक लगता है। रुचि रखने वालों के लिए, मैं बिल्ली के लिए पूछना।

यह सब दर्द के साथ शुरू हुआ

मैं C # (ASP.NET) में परियोजनाएं विकसित करता हूं और कभी-कभी माइक्रोसर्विसेज लिखता हूं। ज्यादातर मामलों में, मैं डेटा के साथ काम करने के लिए MSSQL डेटाबेस का उपयोग करता हूं। डेटाबेस और मेरी परियोजना के बीच एक इकाई के रूप में एंटिटी फ्रेमवर्क का उपयोग किया जाता है। 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 कोर 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 को भेजा और मुझे जो डेटा चाहिए था, वह प्राप्त किया। यह सब अच्छा है, ज़ाहिर है, जब तक आपको जटिल जॉइन-यू और शर्तों के साथ एक दर्जन विभिन्न फिल्टर लटकाए जाने की आवश्यकता नहीं है।

इसलिए - मुझे एक विशिष्ट दर्द है। हमें इसे हल करना चाहिए!

समाधान की तलाश में

एक बार फिर, कम से कम कुछ समाधान खोजने की उम्मीद में अपनी पसंदीदा खोज में बैठे, मैं इस भंडार में आया। इस समाधान के साथ, LINQ predicate support (CONTAINS और FREETEXT) को लागू किया जा सकता है। विशेष IDbCommandInterceptor इंटरफ़ेस के EF 6 के समर्थन के लिए धन्यवाद, जो आपको तैयार SQL क्वेरी को इंटरसेप्ट करने की अनुमति देता है, इस समाधान को डेटाबेस में भेजने से पहले लागू किया गया था। एक विशेष उत्पन्न मार्कर स्ट्रिंग को Contains क्षेत्र में प्रतिस्थापित किया जाता है, और फिर अनुरोध उत्पन्न करने के बाद, इस स्थान को एक विस्तृत विवरण के साथ बदल दिया जाता है:

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

हालांकि, यदि डेटा चयन को मैचों की रैंक द्वारा क्रमबद्ध करने की आवश्यकता है, तो यह समाधान अब उपयुक्त नहीं होगा और आपको SQL क्वेरी को मैन्युअल रूप से लिखना होगा। संक्षेप में, यह समाधान सामान्य LIKE को विधेय चयन के साथ बदल देता है।

तो, इस स्तर पर, मेरे पास एक सवाल था: क्या अंतर्निहित MS SQL फ़ंक्शंस (CONTAINSTABLE और FREETEXTTABLE) का उपयोग करके वास्तविक पूर्ण-पाठ खोज को लागू करना संभव है, ताकि यह सब LINQ के माध्यम से उत्पन्न हो सके और यहां तक ​​कि मैचों की रैंक द्वारा क्वेरी को सॉर्ट करने के समर्थन से भी? जैसा कि यह निकला, आप कर सकते हैं!

कार्यान्वयन

इसके साथ शुरू करने के लिए, LINQ का उपयोग करके क्वेरी लिखने के लिए तर्क विकसित करना आवश्यक था। चूंकि पूर्ण-पाठ चयन के साथ वास्तविक एसक्यूएल प्रश्नों में, 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] टाइप 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 में आवश्यक मेटाडेटा की कमी के कारण EF में बनाए गए FTS_Int वर्ग का उपयोग नहीं कर सकते हैं, मैंने इसके मॉडल के अनुसार एक वास्तविक तालिका बनाई है (शायद कोई बेहतर तरीका जानता हो, मुझे टिप्पणियों में आपकी मदद से खुशी होगी):

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 में फुल-टेक्स्ट सर्च सपोर्ट है।

इस दृष्टिकोण की बारीकियों।

1. FtsSearch.Query फ़ंक्शन में खोज पैरामीटर SQL इंजेक्शन से सुरक्षा के लिए किसी भी चेक या रैपर का उपयोग नहीं करता है। इस चर का मान अनुरोध पाठ के रूप में पारित किया गया है। यदि आपके पास इस बारे में कोई विचार है, तो टिप्पणियों में लिखें। मैंने सामान्य नियमित अभिव्यक्ति का उपयोग किया जो अक्षरों और संख्याओं के अलावा अन्य सभी वर्णों को हटा देता है।

   
   

2. आपको पूर्ण-पाठ क्वेरी के लिए भवन अभिव्यक्तियों की सुविधाओं पर भी विचार करने की आवश्यकता है। कार्य करने के लिए पैरामीटर

   
   

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

   
   

   इसका एक अमान्य प्रारूप है क्योंकि MS SQL को तार्किक शाब्दिक शब्दों से अलग करने की आवश्यकता होती है। अनुरोध को सफलतापूर्वक पूरा करने के लिए, आपको इसे इस तरह से ठीक करने की आवश्यकता है:

   
   

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

   
   

   या डेटा पुनर्प्राप्ति फ़ंक्शन को बदलें

   
   

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

   
   

   प्रश्न बनाने की सुविधाओं के बारे में अधिक जानकारी के लिए, आधिकारिक दस्तावेज को संदर्भित करना बेहतर है।

   
   

3. इस समाधान के साथ मानक लॉगिंग सही ढंग से काम नहीं करता है। इसके लिए एक विशेष लकड़हारा जोड़ा गया है:

   
   

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

   
   

   यदि आप डेटाबेस में उत्पन्न क्वेरी को देखते हैं, तो यह ऑटो-रिप्लेस फ़ंक्शंस को संसाधित करने से पहले उत्पन्न होगा।

   
   

परीक्षण के दौरान, मैंने विभिन्न तालिकाओं से कई चयनों के साथ अधिक जटिल प्रश्नों पर जांच की और कोई समस्या नहीं थी।

गिटहब स्रोत