Запросы SQL

  • Мақала

Entity Framework Core позволяет удалять запросы SQL при работе с реляционной базой данных. Запросы SQL полезны, если нужный запрос не может быть выражен с помощью LINQ или если запрос LINQ приводит к тому, что EF создает неэффективный SQL. Запросы SQL могут возвращать обычные типы сущностей или типы сущностей без ключей, которые являются частью модели.

Совет

Вы можете скачать используемый в этой статье пример из репозитория GitHub.

Базовые запросы SQL

Можно использовать FromSql для запуска запроса LINQ на основе SQL-запроса:

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

Примечание.

FromSql представлен в EF Core 7.0. При использовании более старых версий используйте FromSqlInterpolated вместо этого.

Запросы SQL можно использовать для выполнения хранимой процедуры, которая возвращает данные сущности:

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Примечание.

FromSql можно использовать только непосредственно на объекте DbSet. Его нельзя создать по произвольному запросу LINQ.

Передача параметров

Предупреждение

Внимательно обратите внимание на параметризацию при использовании SQL-запросов

При вводе значений, предоставленных пользователем, в SQL-запросе необходимо принять меры, чтобы избежать атак внедрения SQL. Внедрение SQL происходит, когда программа интегрирует предоставленное пользователем строковое значение в SQL-запрос, а предоставленное пользователем значение создается для завершения строки и выполнения другой вредоносной операции SQL. Дополнительные сведения об внедрении SQL см. на этой странице.

FromSqlInterpolated Методы FromSql безопасны для внедрения SQL и всегда интегрируют данные параметров в качестве отдельного параметра SQL. Однако метод FromSqlRaw может быть уязвим к атакам внедрения SQL, если он некорректно используется. Дополнительные сведения см. ниже.

В следующем примере передается один параметр хранимой процедуре, включая заполнитель параметра в строку запроса SQL и предоставляя дополнительный аргумент:

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Хотя этот синтаксис может выглядеть как обычная интерполяция строк C#, указанное значение упаковывается в DbParameter имя созданного параметра, вставленное в место, где {0} был указан заполнитель. Это делает FromSql безопасной от атак внедрения SQL и отправляет значение эффективно и правильно в базу данных.

При выполнении хранимых процедур можно использовать именованные параметры в строке запроса SQL, особенно если хранимая процедура имеет необязательные параметры:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

Если вам нужен дополнительный контроль над отправленным параметром базы данных, можно также создать DbParameter и указать его в качестве значения параметра. Это позволяет задать точный тип базы данных параметра или аспекты, такие как его размер, точность или длина:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Примечание.

Передаваемые параметры должны точно соответствовать определению хранимой процедуры. Обратите особое внимание на упорядочение параметров, заботясь о том, чтобы не пропустить или не указать какие-либо из них , или рассмотреть возможность использования именованных нотации параметров. Кроме того, убедитесь, что типы параметров соответствуют и что их аспекты (размер, точность, масштабирование) задаются по мере необходимости.

Динамический SQL и параметры

FromSql и ее параметризация должна использоваться везде, где это возможно. Однако существуют определенные сценарии, в которых SQL необходимо динамически объединять, а параметры базы данных нельзя использовать. Например, предположим, что переменная C# содержит имя свойства, который необходимо отфильтровать. Может потребоваться использовать SQL-запрос, например следующий:

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

Этот код не работает, так как базы данных не разрешают параметризацию имен столбцов (или любую другую часть схемы).

Во-первых, важно учитывать последствия динамического создания запроса через SQL или в противном случае. Принятие имени столбца от пользователя может позволить им выбрать столбец, который не индексирован, что делает запрос выполняться очень медленно и перегружать базу данных; или может позволить им выбрать столбец, содержащий данные, которые не нужны. За исключением действительно динамических сценариев, обычно лучше иметь два запроса для двух имен столбцов, а не использовать параметризацию для сворачивания их в один запрос.

Если вы решили, что вы хотите динамически создавать SQL, вам потребуется использовать FromSqlRawего, что позволяет интерполировать данные переменной непосредственно в строку SQL, а не использовать параметр базы данных:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

В приведенном выше коде имя столбца вставляется непосредственно в SQL с помощью интерполяции строк C#. Это ваша ответственность, чтобы убедиться, что это строковое значение безопасно, санируя его, если оно поступает из небезопасного источника; Это означает, что обнаружение специальных символов, таких как запятая, комментарии и другие конструкции SQL, а также их правильное удаление или отклонение таких входных данных.

С другой стороны, значение столбца отправляется через и DbParameterпоэтому безопасно в случае внедрения SQL.

Предупреждение

Будьте очень осторожны при использовании FromSqlRaw, и всегда убедитесь, что значения находятся либо из безопасного источника, либо правильно санируются. Атаки на внедрение SQL могут иметь катастрофические последствия для приложения.

