Ich möchte meine Krücke bei der Lösung eines eher banalen Problems teilen: Wie man mit Entity Framework eine Volltext-MSSQL-Suche für Freunde findet. Das Thema ist sehr spezialisiert, aber es scheint mir heute relevant zu sein. Für Interessierte bitte ich um Katze.
Alles begann mit Schmerzen
Ich entwickle Projekte in C # (ASP.NET) und schreibe manchmal Microservices. In den meisten Fällen verwende ich die MSSQL-Datenbank, um mit Daten zu arbeiten. Entity Framework wird als Verbindung zwischen der Datenbank und meinem Projekt verwendet. Mit EF habe ich reichlich Gelegenheit, mit Daten zu arbeiten, die richtigen Abfragen zu generieren und die Belastung des Servers zu regulieren. Der magische LINQ-Mechanismus besticht durch seine Fähigkeiten. Im Laufe der Jahre stelle ich mir keine schnelleren und bequemeren Möglichkeiten mehr vor, mit der Datenbank zu arbeiten. Aber wie fast jedes ORM hat EF eine Reihe von Nachteilen. Erstens ist dies Leistung, aber dies ist das Thema eines separaten Artikels. Und zweitens werden die Funktionen der Datenbank selbst behandelt.
MSSQL verfügt über eine integrierte Volltextsuche, die sofort funktioniert. Um Volltextabfragen durchzuführen, können Sie die integrierten Prädikate (CONTAINS und FREETEXT) oder Funktionen (CONTAINSTABLE und FREETEXTTABLE) verwenden. Es gibt nur ein Problem: EF unterstützt überhaupt keine Volltextabfragen!
Ich werde ein Beispiel aus der Praxis geben. Angenommen, ich habe eine Artikeltabelle - Artikel, und ich erstelle eine Klasse dafür, die diese Tabelle beschreibt:
/// 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; } }
Dann muss ich eine Auswahl dieser Artikel treffen, beispielsweise die letzten 10 veröffentlichten Artikel ausgeben:
/// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray();
Alles ist sehr schön, bis die Aufgabe des Hinzufügens der Volltextsuche erscheint. Da Volltextauswahlfunktionen in EF nicht unterstützt werden (.NET Core 2.1 verfügt bereits teilweise darüber ), müssen Sie entweder eine Bibliothek eines Drittanbieters verwenden oder eine Abfrage in reinem SQL schreiben.
Die SQL-Abfrage aus dem obigen Beispiel ist nicht so kompliziert:
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
In realen Projekten sind die Dinge nicht so einfach. Abfragen an die Datenbank sind viel komplizierter und es ist schwierig, sie manuell zu verwalten. Als ich zum ersten Mal eine Abfrage mit LINQ schrieb, bekam ich den generierten Text der SQL-Abfrage in die Datenbank und führte bereits die Bedingungen für die Volltextdatenauswahl ein. Dann schickte ich es an db.Database.SqlQuery und erhielt die Daten, die ich brauchte. Das ist natürlich alles gut, solange die Anfrage nicht ein Dutzend verschiedener Filter mit komplexen Join-Us und Bedingungen aufhängen muss.
Also - ich habe einen bestimmten Schmerz. Wir müssen es lösen!
Auf der Suche nach einer Lösung
Wieder einmal stieß ich bei meiner Lieblingssuche in der Hoffnung, zumindest eine Lösung zu finden, auf dieses Repository . Mit dieser Lösung kann die Unterstützung von LINQ-Prädikaten (CONTAINS und FREETEXT) implementiert werden. Dank der Unterstützung von EF 6 der speziellen IDbCommandInterceptor Schnittstelle, mit der Sie die fertige SQL-Abfrage abfangen können, wurde diese Lösung implementiert, bevor Sie sie an die Datenbank senden. Eine speziell generierte Markierungszeichenfolge wird in das Feld " Contains eingefügt. Nach dem Generieren der Anforderung wird diese Stelle durch ein Prädikat ersetzt. Beispiel:
/// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text));
Wenn die Datenauswahl jedoch nach dem Rang der Übereinstimmungen sortiert werden muss, funktioniert diese Lösung nicht mehr und Sie müssen die SQL-Abfrage manuell schreiben. Im Wesentlichen ersetzt diese Lösung das übliche LIKE durch eine Prädikatauswahl.
Zu diesem Zeitpunkt hatte ich also eine Frage: Ist es möglich, eine echte Volltextsuche mit den integrierten MS SQL-Funktionen (CONTAINSTABLE und FREETEXTTABLE) zu implementieren, damit all dies über LINQ und sogar mit Unterstützung für das Sortieren der Abfrage nach dem Rang der Übereinstimmungen generiert werden kann? Wie sich herausstellte, können Sie!
Implementierung
Zunächst musste die Logik zum Schreiben der Abfrage selbst mit LINQ entwickelt werden. Da in realen SQL-Abfragen mit Volltextauswahl JOIN am häufigsten zum Verknüpfen einer virtuellen Tabelle mit Rängen verwendet wird, habe ich mich für den gleichen Weg in der LINQ-Abfrage entschieden.
Hier ist ein Beispiel für eine solche LINQ-Abfrage:
/// 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);
Ein solcher Code konnte noch nicht kompiliert werden, löste jedoch bereits visuell das Problem der Sortierung der resultierenden Daten nach Rang. Es blieb, um es in die Praxis umzusetzen.
Zusätzliche Klasse FTS_Int die in dieser Anforderung verwendet wird:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
Der Name wurde nicht zufällig ausgewählt, da die Schlüsselspalte in dieser Klasse mit der Schlüsselspalte in der Suchtabelle übereinstimmen sollte (in meinem Beispiel mit [Article].[Id] Typ int ). Für den Fall, dass Sie Abfragen für andere Tabellen mit anderen Arten von Schlüsselspalten durchführen müssen, habe ich angenommen, dass Sie einfach eine ähnliche Klasse kopieren und ihren Schlüssel des erforderlichen Typs erstellen.
Die Bedingung für die Bildung einer Volltextabfrage sollte in der Variablen queryText werden. Um den Text dieser Variablen zu bilden, wurde eine separate Funktion implementiert:
/// c# string queryText = FtsSearch.Query( dbContext: db, // , ftsEnum: FtsEnum.CONTAINS, // : CONTAINS FREETEXT tableQuery: typeof(News), // tableFts: typeof(FTS_Int), // search: "text"); //
Erfüllung einer fertigen Anfrage und Datenerfassung:
/// c# var result = FtsSearch.Execute(() => query.ToList());
Die letzte FtsSearch.Execute Wrapper-Funktion wird verwendet, um die IDbCommandInterceptor Schnittstelle vorübergehend zu verbinden. In dem Beispiel, das durch den obigen Link bereitgestellt wird, hat der Autor es vorgezogen, den Abfragesubstitutionsalgorithmus ständig für alle Anforderungen zu verwenden. Infolgedessen sucht jede Anforderung nach dem Verbinden des Mechanismus zum Ersetzen von Abfragen nach der erforderlichen Kombination zum Ersetzen. Diese Option erschien mir verschwenderisch, daher wird die Ausführung der Datenanforderung selbst in der übertragenen Funktion ausgeführt, die vor dem Aufruf die automatische Ersetzung der Abfrage verbindet und nach dem Anruf die Verbindung trennt.
Anwendung
Ich verwende die automatische Generierung von Datenmodellklassen aus einer Datenbank mithilfe der EDMX-Datei. Da Sie die erstellte FTS_Int Klasse in EF aufgrund des Fehlens der erforderlichen Metadaten in DbContext einfach nicht verwenden DbContext , habe ich eine echte Tabelle gemäß ihrem Modell erstellt (vielleicht kennt jemand einen besseren Weg, ich freue mich über Ihre Hilfe in den Kommentaren):
Screenshot der in der edmx-Datei erstellten Tabelle
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) );
Fügen Sie anschließend beim Aktualisieren der edmx-Datei aus der Datenbank die erstellte Tabelle hinzu und rufen Sie die generierte Klasse ab:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
Für diese Tabelle werden keine Abfragen durchgeführt. Sie wird nur benötigt, damit die Metadaten zum Erstellen der Abfrage korrekt gebildet werden. Das letzte Beispiel für die Verwendung einer Volltext-Datenbankabfrage:
/// 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());
Es gibt auch Unterstützung für asynchrone Anforderungen:
/// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());
Vor der Autokorrektur generierte SQL-Abfrage:
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-Abfrage nach Autokorrektur generiert:
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
Standardmäßig funktioniert die Volltextsuche in allen Spalten der Tabelle:
CONTAINSTABLE([dbo].[Article],(*),'text')
Wenn Sie nur bestimmte Felder auswählen müssen, können Sie diese im Parameter FtsSearch.Query Funktion FtsSearch.Query .
Insgesamt
Das Ergebnis ist die Unterstützung der Volltextsuche in LINQ.
Die Nuancen dieses Ansatzes.
Der Suchparameter in der Funktion FtsSearch.Query verwendet keine Überprüfungen oder Wrapper zum Schutz vor SQL-Injection. Der Wert dieser Variablen wird unverändert an den Anforderungstext übergeben. Wenn Sie Ideen dazu haben, schreiben Sie in die Kommentare. Ich habe den üblichen regulären Ausdruck verwendet, bei dem einfach alle Zeichen außer Buchstaben und Zahlen entfernt werden.
Sie müssen auch die Funktionen zum Erstellen von Ausdrücken für Volltextabfragen berücksichtigen. Parameter zur Funktion
CONTAINSTABLE([dbo].[News],(*),' ')
Es hat ein ungültiges Format, da MS SQL die Trennung von Wörtern durch logische Literale erfordert. Damit die Anforderung erfolgreich abgeschlossen werden kann, müssen Sie sie wie folgt beheben:
CONTAINSTABLE([dbo].[News],(*),' and ')
oder ändern Sie die Datenabruffunktion
FREETEXTTABLE([dbo].[News],(*),' ')
Weitere Informationen zu den Funktionen zum Erstellen von Abfragen finden Sie in der offiziellen Dokumentation .
Die Standardprotokollierung mit dieser Lösung funktioniert nicht ordnungsgemäß. Hierzu wurde ein spezieller Logger hinzugefügt:
/// c# db.Database.Log = (val) => Console.WriteLine(val);
Wenn Sie sich die generierte Abfrage für die Datenbank ansehen, wird sie generiert, bevor die Funktionen zum automatischen Ersetzen verarbeitet werden.
Während des Tests habe ich komplexere Abfragen mit Mehrfachauswahl aus verschiedenen Tabellen überprüft und es gab keine Probleme.
GitHub- Quellen