Запросы SQL

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

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

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

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

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

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

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

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

var user = "johndoe";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

var searchTerm = "Lorem ipsum";

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

Приведенный выше запрос создает следующий 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 = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToListAsync();

Создание с помощью 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 = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToListAsync();

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

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

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

var ids = await context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToListAsync();

var ids = await context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToListAsync();

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

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

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

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

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

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 для запросов.

Ограничения

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

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