Полнотекстовый поиск entity framework core
PostgreSQL has built-in support for full-text search, which allows you to conveniently and efficiently query natural language documents.
Придумываем API
Не стал заморачиваться со сложными способами и просто объявил метод-расширение для строк:
В теле метода просто кидаем исключение, потому что это просто маркер, не предназначенный для запуска на клиенте. В пользовательском коде это должно выглядеть как-то так:
осталось придумать, как это реализовать.
Operation translation
Almost all PostgreSQL full text search functions can be called through LINQ queries. All supported EF Core LINQ methods are defined in extension classes in the Microsoft.EntityFrameworkCore namespace, so simply referencing the Npgsql provider will light up these methods. The following table lists all supported operations; if an operation you need is missing, please open an issue to request for it.
Хочу поделиться своим костылем в решении довольно банальной проблемы: как подружить полнотекстовый поиск MSSQL c Entity Framework. Тема очень узкоспециальная, но как мне кажется, актуальна на сегодняшний день. Интересующихся прошу под кат.
В MSSQL есть встроенный полнотекстовый поиск который работает “из коробки”. Для выполнения полнотекстовых запросов можно воспользоваться встроенными предикатами (CONTAINS и FREETEXT) или функциями (CONTAINSTABLE и FREETEXTTABLE). Есть только одна проблема: EF не поддерживает полнотекстовые запросы, от слова совсем!
Приведу пример из реального опыта. Допустим у меня есть таблица статей — Article, и я создаю для нее класс описывающий эту таблицу:
Потом мне надо сделать выборку из этих статей, скажем, вывести последние 10 опубликованных статей:
SQL запрос из примера выше не такой уж и сложный:
В реальных проектах все обстоит не так просто. Запросы к базе данных на порядок сложнее и поддерживать их в ручную сложно и долго. В результате первое время я писал запрос с помощью LINQ, потом доставал сгенерированный текст SQL запроса к БД, и уже в него внедрял полнотекстовые условия выборки данных. Далее отправлял это в db.Database.SqlQuery и получал нужные мне данные. Это все конечно хорошо пока на запрос не нужно навешать десяток различных фильтров со сложными join-нами и условиями.
Итак — у меня есть конкретная боль. Надо ее решать!
В очередной раз сидя в своем любимом поиске в надежде отыскать хоть какое-то решение я наткнулся на этот репозиторий. С помощью этого решения можно внедрить в LINQ поддержку предикатов (CONTAINS и FREETEXT). Благодаря поддержки EF 6 специального интерфейса IDbCommandInterceptor , позволяющего делать перехват готового запроса SQL, перед отправкой его в БД и было реализовано данное решение. В поле Contains подставляется специальная сгенерированная строка маркер, а потом после генерации запроса это место заменяется на предикат Пример:
Однако если выборку данных нужно отсортировать по рангу совпадений, то это решение уже не подойдет и придется писать SQL запрос вручную. По сути, это решение, подменяет обычный LIKE на выборку по предикату.
Итак, на этом этапе у меня встал вопрос: можно ли реализовать реальный полнотекстовый поиск с помощью встроенных функций MS SQL (CONTAINSTABLE и FREETEXTTABLE) чтобы все это генерировалось через LINQ да еще и с поддержкой сортировки запроса по рангу совпадений? Как оказалось, можно!
Для начала нужно было разработать логику написания самого запроса с помощью LINQ. Поскольку в реальных SQL запросах с полнотекстовыми выборками чаще всего используют JOIN для присоединения виртуальной таблицы с рангами, я решил пойти по этому же пути и в LINQ запросе.
Вот пример такого LINQ запроса:
Такой код еще нельзя было скомпилировать, но он уже визуально решал задачу по сортировке результирующих данных по рангу. Оставалось реализовать его на практике.
Дополнительный класс FTS_Int используемый в данном запрос:
Название было выбрано не случайно, так как ключевой столбец в этом классе должен совпадать по тику с ключевым столбцом в таблице поиска (в моем примере с [Article].[Id] тип int ). В случае если нужно делать запросы по другим таблицам с другими типами ключевых столбцов, я предполагал просто скопировать подобный класс и создать его Key того типа который нужен.
Само условие для формирование полнотекстового запроса предполагалось передавать в переменной queryText . Для формирование текста этой переменной была реализована отдельная функция:
Выполнение готового запроса и получение данных:
Последняя функция FtsSearch.Execute обертка используется для временного подключения интерфейса IDbCommandInterceptor . В примере приведенном по ссылке выше автор предпочел использовать алгоритм подмены запросов постоянно для всех запросов. В результате после подключения механизма замены запросов в каждом запросе ищется необходимая комбинация для замены. Мне такой вариант показался расточительным, поэтому выполнение самого запроса данных выполняется в передаваемой функции, которая перед вызовом подключает автозамену запроса а после вызова — отключает.
Я использую автогенерацию классов моделей данных из БД с помощью файла edmx. Поскольку просто созданный класс FTS_Int использовать в EF нельзя по причине отсутствия необходимых метаданных в DbContext , я создал реальную таблицу по его модели (может кто знает способ получше, буду рад вашей помощи в комментариях):
Скриншот таблице созданной в файле edmx
После этого при обновлении файла edmx из БД добавляем созданную таблицу и получаем ее сгенерированный класс:
Запросы к этой таблице вестись не будут, она лишь нужна, чтобы правильно сформировались метаданные для создания запроса. Финальный пример использования полнотекстовых запрос к БД:
Также есть поддержка асинхронных запросов:
SQL запрос сформированный до автозамены:
SQL запрос сформированный после автозамены:
По умолчанию полнотекстовый поиск работает по всем столбцам таблицы:
Если нужно сделать выборку только по некоторым полям, то их можно указать в параметре fields функции FtsSearch.Query .
Результат — поддержка полнотекстового поиска в LINQ.
Нюансы данного подхода.
Параметр search в функции FtsSearch.Query не использует каких либо проверок или оберток для защиты от SQL инъекций. Значение этой переменной передается как есть в текст запроса. Если есть какие то идеи по этому поводу пишите в комментариях. Я же использовал обычное регулярное выражение которое просто убирает все символы отличных от букв и цифр.
Также нужно учитывать особенности построения выражений для полнотекстовых запросов. Параметр в функцию
или изменить функцию выборки данных
За более подробной информацией об особенностях создания запросов лучше обратиться к официальной документации.
Стандартное логирование с таким решением работает некорректно. Для этого был добавлен специальный логгер:
Если посмотреть на сформированный запрос к базе данных то он будет сформирован до обработки функциями автозамены.
В ходе тестирования я проверял и на более сложных запросах со множественными выборками из разных таблиц и здесь не возникло никаких проблем.
После многочасового гугления, опробовав десятки различных методов со StackOverflow и прочих подобных сайтов, я пришел к выводу, что очевидного и простого решения проблемы нет, поэтому решил сделать собственное, об этом и пойдет речь далее.
Реализация
Основным требованием к решению проблемы, является простота интеграции в любой новый (существующий) проект. В Code First принято все настраивать атрибутами, поэтому хорошо было бы сделать так:
при этом, не хотелось бы переопределять DatabaseInitializer и делать прочие нетривиальные действия.
В своей работе я использую Visual Studio 2013 Ultimate. Создадим новый проект типа Class Library, сразу добавим в него Entity Framework 6 Beta 1 с помощью NuGet консоли (Package Manager Console):
PM> Install-Package EntityFramework -Pre
Создадим атрибуты Index и FullTextSearch, а так же перечисление для FullTextSearch:
Если Вы ранее работали с полнотекстовым поиском, то Вы наверняка поняли зачем нужен Contains и FreeText, если нет, то Вам сюда.
Далее, создадим абстрактный класс, унаследованный от DbContext:
чтобы не раздувать пост, здесь намеренно убраны summary и некоторые комментарии, полная версия на GitHub'e. Если кратко пояснить, то EF создает модель при первичном обращении к DbContext'у, соответственно строить индексы на конструкторе мы не можем, остается самый простой вариант построить их после создания модели, при попытке уничтожить экземпляр DbContext. Далее, чтобы не нагружать БД каждый раз несколькими запросами и попыткой создания, в лучших традициях EF создадим в базе служебную таблицу __IndexBuildingHistory, наличие которой, будет свидетельствовать о наличии индексов. Остальное очевидно.
В целом, если уже сейчас создать модель, пометить ее атрибутами и запустить проект, то индексы будут успешно созданы, однако, нам еще нужно удобное использование полнотекстового индекса, для это создадим класс расширение (extension class):
Вот и все, казалось бы, такая популярная проблема как индексы и полнотекстовый поиск требует особого внимания со стороны создателей Entity Framework, однако, простого решения на сегодняшний день не было. Данная реализация с лихвой перекрывает мои требования к индексации, если Вам чего то не хватает (обработки ошибок, настроек — например, список стоп-слов и т.д.), Вы можете самостоятельно забрать проект с GitHub'a и доработать, либо написать мне. Статья была бы совсем скучной, если бы мы не попробовали как все это работает, поэтому переходим к использованию.
Использование
1. Создадим проект Console application
2. Добавим Entity Framework 6 beta через NuGet
3. Добавим ссылку на библиотеку (если Вы не читали про реализацию, то Вы можете скачать готовую библиотеку, ссылки в конце статьи)
4. Создадим простую сущность, без вложеностей и связей, для примера этого достаточно:
Сущность животное, с названием (Name), по которому мы построим обычный индекс, описанием (Description) — построим полнотекстовый индекс и прочими полями для вида, мы не будем их использовать. Обратите внимание на строку [StringLength(200)], при создании индекса по строковым полям она обязательна, т.к. MSSQL позволяет строить индексы по полям, размер которых не превышает 900 байт — сколько это в символах, зависит от выбранной Вами кодировки базы данных.
5. Создадим контекст базы данных:
единственная разница здесь в наследовании, обычно Вы наследуетесь от DbContext, а теперь от нашей DbContextIndexed
6. В Programm.cs добавим обращение к контексту, чтобы спровоцировать создание базы данных:
7. В config файле проекта пропишите строку подключения к базе данных с названием DataContext:
8. Нажимаем F5, чтобы создать базу данных, когда программа завершится, с помощью Managment Studio можно убедится, что все работает, как мы запланировали:
9. Теперь, давайте попробуем добавить данные, чтобы опробовать поиск:
запустим, чтобы данные записались в БД, теперь попробуем поискать:
результат следующий:
У меня установлена версия MSSQL 2008R2, поэтому результат хороший, но не идеальный. Насколько я знаю в 2013-ой версии мы бы еще получили значение пантера, т.к. «кошка», тоже бы учлось.
Я считаю, что довольно простым, и самое главное, «стандартным» способом можно пользоваться полнотекстовым поиском и строить индексы по полям. Данной реализации достаточно для 95% маленьких проектов, но я искренне надеюсь, что создатели Entity Framework все таки реализуют данный функционал «в коробке».
Источники
Однажды пасмурным мартовским субботним утром я решил посмотреть, как обстоят дела у Майкрософта в благом деле по трансформированию мастодонта Entity Framework в Entity Framework Core. Ровно год назад, когда наша команда начинала новый проект и подбирала ORM, то руки чесались использовать все как можно более стильное и молодежное. Однако, присмотревшись к EFC, мы поняли, что он еще очень далек продакшна. Очень много проблем с N+1 запросами (сильно улучшили во 2й версии), кривые вложенные селекты (пофиксали в 2.1.0-preview1), нет поддержки Many-to-Many (все еще нет) и вишенка на торте — отсутствие поддержки DbGeometry, что в нашем проекте было очень критично. Примечательно, что последняя фича находится в road map проекта с 2015 года в списке высокоприоритетных. У нас в команде есть даже шутка на эту тему: "Эту задачу добавим в список высокоприоритетных". И вот прошел один год с последней ревизии EFC, вышла уже вторая версия данного продукта и я решил проверить, как обстоят дела.
На мой взгляд один из лучших способов проверить продукт — это попытаться расширить его какой-нибудь кастомной фичей. Это сразу проливает свет на: а) качество архитектуры; б) качество документации; в) поддержку сообщества.
Беглый просмотр первой страницы выдачи гугла показал, что полнотекстовый поиск в EFC пока не поддерживается, но есть планы. Отлично, это нам и надо, можно попробовать реализовать предикат CONTAINS из T-SQL самому.
Проблема и решение
Моя догадка о том, что SQL генератор хочет возвращаемое значение была верна. Чтобы решить эту проблему, нужно было в SqlVisitor'e подменить VisitBinary на VisitUnary, т.к. CONTAINS является унарным оператором. Вот тут есть реализованная идея. Действуем по аналогии, создаем наш кастомный генератор, подключаем его в контейнере и запускаем снова.
Все заработало, генерируется правильный SQL. Метод ContainsText может участвовать в различных выражениях, в общем является полноценным участником EFC.
Setting up and querying a full text search index on an entity
As the PostgreSQL documentation explains, full-text search requires an index to run efficiently. This section will show two ways to do this, each having its benefits and drawbacks. Please read the PostgreSQL docs for more information on the two different approaches.
Method 1: tsvector column
This method adds a tsvector column to your table, that is automatically updated when the row is modified. First, add an NpgsqlTsVector property to your entity:
Setting up the column to be auto-updated depends on your PostgreSQL version. On PostgreSQL 12 and above, the column can be a simple generated column, and version 5.0.0 contains sugar for setting that up. In previous versions, you must manually set up database triggers that update the column instead.
The below only works on PostgreSQL 12 and version 5.0.0 of the EF Core provider.
The following will set up a generated tsvector column, over which you can easily create an index:
First, modify the OnModelCreating() of your context class to add an index as follows:
Now generate a migration ( dotnet ef migrations add . ), and open it with your favorite editor, adding the following:
Once your auto-updated tsvector column is set up, any inserts or updates on the Products table will now update the SearchVector column and maintain it automatically. You can query it as follows:
DI в EFC
Устанавливаем и запускаем.
Проблемы с генерированием SQL
Method 2: Expression index
Version 5.0.0 of the provider includes sugar for defining the appropriate expression index; if you're using an older version, you'll have to define a raw SQL migration yourself.
Create a migration which will contain the index creation SQL ( dotnet ef migrations add . ). At this point, open the generated migration with your editor and add the following:
Once the index is created on the Title and Description columns, you can query as follows:
Пишем свой транслятор
Транслятор должен реализовать интерфейс IMethodCallTranslator . Контракт, который он должен исполнить в методе Expression Translate(MethodCallExpression methodCallExpression) , достаточно прост: если входное выражение не известно — возвращаем null, в другом случае — преобразовываем в Sql выражение.
Вот как выглядит класс:
Осталось только подключить его при помощи CustomSqlMethodCallTranslator:
Mapping
Поиск точек расширения
Самый лучший способ сделать что-то новое — это сделать по аналогии. Какой самый ближайший близкий по смыслу оператор, который мы хотим реализовать? Правильно, LIKE . Оператор LIKE транслируется из метода String.Contains . Все что нам нужно сделать, это подсмотреть, как это сделано разработчиками EFC.
Качаем репозиторий, открываем его в Visual Studio 2017 и… Visual Studio уходит в мертвый штопор. Ну ок, жирные IDE для дилетантов, берем Visual Studio Code, там все летает. Более того, Code Lens работает из коробки, просто удивительно.
Находим файлы, содержащие Contains в названии,SqlServerContainsOptimizedTranslator.cs — наш кандидат. Интересно, что же в нем такого оптимизированного? Оказывается, EFC, в отличие от EF использует CHARINDEX > 0 вместо LIKE '%pattern%' .
Этот пост на SO ставит под сомнение решение команды EFC.
Выводы
Архитектурно EFC ушел далеко вперед от классического EF. Расширить его не составляет никаких проблем, однако будьте готовы искать решения в исходниках. Для меня это один из главных способов узнать что-то новое, хоть он и занимает много времени.
Мейнтейнеры проекта готовы дать развернутый ответ на ваш вопрос. Я заметил, что спустя 4 дня после того, как я зарепортил свой баг, было открыто еще ~20 issues. На большую часть из них был получен ответ.
Готовый код находится здесь. Чтобы его запустить, вам понадобится последняя VS и docker на linux контейнерах, либо SQL Server с Full-Text Search. К сожалению, localdb поставляется без лингвистических сервисов и подключить их не представляется возможным. Я воспользовался докер-файлом из интернета. Сборка и запуск docker образа находится в файлe database-create.ps1.
Out of the box Entity Framework does not support Full Text Search. To do Full Text Search you have a number of options to get it working. In this blog post I’ll describe a method to get Full Text Search working using a table valued function. The method does the Full Text Search in a table valued function that returns an id list, the returned ids can be used to filter the records from an actual table.
The solution I use to get Full Text Search working with Entity Framework uses the following techniques/packages:
- Entity Framework 6.1.2
- Entity Framework Functions 1.3.1.0
- Sql table valued functions
- Entity Framework interception
Use Full Text Search from code
After you have implemented the infrastructure to use Full Text Search from Entity Framework, the code that uses the Full Text Search can look like this:
Under laying Table Valued Function
Get started with Full Text Search will give you some context on how to setup full text search. You need to prepare your table/column to be searchable.
The goal is to search the TextField column in the Sample table on the words in the searchtxt. The Full Text Search is done in a table valued function called fnSampleTableFTS. This function gets the searchterm parameter. The result of the function is a list of ids.
Database context
EntityFramework.Functions library provides a few simple APIs, following the pattern of Entity Framework and LINQ to SQL.
To connect the function call to the table valued function we need to implement a custom datacontext which enables you to connect the function in the database to a function on your database context. The mapping is done with Entity Framework Functions. In the following sample datacontext, the function fnSampleTableFTS is mapped to the fnSampleTableFTS table valued function in the database. In the function, the parameters are prepared and sanitized (AndPartsFormatter) so they can be used as Full Text Search parameters. In the OnModelCreating the function and new type are registered on the context.
Formatting the search term
To make Full Text Search work, you need to format the search term. You can choose on how to search in to column by formatting the search term. In this case I split the words and add AND to find match on all words. Besides preparing the input I sanitize the input to prevent nasty stuff inserted into my search terms. More options on how to format your search term or setup your query: query with Full Text Search.
Correct the length of the search parameter
As last you need to add an IDBCommandInterceptor to change the length of your query parameter (it is registered in the OnModelCreating method in the context). Full Text Search only accepts nvarchar sizes till 1000. Entity Framework makes all parameters standard 4000. To correct this the interceptor replaces the size of your search parameter with length 1000:
When everything is in place you can easily change the behavior of your search by updating the table valued function or change the formatting of the search term.
Читайте также: