День 1539. #ЗаметкиНаПолях
Чистые SQL-запросы в EF Core
В EF7 появилась поддержка возврата скалярных типов с помощью SQL-запросов. В EF8 появилась поддержка запросов несопоставленных (unmapped) типов с помощью чистого SQL. Это то, что Dapper предлагает по умолчанию, и приятно видеть, что EF Core догоняет его.

EF Core и SQL
В EF8 чистые SQL-запросы могут возвращать любой тип без необходимости включать его в модель EF. Вы можете запрашивать несопоставленные типы с помощью методов SqlQuery и SqlQueryRaw. Метод SqlQuery использует интерполяцию строк для параметризации запроса, защищая от атак SQL-инъекцией.

Вот пример запроса, возвращающего список OrderSummary:

var start = new DateOnly(2023, 1, 1);
var orders = await dbContext
  .Database
  .SqlQuery<Order>(
   @$"SELECT * FROM Orders 
      AS o WHERE o.Created >= {start}")
  .ToListAsync();

В БД будет отправлен следующий запрос:

SELECT * FROM Orders AS o WHERE o.Created >= @p0

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

SQL + LINQ
Интересной особенностью SqlQuery является то, что он возвращает IQueryable, который можно дополнить с помощью LINQ. Можно добавить оператор Where после вызова SqlQuery:

var orders = await dbContext
  .Database
  .SqlQuery<Order>(
    "SELECT * FROM Orders AS o")
  .Where(o => o.Created >= start)
  .ToListAsync();

Однако, в результате получится неоптимальный запрос:

SELECT s.Id, s.CustomerId, s.Price, s.Created
FROM (
  SELECT * FROM Orders AS o
) AS s
WHERE s.Created >= @p0

Также возможно добавить OrderBy, Skip и Take:

var orders = await dbContext
  .Database
  .SqlQuery<Order>(
    @$"SELECT * FROM Orders AS o 
      WHERE o.Created >= {start}")
    .OrderBy(o => o.Id)
    .Skip(10)
    .Take(5)
    .ToListAsync();

Получим следующий запрос:

SELECT s.Id, s.CustomerId, s.Price, s.Created
FROM (
  SELECT * FROM Orders AS o WHERE o.Created >= @p0
) AS s
ORDER BY s.Id
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY

Производительность аналогична LINQ-запросам с проекцией с помощью Select.

SQL-запросы для модификации данных
Если вы хотите изменить данные в базе данных с помощью SQL, вы обычно пишете запрос, который не возвращает результата. Запрос может быть UPDATE, DELETE или вызовом хранимой процедуры. Для этого можно использовать метод ExecuteSql:

dbContext.Database.ExecuteSql(
  @$"UPDATE Orders SET Status = 5 
     WHERE Created >= {start}");

ExecuteSql также защищает от SQL-инъекций путем параметризации аргументов, как и SqlQuery. В EF7 можно написать приведённый выше запрос с помощью LINQ и метода ExecuteUpdate. Также есть метод ExecuteDelete для удаления записей.

Источник: https://www.milanjovanovic.tech/blog/ef-core-raw-sql-queries