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

День 2442. #ЗаметкиНаПолях
Используем Хранимые Процедуры в EF Core. Продолжение
Начало

2. Функция, возвращающая таблицу
Функции могут возвращать целые наборы результатов:

CREATE OR REPLACE FUNCTION 
 tt.customer_order_summary(customer_id uuid)
RETURNS TABLE (
  order_id uuid,
  created_at timestamptz,
  total numeric,
  currency text,
  qty numeric
)
LANGUAGE sql
AS $$
SELECT
 o.id, o.created_at, o.total, o.currency,
 COALESCE(SUM(oi.qty), 0) AS qty
FROM tt.orders o
LEFT JOIN tt.order_items oi ON oi.order_id = o.id
WHERE o.customer_id = customer_id
GROUP BY o.id, o.created_at, o.total, o.currency
ORDER BY o.created_at DESC
$$;

Эта функция объединяет заказы с их товарами, суммирует количество и возвращает несколько строк. Вы можете написать это на LINQ, но SQL понятнее, и вы можете протестировать его непосредственно в инструменте для работы с БД. Чтобы использовать её из C#, создайте DTO, соответствующий выходным данным функции:

public class OrderSummaryDto
{
  public Guid OrderId { get; set; }
  public DateTime CreatedAt { get; set; }
  public decimal Total { get; set; }
  public string Currency { get; set; }
  public int Quantity { get; set; }
}

Затем запросите функцию, как и любую другую таблицу:

var orders = 
 await dbContext.Database
  .SqlQuery<OrderSummaryDto>(
  $"""
  SELECT order_id AS OrderId, created_at AS CreatedAt, total AS Total, currency AS Currency, qty AS Quantity
FROM tt.customer_order_summary({customerId})
  """)
  .ToListAsync();

Ключевым моментом является сопоставление имён столбцов со свойствами DTO с помощью алиасов. EF Core обрабатывает всё остальное автоматически.

Это простой случай без объединений, но вы можете использовать этот шаблон и в более сложных запросах. Однако вам придётся вручную проецировать данные в DTO, поскольку EF Core не может преобразовать объединения в чистом SQL в графы сущностей. Обычно вы в любом случае возвращаете плоскую структуру из функций, а затем при необходимости преобразуете её в более сложные модели на C#.

Понимание функций и процедур PostgreSQL
PostgreSQL различает функции и процедуры:
- Функции предназначены для возврата значений. Они могут возвращать скалярные значения, таблицы или даже сложные JSON-объекты. Они вызываются внутри транзакции с помощью SELECT и могут использоваться в запросах, предложениях WHERE, объединениях и других контекстах запросов.
- Процедуры предназначены для побочных эффектов. Они не возвращают значения напрямую, но могут изменять данные и иметь OUT-параметры. Вызываются с помощью CALL и идеально подходят для сложных операций, требующих явного управления транзакциями или выполнения нескольких связанных обновлений.

Используйте функции, когда нужно вернуть данные, используйте процедуры, когда нужно что-то изменить.

Окончание следует…

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