Создание с помощью LINQ

Вы можете создать на вершине исходного SQL-запроса с помощью операторов LINQ; EF Core будет рассматривать SQL как вложенный запрос и создавать над ним в базе данных. В следующем примере используется SQL-запрос, который выбирается из функции с табличным значением (TVF). Затем запрос выполняет составление на ее основе с использованием LINQ для фильтрации и сортировки.

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

Приведенный выше запрос создает следующий SQL:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

Оператор Include можно использовать для загрузки связанных данных так же, как и с любым другим запросом LINQ:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

Создание с помощью LINQ требует, чтобы ваш SQL-запрос был компонуемым, так как EF Core будет рассматривать предоставленный SQL как вложенный запрос. Компонуемые запросы SQL обычно начинаются с SELECT ключевое слово и не могут содержать функции SQL, недопустимые в вложенных запросах, например:

  • конечная точка с запятой;
  • на сервере SQL Server конечное указание на уровне запроса (например, OPTION (HASH JOIN));
  • на сервере SQL Server предложение ORDER BY, которое не используется с OFFSET 0 или TOP 100 PERCENT в предложении SELECT.

SQL Server не допускает составления вызовов хранимых процедур, поэтому любая попытка применить дополнительные операторы запроса к такому вызову приведет к формированию недопустимого SQL. Используйте AsEnumerable или AsAsyncEnumerable сразу после FromSql или FromSqlRaw убедитесь, что EF Core не пытается создать хранимую процедуру.

Отслеживание изменений

Запросы, использующие FromSql или следуйте тем же правилам отслеживания изменений, что и FromSqlRaw любой другой запрос LINQ в EF Core. Например, если типы сущностей проектов запросов, результаты отслеживаются по умолчанию.

В следующем примере используется SQL-запрос, который выбирается из функции с табличным значением (TVF), а затем отключает отслеживание изменений с вызовом AsNoTracking:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

Запрос скалярных типов (не сущностей)

Примечание.

Эта функция появилась в EF Core 7.0.

Хотя FromSql это полезно для запросов сущностей, определенных в модели, SqlQuery позволяет легко запрашивать скалярные, не являющиеся сущностями через SQL, без необходимости раскрываться до API доступа к данным нижнего уровня. Например, следующий запрос извлекает все идентификаторы из Blogs таблицы:

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

Вы также можете создавать операторы LINQ по запросу SQL. Тем не менее, так как SQL становится вложенным запросом, выходной столбец которого необходимо ссылаться на добавленный SQL EF, необходимо указать выходной столбец Value. Например, следующий запрос возвращает идентификаторы, которые превышают среднее значение идентификатора:

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql можно использовать с любым скалярным типом, поддерживаемым поставщиком базы данных. Если вы хотите использовать тип, не поддерживаемый поставщиком базы данных, можно использовать конфигурацию предварительного соглашения для определения преобразования значений.

SqlQueryRaw позволяет выполнять динамическое построение запросов SQL так же, как FromSqlRaw и для типов сущностей.

Выполнение не запрашивающих SQL

В некоторых сценариях может потребоваться выполнить SQL, который не возвращает никакие данные, обычно для изменения данных в базе данных или вызова хранимой процедуры, которая не возвращает результирующие наборы. Это можно сделать с помощью ExecuteSql:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Это выполняет предоставленный SQL и возвращает количество измененных строк. ExecuteSql защищает от внедрения SQL с помощью безопасной параметризации, как FromSqlи ExecuteSqlRaw позволяет динамическое построение запросов SQL, как FromSqlRaw и для запросов.

Примечание.

До EF Core 7.0 иногда необходимо было использовать ExecuteSql API для выполнения массового обновления в базе данных, как описано выше. Это значительно эффективнее, чем запросы ко всем соответствующим строкам, а затем их изменение.SaveChanges EF Core 7.0 представила ExecuteUpdate и ExecuteDelete, что позволило выразить эффективные операции массового обновления через LINQ. Рекомендуется использовать эти API по возможности, а не ExecuteSql.

Ограничения

При возврате типов сущностей из запросов SQL следует учитывать несколько ограничений.

  • SQL-запрос должен возвращать данные для всех свойств типа сущности.
  • Имена столбцов в результирующем наборе должны совпадать с именами столбцов, с которыми сопоставляются свойства. Обратите внимание, что это поведение отличается от EF6; Ef6 игнорирует сопоставление свойств и столбцов для запросов SQL, а имена столбцов результирующего набора должны соответствовать этим именам свойств.
  • SQL-запрос не может содержать связанные данные. Однако во многих случаях вы можете использовать метод compose поверх запроса с помощью оператора Include для возврата связанных данных (см. раздел Включение связанных данных).