.NET Разработчик: “День 2441. #ЗаметкиНаПолях Используем Хранимые Процедуры в EF Core. На…”

День 2441. #ЗаметкиНаПолях
Используем Хранимые Процедуры в EF Core. Начало
Необходимость в хранимых процедурах в БД может возникнуть по разным причинам:
- сложный отчёт, объединяющий несколько таблиц с помощью агрегаций и оконных функций, когда LINQ-запрос генерирует неоптимальный SQL;
- нужно обновить таблицу, используя правильные блокировки, чтобы предотвратить состояние гонки.

В разных источниках об использовании хранимых процедур в EF Core можно встретить противоречивые советы: от «избегать использования чистого SQL любой ценой» до «полностью отказаться от EF и использовать ADO.NET». Ни то, ни другое не кажется правильным. EF Core отлично работает с функциями и процедурами БД.

В примерах далее будем использовать PostgreSQL, но те же принципы применимы и к другим реляционным БД.

Когда стоит использовать чистый SQL?
В большинстве случаев LINQ вполне подходит. EF Core преобразует ваш код C# в качественный SQL, а вы получаете типобезопасность и поддержку рефакторинга. Но есть исключения:
1. Нужна производительность, которую невозможно получить от LINQ. Сложные агрегации с несколькими соединениями, оконными функциями или запросы к отчётам часто выполняются быстрее, если написаны непосредственно на SQL. Вы можете протестировать и настроить запрос в инструменте работы с БД, прежде чем добавлять его в код.
2. Специфичные для БД функции. В PostgreSQL есть полнотекстовый поиск запросы к JSON и общие табличные выражения (CTE), которые не всегда имеют понятные эквиваленты в LINQ.
3. Есть существующая логика в БД (хранимые процедуры и функции, например, из унаследованной системы). Их прямой вызов лучше, чем переписывание всего на C#.
4. Нужны атомарные операции с корректной блокировкой. Хранимая процедура, которая координирует несколько обновлений с помощью блокировок FOR UPDATE, проще и безопаснее, чем пытаться управлять этим из кода приложения.
5. Нужно сократить количество запросов. Один вызов функции, агрегирующий данные из пяти таблиц, эффективнее пяти отдельных LINQ-запросов.

Рассмотрим, как это сделать.

1. Простая скалярная функция
Вот простая функция, которая показывает, сколько билетов осталось:

CREATE OR REPLACE FUNCTION 
 tt.tickets_left(ticket_type_id uuid)
RETURNS numeric LANGUAGE sql
AS $$
  SELECT tt.available_quantity
  FROM ticketing.ticket_types tt
  WHERE tt.id = ticket_type_id
$$;

Ничего необычного, просто запрос, обёрнутый в функцию. Вызвать её в EF Core просто:

var result = await dbContext.Database.SqlQuery<int>(
 $"""
  SELECT tt.tickets_left({ticketTypeId}) AS "Value"
 """)
 .FirstAsync();

Обратите внимание на алиас AS "Value". Когда EF Core сопоставляется с примитивным типом, он ожидает свойство с именем Value. Кавычки сохраняют точный регистр (PostgreSQL по умолчанию преобразует идентификаторы без кавычек в нижний регистр).

Синтаксис интерполированной строки ($"{ticketTypeId}") может показаться опасным и подверженным SQL-инъекциям, но EF Core автоматически преобразует его в параметризованный запрос. Это всего лишь удобный способ написания параметризованных запросов. Причина в том, что мы передаём методу SqlQuery не строку, а FormattableString. Это специальный тип, который сохраняет формат и аргументы отдельно, позволяя EF Core обрабатывать параметры.

Продолжение следует…

Источник: https://www.milanjovanovic.tech/blog/using-stored-procedures-and-functions-with-ef-core-and-postgresql