Хочу поделиться своим костылем в решении довольно банальной проблемы: как подружить полнотекстовый поиск MSSQL c Entity Framework. Тема очень узкоспециальная, но как мне кажется, актуальна на сегодняшний день. Интересующихся прошу под кат.
Все началось с боли
Я разрабатываю проекты на C# (ASP.NET) и иногда пишу микросервисы. В большинстве случаев для работы с данными я использую базу данных MSSQL. В качестве связующего звена между базой данных и моим проектом используется Entity Framework. С EF я получаю широкие возможности для работы с данными, формирования правильных запросов, регулирования нагрузки на сервер. Волшебный механизм LINQ просто очаровывает своими возможностями. Спустя годы я уже и не представляю более быстрые и удобные способы по работе с базой данных. Но как и практически любая ORM, EF имеет ряд недостатков. Во первых это производительность, но это тема отдельной статьи. А во вторых — это покрытие возможностей самой базы данных.
В MSSQL есть встроенный полнотекстовый поиск который работает “из коробки”. Для выполнения полнотекстовых запросов можно воспользоваться встроенными предикатами (CONTAINS и FREETEXT) или функциями (CONTAINSTABLE и FREETEXTTABLE). Есть только одна проблема: EF не поддерживает полнотекстовые запросы, от слова совсем!
Приведу пример из реального опыта. Допустим у меня есть таблица статей — Article, и я создаю для нее класс описывающий эту таблицу:
/// 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 core 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 и получал нужные мне данные. Это все конечно хорошо пока на запрос не нужно навешать десяток различных фильтров со сложными join-нами и условиями.
Итак — у меня есть конкретная боль. Надо ее решать!
В поисках решения
В очередной раз сидя в своем любимом поиске в надежде отыскать хоть какое-то решение я наткнулся на этот репозиторий. С помощью этого решения можно внедрить в LINQ поддержку предикатов (CONTAINS и FREETEXT). Благодаря поддержки EF 6 специального интерфейса IDbCommandInterceptor, позволяющего делать перехват готового запроса SQL, перед отправкой его в БД и было реализовано данное решение. В поле Contains подставляется специальная сгенерированная строка маркер, а потом после генерации запроса это место заменяется на предикат Пример:
/// c#
var text = FullTextSearchModelUtil.Contains("code");
db.Tables.Where(c=>c.Fullname.Contains(text));Однако если выборку данных нужно отсортировать по рангу совпадений, то это решение уже не подойдет и придется писать SQL запрос вручную. По сути, это решение, подменяет обычный LIKE на выборку по предикату.
Итак, на этом этапе у меня встал вопрос: можно ли реализовать реальный полнотекстовый поиск с помощью встроенных функций MS SQL (CONTAINSTABLE и FREETEXTTABLE) чтобы все это генерировалось через LINQ да еще и с поддержкой сортировки запроса по рангу совпадений? Как оказалось, можно!
Реализация
Для начала нужно было разработать логику написания самого запроса с помощью LINQ. Поскольку в реальных SQL запросах с полнотекстовыми выборками чаще всего используют 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). В случае если нужно делать запросы по другим таблицам с другими типами ключевых столбцов, я предполагал просто скопировать подобный класс и создать его Key того типа который нужен.
Само условие для формирование полнотекстового запроса предполагалось передавать в переменной 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 нельзя по причине отсутствия необходимых метаданных в DbContext, я создал реальную таблицу по его модели (может кто знает способ получше, буду рад вашей помощи в комментариях):
Скриншот таблице созданной в файле 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] DESCSQL запрос сформированный после автозамены:
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')Если нужно сделать выборку только по некоторым полям, то их можно указать в параметре fields функции FtsSearch.Query.
Итого
Результат — поддержка полнотекстового поиска в LINQ.
Нюансы данного подхода.
Параметр search в функции
FtsSearch.Queryне использует каких либо проверок или оберток для защиты от SQL инъекций. Значение этой переменной передается как есть в текст запроса. Если есть какие то идеи по этому поводу пишите в комментариях. Я же использовал обычное регулярное выражение которое просто убирает все символы отличных от букв и цифр.
Также нужно учитывать особенности построения выражений для полнотекстовых запросов. Параметр в функцию
/* Запрос с ошибкой */ CONTAINSTABLE([dbo].[News],(*),'Новое исследование')
имеет недопустимый формат так как MS SQL требует разделения слов логическими литералами. Чтобы запрос был выполнен успешно нужно исправить его так:
/* Корректный запрос */ CONTAINSTABLE([dbo].[News],(*),'Новое and исследование')
или изменить функцию выборки данных
/* Корректный запрос */ FREETEXTTABLE([dbo].[News],(*),'Новое исследование')
За более подробной информацией об особенностях создания запросов лучше обратиться к официальной документации.
Стандартное логирование с таким решением работает некорректно. Для этого был добавлен специальный логгер:
/// c# db.Database.Log = (val) => Console.WriteLine(val);
Если посмотреть на сформированный запрос к базе данных то он будет сформирован до обработки функциями автозамены.
В ходе тестирования я проверял и на более сложных запросах со множественными выборками из разных таблиц и здесь не возникло никаких проблем.
Исходники на GitHub
