Полнотекстовый поиск entity framework core
Невозможно использовать предикат CONTAINS или FREETEXT для столбца "FirstName", поскольку он не проиндексирован полнотекстовым индексом.
Ниже приведен запрос, выполняемый EF:
Когда я выполняю этот запрос в SQL MS, он выдает такую же ошибку. Несмотря на то, что я могу успешно выполнить запрос содержимого прямо в таблице, например:
Этот Linq работает:
4 ответа
Ваша проблема в том, что вы пытаетесь запустить полнотекстовый режим не из таблицы Employees , а из таблицы Extent2 , то есть SELECT something FROM Employees WHERE . , и это не полнотекстовый индекс. Вам придется переписать ваш linq-запрос или сделать это на T-SQL вместо linq.
(SELECT [Var_42]. [Id] AS [Id], [Var_42]. [FirstName] AS [FirstName], [Var_42]. [Discriminator] AS [Discriminator] FROM [dbo]. [Employee] AS [Var_42] WHERE ([Var_42]. [IsDeleted] = @DynamicFilterParam_IsDeleted_IsDeleted) ИЛИ (@DynamicFilterParam_IsDeleted_DynamicFilterIsDisabled НЕ ПУСТОЙ)) AS [Extent2]
Я предлагаю вам попробовать переписать свой запрос linq следующим образом:
Это должно сгенерировать простой оператор EXISTS , который должен работать нормально. Лучше использовать синтаксис запроса, потому что вы можете давать имена своим подзапросам.
Причина, по которой EF делает этот «беспорядок» для вас, заключается в том, что вы используете динамические фильтры.
[Var_58]. [IsDeleted] = @DynamicFilterParam_IsDeleted_IsDeleted
Если вы попытаетесь отключить динамические фильтры:
Сейчас он вам не поможет, поскольку он просто устанавливает переменную @DynamicFilterParam_IsDeleted_DynamicFilterIsDisabled в сгенерированном запросе, но запрос все равно будет содержать подзапросы [Var_xx] , потому что EntityFramework.DynamicFilters переопределяет некоторые методы Entity Framework. См. эту ссылку.
Почему динамические фильтры вызывают проблемы?
Когда я указываю дополнительные фильтры для запросов сущностей (например, с помощью предложения linq .Where ()), эти дополнительные фильтры заставляют EF создавать вложенные таблицы в запросе.
Итак, динамические фильтры создают подзапросы, и вы получаете исключение, это также хорошо описано здесь. Рекомендуется аналогичный обходной путь:
Обходной путь, который я сейчас использую (который, кажется, работает), заключается в том, чтобы всегда принудительно включать предикат полнотекстового индекса в отдельный подзапрос, чтобы предикат всегда выполнялся для базовой таблицы, а не для промежуточного набора результатов.
Поэтому вам следует попытаться преобразовать все условия, использующие полнотекстовые индексы, в отдельные операторы EXISTS.
Используйте raw sql через Entity Framework, он работает.
RtEntities rv = новые rtEntities ();
Не могли бы вы подтвердить, что у вас включено полнотекстовое индексирование для ВСЕХ столбцов, участвующих в функции "Содержит"
После многочасового гугления, опробовав десятки различных методов со 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 все таки реализуют данный функционал «в коробке».
Хочу поделиться своим костылем в решении довольно банальной проблемы: как подружить полнотекстовый поиск 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 инъекций. Значение этой переменной передается как есть в текст запроса. Если есть какие то идеи по этому поводу пишите в комментариях. Я же использовал обычное регулярное выражение которое просто убирает все символы отличных от букв и цифр.
Также нужно учитывать особенности построения выражений для полнотекстовых запросов. Параметр в функцию
или изменить функцию выборки данных
За более подробной информацией об особенностях создания запросов лучше обратиться к официальной документации.
Стандартное логирование с таким решением работает некорректно. Для этого был добавлен специальный логгер:
Если посмотреть на сформированный запрос к базе данных то он будет сформирован до обработки функциями автозамены.
В ходе тестирования я проверял и на более сложных запросах со множественными выборками из разных таблиц и здесь не возникло никаких проблем.
В этом разделе рассматриваются различные способы запроса данных с помощью Entity Framework, включая LINQ и метод Find. Методы, представленные в этом разделе, также применимы к моделям, созданным с помощью Code First и конструктора EF.
Поиск сущностей с помощью запроса
DbSet и IDbSet реализуют IQueryable, поэтому их можно использовать как начальную точку для написания запроса LINQ к базе данных. Здесь мы не будем подробно рассматривать LINQ, но приведем несколько простых примеров:
Обратите внимание, что DbSet и IDbSet всегда создают запросы к базе данных и всегда используют круговой путь к базе данных, даже если возвращаемые сущности уже присутствуют в контексте. Запрос выполняется в базе данных, если:
Когда из базы данных возвращаются результаты, объекты, отсутствующие в контексте, присоединяются к контексту. Если объект уже есть в контексте, возвращается существующий объект (текущие и исходные значения свойств объекта в записи не переписываются значениями из базы данных).
Когда вы выполняете запрос, сущности, которые были добавлены в контекст, но еще не были сохранены в базе данных, не возвращаются в составе результирующего набора. Чтобы получить данные из контекста, см. раздел Локальные данные.
Если запрос не возвращает строки из базы данных, результатом будет пустая коллекция, а не NULL.
Поиск сущностей с помощью первичных ключей
Метод Find в классе DbSet использует значение первичного ключа, чтобы найти сущность, отслеживаемую контекстом. Если сущность не найдена в контексте, запрос отправляется в базу данных для поиска сущности там. Если сущность не найдена в контексте или в базе данных, возвращается значение NULL.
Метод Find имеет два важных отличия от запроса:
- Круговой путь к базе данных будет использоваться только в том случае, если сущность с указанным ключом не найдена в контексте.
- Метод Find возвращает сущности в состоянии "Добавлено". Это значит, что метод Find возвращает сущности, которые были добавлены в контекст, но еще не были сохранены в базе данных.
Поиск сущности по первичному ключу
В коде ниже приведено несколько примеров использования метода Find.
Поиск сущности по составному первичному ключу
Платформа Entity Framework позволяет сущностям иметь составные ключи, то есть ключи, состоящие из нескольких свойств. Например, вы можете создать сущность BlogSettings, которая представляет собой параметры пользователей для конкретного блога. Так как пользователю необходима только одна сущность BlogSettings для каждого блога, первичный ключ для BlogSettings может состоять из комбинации идентификатора блога и имени пользователя. Следующий код пытается найти BlogSettings по идентификатору = 3 и имени пользователя = johndoe1987:
Если у вас есть составные ключи, вам нужно использовать ColumnAttribute или текучий API, чтобы указать порядок свойств составного ключа. В вызове метода Find эти значения ключа должны указываться в том же порядке.
Читайте также: