Quiero compartir mi muleta para resolver un problema bastante banal: cómo hacer amigos en la búsqueda de texto completo de MSSQL con Entity Framework. El tema es muy especializado, pero me parece que es relevante hoy. Para los interesados, pido gato.
Todo comenzó con dolor.
Desarrollo proyectos en C # (ASP.NET) y a veces escribo microservicios. En la mayoría de los casos, uso la base de datos MSSQL para trabajar con datos. Entity Framework se utiliza como un enlace entre la base de datos y mi proyecto. Con EF, obtengo amplias oportunidades para trabajar con datos, generar las consultas correctas y regular la carga en el servidor. El mecanismo mágico de LINQ simplemente cautiva con sus capacidades. Con los años, ya no imagino formas más rápidas y convenientes de trabajar con la base de datos. Pero como casi cualquier ORM, EF tiene una serie de desventajas. En primer lugar, este es el rendimiento, pero este es el tema de un artículo separado. Y en segundo lugar, cubre las capacidades de la base de datos en sí.
MSSQL tiene una búsqueda de texto completo incorporada que funciona de forma inmediata. Para realizar consultas de texto completo, puede usar los predicados integrados (CONTAINS y FREETEXT) o funciones (CONTAINSTABLE y FREETEXTTABLE). Solo hay un problema: ¡EF no admite consultas de texto completo, desde el principio!
Daré un ejemplo de la experiencia real. Supongamos que tengo una tabla de artículos - Artículo, y creo una clase que describe esta tabla:
/// 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; } }
Luego necesito hacer una selección de estos artículos, digamos, mostrar los últimos 10 artículos publicados:
/// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray();
Todo es muy hermoso hasta que aparece la tarea de agregar búsqueda de texto completo. Como no hay soporte para las funciones de selección de texto completo en EF (.NET core 2.1 ya lo tiene parcialmente), queda usar una biblioteca de terceros o escribir una consulta en SQL puro.
La consulta SQL del ejemplo anterior no es tan complicada:
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
En proyectos reales, las cosas no son tan simples. Las consultas a la base de datos son mucho más complicadas y es difícil mantenerlas manualmente. Como resultado, la primera vez que escribí una consulta usando LINQ, obtuve el texto generado de la consulta SQL en la base de datos y ya introduje las condiciones de selección de datos de texto completo. Luego lo envié a db.Database.SqlQuery y recibí los datos que necesitaba. Todo esto es bueno, por supuesto, siempre y cuando la solicitud no necesite colgar una docena de filtros diferentes con condiciones y condiciones complejas.
Entonces, tengo un dolor específico. ¡Debemos resolverlo!
En busca de una solución
Una vez más, sentado en mi búsqueda favorita con la esperanza de encontrar al menos alguna solución, me encontré con este repositorio . Con esta solución, se puede implementar el soporte de predicado LINQ (CONTAINS y FREETEXT). Gracias al soporte de EF 6 de la interfaz especial IDbCommandInterceptor , que le permite interceptar la consulta SQL finalizada, esta solución se implementó antes de enviarla a la base de datos. Una cadena de marcador especial generada se sustituye en el campo Contains , y luego de generar la solicitud, este lugar se reemplaza con un predicado Ejemplo:
/// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text));
Sin embargo, si la selección de datos necesita ser ordenada por el rango de coincidencias, entonces esta solución ya no será adecuada y tendrá que escribir la consulta SQL manualmente. En esencia, esta solución reemplaza el LIKE habitual con una selección de predicado.
Entonces, en esta etapa, tenía una pregunta: ¿es posible implementar una búsqueda de texto completo real usando las funciones integradas de MS SQL (CONTAINSTABLE y FREETEXTTABLE) para que todo esto se pueda generar a través de LINQ e incluso con soporte para ordenar la consulta por el rango de coincidencias? ¡Resultó que puedes!
Implementación
Para empezar, era necesario desarrollar la lógica para escribir la consulta en sí usando LINQ. Dado que en las consultas SQL reales con selecciones de texto completo, JOIN se usa con mayor frecuencia para unir una tabla virtual con rangos, decidí seguir el mismo camino en la consulta LINQ.
Aquí hay un ejemplo de dicha consulta 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);
Tal código aún no se pudo compilar, pero ya resolvió visualmente el problema de ordenar los datos resultantes por rango. Quedaba por ponerlo en práctica.
Clase adicional FTS_Int utilizada en esta solicitud:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
El nombre no se eligió por casualidad, ya que la columna clave en esta clase debe coincidir en tic con la columna clave en la tabla de búsqueda (en mi ejemplo, con [Article].[Id] tipo int ). En caso de que necesite realizar consultas en otras tablas con otros tipos de columnas clave, supuse que simplemente copiaría una clase similar y crearía su clave del tipo que se necesita.
Se suponía que la condición para la formación de una consulta de texto completo se pasaba en la variable queryText . Para formar el texto de esta variable, se implementó una función separada:
/// c# string queryText = FtsSearch.Query( dbContext: db, // , ftsEnum: FtsEnum.CONTAINS, // : CONTAINS FREETEXT tableQuery: typeof(News), // tableFts: typeof(FTS_Int), // search: "text"); //
Cumplimiento de la solicitud lista y recuperación de datos:
/// c# var result = FtsSearch.Execute(() => query.ToList());
La última función de contenedor FtsSearch.Execute se usa para conectar temporalmente la interfaz IDbCommandInterceptor . En el ejemplo proporcionado por el enlace anterior, el autor prefirió usar el algoritmo de sustitución de consultas constantemente para todas las solicitudes. Como resultado, después de conectar el mecanismo de reemplazo de consultas, cada solicitud busca la combinación necesaria para el reemplazo. Esta opción me pareció un desperdicio, por lo tanto, la ejecución de la solicitud de datos en sí se realiza en la función transmitida, que antes de la llamada conecta la consulta, reemplaza automáticamente y, después de la llamada, la desconecta.
Solicitud
Estoy usando la generación automática de clases de modelo de datos a partir de una base de datos usando el archivo edmx. Dado que simplemente no puede usar la clase FTS_Int creada en EF debido a la falta de los metadatos necesarios en DbContext , creé una tabla real de acuerdo con su modelo (tal vez alguien conozca una mejor manera, me complacerá su ayuda en los comentarios):
Captura de pantalla de la tabla creada en el archivo 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) );
Después de eso, al actualizar el archivo edmx desde la base de datos, agregue la tabla creada y obtenga su clase generada:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
No se realizarán consultas en esta tabla; solo es necesario para que los metadatos se formen correctamente para crear la consulta. El último ejemplo de uso de consulta de base de datos de texto completo:
/// 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());
También hay soporte para solicitudes asíncronas:
/// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());
Consulta SQL generada antes de Autocorrección:
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
Consulta SQL generada después de autocorrección:
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
Por defecto, la búsqueda de texto completo funciona en todas las columnas de la tabla:
CONTAINSTABLE([dbo].[Article],(*),'text')
Si necesita seleccionar solo ciertos campos, puede especificarlos en el parámetro de campos de la función FtsSearch.Query .
Total
El resultado es el soporte de búsqueda de texto completo en LINQ.
Los matices de este enfoque.
El parámetro de búsqueda en la función FtsSearch.Query no utiliza ninguna comprobación o envoltura para protegerse contra la inyección de SQL. El valor de esta variable se pasa tal cual al texto de la solicitud. Si tienes alguna idea sobre esto, escribe en los comentarios. Usé la expresión regular habitual que simplemente elimina todos los caracteres que no sean letras y números.
También debe tener en cuenta las características de la creación de expresiones para consultas de texto completo. Parámetro para funcionar
CONTAINSTABLE([dbo].[News],(*),' ')
Tiene un formato no válido porque MS SQL requiere la separación de palabras por literales lógicos. Para que la solicitud se complete con éxito, debe corregirla de esta manera:
CONTAINSTABLE([dbo].[News],(*),' and ')
o cambiar la función de recuperación de datos
FREETEXTTABLE([dbo].[News],(*),' ')
Para obtener más información sobre las características de la creación de consultas, es mejor consultar la documentación oficial .
El registro estándar con esta solución no funciona correctamente. Se ha agregado un registrador especial para esto:
/// c# db.Database.Log = (val) => Console.WriteLine(val);
Si observa la consulta generada en la base de datos, se generará antes de procesar las funciones de reemplazo automático.
Durante las pruebas, verifiqué consultas más complejas con múltiples selecciones de diferentes tablas y no hubo problemas.
Fuentes de GitHub