Je veux partager ma béquille pour résoudre un problème plutôt banal: comment se faire des amis recherche MSSQL en texte intégral avec Entity Framework. Le sujet est très spécialisé, mais il me semble pertinent aujourd'hui. Pour ceux intéressés, je demande chat.

Tout a commencé avec de la douleur

Je développe des projets en C # (ASP.NET) et j'écris parfois des microservices. Dans la plupart des cas, j'utilise la base de données MSSQL pour travailler avec des données. Entity Framework est utilisé comme lien entre la base de données et mon projet. Avec EF, j'ai de nombreuses opportunités de travailler avec des données, de générer les bonnes requêtes, de réguler la charge sur le serveur. Le mécanisme magique LINQ séduit simplement par ses capacités. Au fil des ans, je n'imagine plus de façons plus rapides et plus pratiques de travailler avec la base de données. Mais comme presque n'importe quel ORM, EF présente un certain nombre d'inconvénients. Tout d'abord, ce sont les performances, mais c'est le sujet d'un article séparé. Et deuxièmement, il couvre les capacités de la base de données elle-même.

MSSQL a une recherche de texte intégral intégrée qui fonctionne dès la sortie de la boîte. Pour effectuer des requêtes de texte intégral, vous pouvez utiliser les prédicats intégrés (CONTAINS et FREETEXT) ou les fonctions (CONTAINSTABLE et FREETEXTTABLE). Il n'y a qu'un seul problème: EF ne prend pas du tout en charge les requêtes de texte intégral!

Je vais donner un exemple d'expérience réelle. Supposons que j'ai un tableau d'articles - Article, et que je crée pour lui une classe qui décrit ce tableau:

/// 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; } } 

Ensuite, je dois faire une sélection de ces articles, disons, produire les 10 derniers articles publiés:

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

Tout est très beau jusqu'à ce que la tâche d'ajout de recherche en texte intégral apparaisse. Comme il n'y a pas de prise en charge des fonctions de sélection de texte intégral dans EF (.NET core 2.1 l'a déjà partiellement), il reste soit à utiliser une bibliothèque tierce, soit à écrire une requête en SQL pur.

La requête SQL de l'exemple ci-dessus n'est pas si compliquée:

 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 

Dans les vrais projets, les choses ne sont pas si simples. Les requêtes vers la base de données sont beaucoup plus compliquées et il est difficile de les gérer manuellement. Par conséquent, la première fois que j'ai écrit une requête à l'aide de LINQ, j'ai ensuite obtenu le texte généré de la requête SQL dans la base de données et y ai déjà introduit les conditions de sélection des données en texte intégral. Ensuite, je l'ai envoyé à db.Database.SqlQuery et reçu les données dont j'avais besoin. Tout cela est bien sûr, tant que la demande n'a pas besoin de bloquer une douzaine de filtres différents avec des conditions et des conditions de participation complexes.

Donc - j'ai une douleur spécifique. Nous devons le résoudre!

A la recherche d'une solution

Une fois de plus, assis dans ma recherche préférée dans l'espoir de trouver au moins une solution, je suis tombé sur ce référentiel . Avec cette solution, la prise en charge des prédicats LINQ (CONTAINS et FREETEXT) peut être implémentée. Grâce au support de EF 6 de l'interface spéciale IDbCommandInterceptor , qui vous permet d'intercepter la requête SQL terminée, cette solution a été implémentée avant de l'envoyer à la base de données. Une chaîne de marqueurs générée spéciale est substituée dans le champ Contains , puis après avoir généré la demande, cet endroit est remplacé par un prédicat Exemple:

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

Cependant, si la sélection des données doit être triée par le rang des correspondances, cette solution ne sera plus appropriée et vous devrez écrire la requête SQL manuellement. En substance, cette solution remplace le LIKE habituel par une sélection de prédicats.

Donc, à ce stade, j'avais une question: est-il possible d'implémenter une véritable recherche de texte intégral en utilisant les fonctions MS SQL intégrées (CONTAINSTABLE et FREETEXTTABLE) afin que tout cela puisse être généré via LINQ et même avec un support pour trier la requête par le rang des correspondances? Il s'est avéré que vous le pouvez!

Implémentation

Pour commencer, il était nécessaire de développer la logique d'écriture de la requête elle-même à l'aide de LINQ. Étant donné que dans les requêtes SQL réelles avec des sélections de texte intégral, JOIN est le plus souvent utilisé pour joindre une table virtuelle avec des rangs, j'ai décidé de suivre la même voie dans la requête LINQ.

Voici un exemple d'une telle requête 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); 

Un tel code n'a pas encore pu être compilé, mais il a déjà résolu visuellement le problème du tri des données résultantes par rang. Restait à le mettre en pratique.

Classe supplémentaire FTS_Int utilisée dans cette demande:

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

Le nom n'a pas été choisi par hasard, car la colonne clé de cette classe doit coïncider en cochant avec la colonne clé de la table de recherche (dans mon exemple, avec [Article].[Id] type int ). Au cas où vous auriez besoin de faire des requêtes sur d'autres tables avec d'autres types de colonnes clés, j'ai supposé simplement copier une classe similaire et créer sa clé du type nécessaire.

La condition pour la formation d'une requête de texte intégral était censée être passée dans la variable queryText . Pour former le texte de cette variable, une fonction distincte a été implémentée:

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

Réalisation d'une demande prête et acquisition de données:

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

La fonction finale de wrapper FtsSearch.Execute est utilisée pour connecter temporairement l'interface IDbCommandInterceptor . Dans l'exemple fourni par le lien ci-dessus, l'auteur a préféré utiliser l'algorithme de substitution de requête en permanence pour toutes les demandes. Par conséquent, après avoir connecté le mécanisme de remplacement de requête, chaque demande recherche la combinaison nécessaire pour le remplacement. Cette option m'a semblé inutile, par conséquent, l'exécution de la demande de données elle-même est effectuée dans la fonction transmise qui, avant l'appel, connecte l'auto-remplacement de la requête et, après l'appel, la déconnecte.

Candidature

J'utilise la génération automatique de classes de modèles de données à partir d'une base de données à l'aide d'un fichier edmx. Comme vous ne pouvez tout simplement pas utiliser la classe FTS_Int créée dans EF en raison du manque de métadonnées nécessaires dans DbContext , j'ai créé une vraie table selon son modèle (peut-être que quelqu'un connaît une meilleure façon, je serai heureux de votre aide dans les commentaires):

Capture d'écran de la table créée dans le fichier 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) ); 

Après cela, lors de la mise à jour du fichier edmx à partir de la base de données, ajoutez la table créée et obtenez sa classe générée:

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

Aucune requête ne sera effectuée sur cette table; elle est uniquement nécessaire pour que les métadonnées soient correctement formées pour créer la requête. Le dernier exemple d'utilisation d'une requête de base de données en texte intégral:

 /// 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()); 

Il existe également un support pour les requêtes asynchrones:

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

Requête SQL générée avant la correction automatique:

 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 

Requête SQL générée après correction automatique:

 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 

Par défaut, la recherche en texte intégral fonctionne sur toutes les colonnes du tableau:

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

Si vous devez sélectionner uniquement certains champs, vous pouvez les spécifier dans le paramètre fields de la fonction FtsSearch.Query .

Total

Le résultat est la prise en charge de la recherche en texte intégral dans LINQ.

Les nuances de cette approche.

1. Le paramètre de recherche dans la fonction FtsSearch.Query n'utilise aucun contrôle ou wrapper pour se protéger contre l'injection SQL. La valeur de cette variable est transmise telle quelle au texte de la requête. Si vous avez des idées à ce sujet, écrivez dans les commentaires. J'ai utilisé l'expression régulière habituelle qui supprime simplement tous les caractères autres que les lettres et les chiffres.

   
   

2. Vous devez également prendre en compte les fonctionnalités de création d'expressions pour les requêtes de texte intégral. Paramètre pour fonctionner

   
   

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

   
   

   Il a un format non valide car MS SQL nécessite la séparation des mots par des littéraux logiques. Pour que la demande aboutisse, vous devez la corriger comme ceci:

   
   

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

   
   

   ou modifiez la fonction d'échantillonnage des données

   
   

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

   
   

   Pour plus d'informations sur les fonctionnalités de création de requêtes, il est préférable de se référer à la documentation officielle .

   
   

3. La journalisation standard avec cette solution ne fonctionne pas correctement. Un enregistreur spécial a été ajouté pour cela:

   
   

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

   
   

   Si vous regardez la requête générée dans la base de données, elle sera générée avant de traiter les fonctions de remplacement automatique.

   
   

Pendant les tests, j'ai vérifié des requêtes plus complexes avec plusieurs sélections dans différentes tables et il n'y a eu aucun problème.

Sources GitHub