Quero compartilhar minha muleta na solução de um problema bastante banal: como fazer amigos em busca de texto completo em MSSQL com o Entity Framework. O tópico é muito especializado, mas me parece relevante hoje. Para os interessados, peço gato.
Tudo começou com dor
Desenvolvo projetos em C # (ASP.NET) e às vezes escrevo microsserviços. Na maioria dos casos, eu uso o banco de dados MSSQL para trabalhar com dados. O Entity Framework é usado como um link entre o banco de dados e meu projeto. Com a EF, tenho amplas oportunidades para trabalhar com dados, gerar as consultas corretas e regular a carga no servidor. O mecanismo LINQ mágico simplesmente cativa com seus recursos. Ao longo dos anos, não imagino mais maneiras mais rápidas e convenientes de trabalhar com o banco de dados. Mas, como quase qualquer ORM, a EF tem várias desvantagens. Primeiro, isso é desempenho, mas esse é o tópico de um artigo separado. E, em segundo lugar, abrange os recursos do próprio banco de dados.
O MSSQL possui uma pesquisa de texto completo incorporada que funciona imediatamente. Para executar consultas de texto completo, você pode usar os predicados internos (CONTAINS e FREETEXT) ou as funções (CONTAINSTABLE e FREETEXTTABLE). Há apenas um problema: a EF não suporta consultas de texto completo, a partir da palavra!
Vou dar um exemplo da experiência real. Suponha que eu tenha uma tabela de artigos - Artigo e crie uma classe para ela que descreva esta tabela:
/// 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; } }
Então, preciso fazer uma seleção desses artigos, digamos, produzir os últimos 10 artigos publicados:
/// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray();
Tudo fica muito bonito até aparecer a tarefa de adicionar pesquisa de texto completo. Como não há suporte para funções de seleção de texto completo no EF (o .NET core 2.1 já o possui parcialmente), resta usar alguma biblioteca de terceiros ou gravar uma consulta em SQL puro.
A consulta SQL do exemplo acima não é tão 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
Em projetos reais, as coisas não são tão simples. As consultas ao banco de dados são muito mais complicadas e é difícil mantê-las manualmente. Como resultado, na primeira vez em que escrevi uma consulta usando o LINQ, obtive o texto gerado da consulta SQL no banco de dados e já introduzi as condições de seleção de dados de texto completo. Então enviei para o db.Database.SqlQuery e recebi os dados necessários. Tudo isso é bom, é claro, desde que a solicitação não precise travar uma dúzia de filtros diferentes com condições e junções complexas.
Então - eu tenho uma dor específica. Temos que resolver isso!
Em busca de uma solução
Mais uma vez, sentado na minha pesquisa favorita, na esperança de encontrar pelo menos alguma solução, me deparei com este repositório . Com esta solução, o suporte ao predicado LINQ (CONTAINS e FREETEXT) pode ser implementado. Graças ao suporte do EF 6 da interface especial IDbCommandInterceptor , que permite interceptar a consulta SQL finalizada, esta solução foi implementada antes de enviá-la ao banco de dados. Uma sequência de marcadores gerada especial é substituída no campo Contains e, depois de gerar a solicitação, esse local é substituído por um exemplo predicado:
/// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text));
No entanto, se a seleção de dados precisar ser classificada pela classificação das correspondências, essa solução não será mais adequada e você terá que escrever a consulta SQL manualmente. Em essência, esta solução substitui o LIKE usual por uma seleção de predicado.
Então, nesse estágio, eu tinha uma pergunta: é possível implementar a pesquisa de texto completo usando as funções integradas do MS SQL (CONTAINSTABLE e FREETEXTTABLE) para que tudo isso possa ser gerado via LINQ e até com suporte para classificar a consulta pelo ranking de correspondências? Como se viu, você pode!
Implementação
Para começar, foi necessário desenvolver a lógica para escrever a própria consulta usando o LINQ. Como em consultas SQL reais com seleções de texto completo, JOIN é mais frequentemente usado para ingressar em uma tabela virtual com classificações, eu decidi seguir o mesmo caminho na consulta LINQ.
Aqui está um exemplo de uma 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);
Esse código ainda não pôde ser compilado, mas já resolveu visualmente o problema de classificar os dados resultantes por classificação. Faltava colocá-lo em prática.
Classe adicional FTS_Int usada nesta solicitação:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
O nome não foi escolhido por acaso, uma vez que a coluna-chave nesta classe deve coincidir com a coluna-chave na tabela de pesquisa (no meu exemplo, com [Article].[Id] type int ). Caso você precise fazer consultas em outras tabelas com outros tipos de colunas-chave, presumi simplesmente copiar uma classe semelhante e criar sua chave do tipo necessário.
A condição para a formação de uma consulta de texto completo deveria ser passada na variável queryText . Para formar o texto dessa variável, uma função separada foi implementada:
/// c# string queryText = FtsSearch.Query( dbContext: db, // , ftsEnum: FtsEnum.CONTAINS, // : CONTAINS FREETEXT tableQuery: typeof(News), // tableFts: typeof(FTS_Int), // search: "text"); //
Cumprimento de uma solicitação pronta e aquisição de dados:
/// c# var result = FtsSearch.Execute(() => query.ToList());
A função final do wrapper FtsSearch.Execute é usada para conectar temporariamente a interface IDbCommandInterceptor . No exemplo fornecido pelo link acima, o autor preferiu usar o algoritmo de substituição de consulta constantemente para todas as solicitações. Como resultado, depois de conectar o mecanismo de substituição da consulta, cada solicitação procura a combinação necessária para a substituição. Essa opção me pareceu um desperdício, portanto, a execução da própria solicitação de dados é realizada na função transmitida, que antes da ligação conecta a consulta, substitui-se automaticamente e, após a ligação, a desconecta.
Aplicação
Estou usando a geração automática de classes de modelo de dados de um banco de dados usando o arquivo edmx. Como você simplesmente não pode usar a classe FTS_Int criada no EF devido à falta dos metadados necessários no DbContext , criei uma tabela real de acordo com seu modelo (talvez alguém saiba o melhor caminho, ficarei feliz em ajudá-lo nos comentários):
Captura de tela da tabela criada no arquivo 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) );
Depois disso, ao atualizar o arquivo edmx do banco de dados, adicione a tabela criada e obtenha sua classe gerada:
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
Nenhuma consulta será feita nessa tabela; ela é necessária apenas para que os metadados sejam formados corretamente para criar a consulta. O exemplo final do uso da consulta de banco de dados 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());
Também há suporte para solicitações assíncronas:
/// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());
Consulta SQL gerada antes da AutoCorreção:
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 gerada após a correção automática:
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 padrão, a pesquisa de texto completo funciona em todas as colunas da tabela:
CONTAINSTABLE([dbo].[Article],(*),'text')
Se você precisar selecionar apenas determinados campos, poderá especificá-los no parâmetro fields da função FtsSearch.Query .
Total
O resultado é um suporte à pesquisa de texto completo no LINQ.
As nuances dessa abordagem.
O parâmetro de pesquisa na função FtsSearch.Query não usa nenhuma verificação ou invólucro para proteger contra injeção de SQL. O valor dessa variável é passado como está para o texto da solicitação. Se você tem alguma idéia sobre isso, escreva nos comentários. Eu usei a expressão regular usual que simplesmente remove todos os caracteres, exceto letras e números.
Você também precisa considerar os recursos de construção de expressões para consultas de texto completo. Parâmetro para funcionar
CONTAINSTABLE([dbo].[News],(*),' ')
Ele tem um formato inválido porque o MS SQL requer a separação de palavras por literais lógicos. Para que a solicitação seja concluída com êxito, é necessário corrigi-lo assim:
CONTAINSTABLE([dbo].[News],(*),' and ')
ou alterar a função de recuperação de dados
FREETEXTTABLE([dbo].[News],(*),' ')
Para mais informações sobre os recursos da criação de consultas, é melhor consultar a documentação oficial .
O log padrão com esta solução não funciona corretamente. Um logger especial foi adicionado para isso:
/// c# db.Database.Log = (val) => Console.WriteLine(val);
Se você olhar a consulta gerada no banco de dados, ela será gerada antes de processar as funções de substituição automática.
Durante o teste, verifiquei consultas mais complexas com várias seleções de tabelas diferentes e não houve problemas.
Fontes do GitHub