Entity framework поиск по таблице
Обновлено: 05.07.2024
Невозможно использовать предикат 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 ();
Не могли бы вы подтвердить, что у вас включено полнотекстовое индексирование для ВСЕХ столбцов, участвующих в функции "Содержит"
В этом разделе рассматриваются различные способы запроса данных с помощью 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 эти значения ключа должны указываться в том же порядке.
В предыдущей статье мы рассмотрели как создаются LINQ-запросы в Entity Framework. В этой и последующих статьях мы рассмотрим операции CRUD (create, read, update, delete) для работы с данными. Для извлечения данных из таблиц в SQL используется инструкция SELECT. Знать детали SQL-запросов вам не нужно, т.к. Entity Framework заботится о преобразовании LINQ-запросов в SQL.
Для получения всех данных из таблицы вам даже не нужно использовать LINQ-запрос, вы можете просто использовать свойство класса контекста, ссылающееся на класс модели и имеющее тип DbSet<T>. Entity Framework создаст запрос в базу данных для загрузки всех данных из таблицы, связанной с этим классом модели. Давайте добавим метод GetAllCustomers() в наш консольный проект, который мы создали ранее, чтобы извлечь все данные покупателей (т.к. мы еще не рассмотрели вставку данных с помощью Entity Framework, чтобы протестировать примеры выборки данных, вы можете заполнить таблицы Customers и Orders вручную, используя средства Visual Studio или SQL Server Management Studio):
Запрос context.Customers в этом примере извлечет все данные из таблицы Customers. Для запуска примера, вы можете вызвать метод GetAllCustomers() в методе Main() консольного приложения. Для этого примера Entity Framework сгенерирует следующий SQL-код (здесь я привожу фрагменты кода SQL для тех читателей, которые хорошо знакомы с T-SQL и хотят знать как EF транслирует запросы LINQ в SQL):
Код в этом примере возможно покажется несколько перегруженным, за счет использования псевдонима Extent1 для таблицы Customers и других псевдонимов для столбцов, а также явного указания схемы по умолчанию dbo. Это связано с тем, что Entity Framework имеет общий алгоритм построения запросов, который не только обслуживает этот очень простой запрос в примере, но также используется и для гораздо более сложных сценариев, в которых могут понадобиться эти псевдонимы.
На рисунке ниже, для наглядности, показана та область таблицы Customers, данные которой извлекаются в этом примере:
Стоит также отметить, что в этом примере мы используем отложенный LINQ-запрос. Если в вашем приложении предполагается делать несколько выборок данных в одном месте, отложенные запросы могут сильно ухудшить производительность приложения. Давайте рассмотрим пример:
Указание столбцов, условий и сортировки в запросе
В запросе данных из таблицы вы можете указать порядок сортировки, который будет использован для выборки данных. Для этого используется вспомогательный метод OrderBy(). Ниже показан пример, в котором данные заказчика сортируются по фамилии:
Приведенный выше код использует LINQ для создания запроса выборки сортированных данных из таблицы, а затем перебирает результаты запроса и отображает имя и фамилию каждого покупателя. После получения коллекции данных через свойство Customers класса контекста, вы можете использовать вспомогательные LINQ-методы OrderBy(), GroupBy() и Join() для сортировки, группировки данных на основе ключа и соединения таблиц. Эти методы будут транслированы на одноименные инструкции языка SQL.
Другой часто определяемой задачей при выборке данных, является их фильтрация на основе определенного условия. В SQL для этих целей используется оператор WHERE, LINQ содержит одноименный метод Where(), с помощью которого можно добавить дополнительное условие для загрузки данных:
В этом запросе мы выбираем всех покупателей старше 25 лет.
До сих пор мы выбирали из базы данных только коллекцию объектов модели, т.е. коллекцию IQueryable<Customer>. Зачастую извлекать все данные не нужно, а нужно извлечь данные только из определенного столбца таблицы, создавая проекцию таблицы. Такой подход позволяет улучшать производительность, когда запрашиваются данные из таблицы, содержащей много столбцов, а в приложении нужно работать только с одним или несколькими столбцами.
Например, мы могли бы захотеть выбирать только фамилии покупателей из таблицы базы данных и отбрасывать всю другую информацию. Для этих целей в LINQ используется метод Select(), которому передается делегат, в котором выбираются нужные свойства модели. Этот метод возвращает уже не коллекцию объектов Customer, а коллекцию простых типов, например, для фамилий он вернет тип IQueryable<string>. Ниже показан соответствующий пример:
Этот запрос транслируется в следующий SQL-код:
Здесь видно, что из таблицы будут выбираться данные только фамилии покупателей (столбец LastName указывается после инструкции SELECT). На рисунке ниже наглядно показано, какие данные выбирает этот запрос:
В нашем примере мы загружаем данные имен и фамилий в анонимный объект. Иногда бывает необходимо загрузить данные в объект модели, инициализировав только некоторые его свойства. В контексте нашего примера это означает, что мы могли бы создать коллекцию объектов Customers, в которую загрузили бы из базы данных всех пользователей, указав только два свойства FirstName и LastName. Для этих целей вы могли бы предположить, что нужно написать следующий пример:
Если вы запустите этот пример, то в приложении возникнет исключение NotSupportedException, в котором говорится, что Entity Framework не может использовать сложный тип Customer с запросом LINQ to Entities. Для этих целей вы должны сначала загрузить коллекцию анонимных объектов, которая инициализируется из базы данных, а затем создать коллекцию объектов Customer, которая инициализируется из коллекции анонимных объектов. Эту задачу можно выполнить в одном запросе, функционально разделив сложный запрос на две части – первая будет использоваться Entity Framework для извлечения данных из базы, вторая будет работать в памяти приложения и инициализировать коллекцию объектов Customer. В качестве разделителя нужно использовать метод AsEnumerable():
Метод AsEnumerable() в LINQ просто преобразует коллекцию IQueryable к IEnumerable. В простых приложениях, работающих с коллекциями данный метод практически не используется, т.к. в нем нет смысла – интерфейс IQueryable является производным от интерфейса IEnumerable. Но этот метод оказывает существенное влияние при использовании с Entity Framework, указывая, что цепочку методов в запросе до его вызова нужно выполнить, отправив запрос к базе данных, а последующие методы будут оперировать уже на коллекции в памяти приложения. Если вы запустите этот пример, то можете убедиться в его работоспособности.
Поиск в запросе
Пока вы видели запросы, которые возвращают коллекцию объектов из базы данных, но иногда нужно чтобы запрос возвращал один объект. Наиболее распространенным сценарием для запросов, возвращающих один объект, является поиск определенного объекта с заданным ключом. DbContext API позволяет делать это очень просто, используя метод Find() класса DbSet. Этот метод принимает значение, которое нужно найти в таблице и возвращает соответствующий объект, если он найден, или null если не найден.
Поиск в запросе в Entity Framework использует следующую последовательность:
Сначала выполняется поиск в коллекции объектов, уже загруженных из базы данных в память приложения.
Если к коллекции добавлялись новые объекты, то поиск выполняется и в них.
Выполняется поиск в базе данных в объектах, которые еще не были загружены в память приложения.
Чтобы увидеть реализацию поиска, давайте добавим новый метод FindCustomer() в котором мы будем принимать идентификатор покупателя от пользователя, путем ввода его в консоль и затем будем искать покупателя с соответствующим идентификатором в базе данных:
Метод Find() осуществляет поиск по первичному ключу таблицы. Как вы знаете, первичные ключи бывают составными, поэтому методу Find() можно передать несколько значений, для поиска в ключах, при этом эти значения должны следовать в том же порядке, в котором свойства первичных ключей определены в классе модели. Напомню, что в Code-First для этого используется атрибут аннотаций данных Column, с переданным ему параметром Order, либо метод HasColumnOrder(), если вы используете для настроек Fluent API.
Если вам нужно выполнить поиск в обычных столбцах, не являющихся ключами таблицы, то для этого можно использовать условие через метод Where(). Например, если мы хотим найти всех покупателей, чья фамилия начинается на русскую букву “В”, то можем использовать следующий запрос:
Локальные данные
Во всех предыдущих примерах мы использовали запрос к свойству класса контекста типа DbSet (context.Customers). Как говорилось ранее, использование этого свойства приводит к созданию запроса к базе для выборки всех данных из привязанной таблицы. Мы также использовали метод Find(), который ищет в памяти приложения данные до создания запроса к этой базе данных, в уже загруженных ранее данных.
Могут быть случаи, когда вы захотите использовать более сложный запрос для данных, которые уже были загружены из базы данных и находятся в памяти приложения, т.е использовать отложенный запрос, чтобы избежать повторной отправки запроса к базе данных. Например, мы могли бы загружать данные всех пользователей только при первом запуске приложения и сохранить их в каком-то локальном для приложения объекте, а затем в различных частях приложения создавать запросы к уже существующей коллекции, чтобы всякий раз не загружать ее снова.
Еще одной причиной использования такой локальной коллекции данных, является то, что в приложении в разных местах могут добавляться новые данные к этой коллекции, которые должны быть также использованы в запросах. При использовании не отложенного метода ToList(), запрос сразу же отправляется в базу данных и извлекаются данные, содержащиеся в базе данных, игнорируя новые данные в памяти приложения. Наглядно это показано на следующем примере:
Здесь, при перечислении данных покупателей, новый добавленный покупатель не будет отображаться в списке.
Entity Framework позволяет хранить локальные данные для объектов DbSet, определяя свойство Local в этом классе. Это свойство вернет все данные, которые были загружены из базы данных плюс любые добавленные новые данные в приложении. Данные, которые были помечены в приложении как удаленные, но при этом еще не удаленные из базы данных, также будут фильтроваться в запросе.
Давайте начнем с очень простой задачи и определим, сколько на текущий момент находится объектов в памяти приложения:
Этот метод позволяет проверить, сколько объектов Customer на данный момент находятся в памяти приложения. Если вы запустите приложение с вызовом этого метода, вы увидите, что количество объектов равно нулю. Мы получаем нулевой результат, потому что мы не выполняем никаких запросов, чтобы загрузить покупателей из базы данных, и мы не добавляли новые объекты Customer в коде. Давайте немного изменим этот метод, и запросим некоторые данные из базы:
У меня в базе данных находится три заказчика, поэтому данный пример вернет следующий результат:
Перебор содержимого DbSet в цикле по каждому элементу является одним из способов, чтобы получить все данные из памяти приложения, но это немного неэффективно, чтобы каждый раз выполнять цикл просто ради загрузки данных. К счастью, DbContext API включает специальный метод Load() в классе DbSet, который можно вызвать для ручного запуска процесса загрузки данных из базы. Ниже показано использование этого метода (не забудьте указать пространство имен System.Data.Entity):
Этот код гораздо лаконичнее, чем тот, что мы использовали ранее. В сборке System.Data.Entity определен также расширяющий IQueryable<T> метод Load(), поэтому мы можем использовать его не только для загрузки всех данных из таблицы, но также использовать LINQ-методы для сужения выборки. Например, следующий запрос посчитает, сколько находится в памяти объектов Customer, чей возраст превышает 25 лет:
Использование особенностей коллекции ObservableCollection
Если вы смотрели примеры использования локальных данных довольно внимательно, то должны были увидеть, что свойство Local возвращает специальный тип обобщенной коллекции ObservableCollection. Этот тип коллекции позволяет получать уведомления, когда добавляются или удаляются элементы из коллекции. Коллекция ObservableCollection полезна в ряде сценариев, работающих с привязкой данных. Например, мы ее использовали при рассмотрении WPF в статье “Привязка к коллекции объектов”, чтобы изменять графический интерфейс приложения, когда добавлялись данные.
Эта коллекция имеет событие CollectionChanged, в обработчике которого мы можем вносить полезные действия. В контексте локальных данных Entity Framework это событие возникает когда мы загружаем или удаляем данные из базы, работаем с данными в памяти приложения или добавляем новые объекты в DbContext. Ниже показан пример:
Этот код добавляет новый обработчик событий изменения коллекции локальных данных. Этот обработчик указан через лямбда-выражения, хотя можно было и добавить отдельный метод. С помощью этого обработчика мы фиксируем в консоли, когда добавляется, а когда удаляется запись из коллекции. Если вы запустите приложение с вызовом метода LocalLinqQueryies(), то вы увидите, что EF загрузит из базы данных три записи в локальную коллекцию:
Использование такого типа коллекций может упростить работу с Entity Framework в приложениях, которые работают с привязкой данных, например, в приложениях WPF.
Хочу поделиться своим костылем в решении довольно банальной проблемы: как подружить полнотекстовый поиск 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 инъекций. Значение этой переменной передается как есть в текст запроса. Если есть какие то идеи по этому поводу пишите в комментариях. Я же использовал обычное регулярное выражение которое просто убирает все символы отличных от букв и цифр.
Также нужно учитывать особенности построения выражений для полнотекстовых запросов. Параметр в функцию
или изменить функцию выборки данных
За более подробной информацией об особенностях создания запросов лучше обратиться к официальной документации.
Стандартное логирование с таким решением работает некорректно. Для этого был добавлен специальный логгер:
Если посмотреть на сформированный запрос к базе данных то он будет сформирован до обработки функциями автозамены.
В ходе тестирования я проверял и на более сложных запросах со множественными выборками из разных таблиц и здесь не возникло никаких проблем.
Читайте также: