Ef Core

Raw SQL — Stored Procedures, DbFunction та Bulk Operations (Частина 2)

Stored Procedures у EF Core — виклик з і без результату, OUTPUT параметри. HasDbFunction для маппінгу SQL-функцій у LINQ. ExecuteUpdate та ExecuteDelete (EF Core 7+) для ефективних bulk операцій без завантаження в пам'ять.

Raw SQL: Stored Procedures, DbFunction та Bulk Operations

Це продовження статті «Raw SQL, Views та Stored Procedures». Читайте послідовно.

Stored Procedures: виклик через EF Core

Stored Procedure (збережена процедура) — компільований SQL-код, збережений у базі. EF Core підтримує виклик SP через різні підходи залежно від того, що SP повертає.

SP що повертає рядки

Якщо SP повертає результат (SELECT), використовуємо FromSqlRaw:

-- Stored Procedure у SQL Server
CREATE PROCEDURE [dbo].[GetProductsByCategory]
    @CategoryId  INT,
    @MinPrice    DECIMAL(12,2) = NULL,
    @MaxPrice    DECIMAL(12,2) = NULL
AS
BEGIN
    SELECT p.Id, p.Name, p.Price, p.Stock, p.CategoryId, p.IsActive
    FROM Products p
    WHERE p.CategoryId = @CategoryId
      AND p.IsActive = 1
      AND (@MinPrice IS NULL OR p.Price >= @MinPrice)
      AND (@MaxPrice IS NULL OR p.Price <= @MaxPrice)
    ORDER BY p.Price
END

// Виклик через FromSqlRaw з SqlParameter (SQL Server)
using Microsoft.Data.SqlClient;

var products = await context.Products
    .FromSqlRaw(
        "EXEC [dbo].[GetProductsByCategory] @CategoryId, @MinPrice, @MaxPrice",
        new SqlParameter("@CategoryId", categoryId),
        new SqlParameter("@MinPrice",   (object?)minPrice ?? DBNull.Value),
        new SqlParameter("@MaxPrice",   (object?)maxPrice ?? DBNull.Value)
    )
    .AsNoTracking()
    .ToListAsync();

FromSqlRaw + Stored Procedure: результат SP маппиться на entity клас — стовпці результату мають відповідати полям entity. Якщо SP повертає підмножину полів або кастомний формат — використовуйте SqlQueryRaw<T> з власним класом.

// Через SqlQueryRaw для кастомного результату
public class ProductWithSalesStats
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int TotalSold { get; set; }
    public decimal Revenue { get; set; }
}

var statsResults = await context.Database
    .SqlQueryRaw<ProductWithSalesStats>(
        "EXEC [dbo].[GetProductSalesStats] @StartDate, @EndDate",
        new SqlParameter("@StartDate", startDate),
        new SqlParameter("@EndDate",   endDate)
    )
    .ToListAsync();

PostgreSQL Functions (аналог SP)

PostgreSQL по-різному: для повернення рядків — TABLE функції, не PROCEDURE:

-- PostgreSQL: FUNCTION що повертає таблицю
CREATE OR REPLACE FUNCTION get_products_by_category(
    p_category_id  INT,
    p_min_price    NUMERIC DEFAULT NULL,
    p_max_price    NUMERIC DEFAULT NULL
) RETURNS TABLE (
    id         INT,
    name       VARCHAR,
    price      NUMERIC,
    is_active  BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.price, p.is_active
    FROM products p
    WHERE p.category_id = p_category_id
      AND p.is_active = true
      AND (p_min_price IS NULL OR p.price >= p_min_price)
      AND (p_max_price IS NULL OR p.price <= p_max_price);
END;
$$ LANGUAGE plpgsql;

// Виклик PostgreSQL TABLE функції
var products = await context.Products
    .FromSqlInterpolated(
        $"SELECT * FROM get_products_by_category({categoryId}, {minPrice}, {maxPrice})")
    .AsNoTracking()
    .ToListAsync();

SP без результату (OUTPUT parameters)

Для SP що виконує дію і повертає результат через OUTPUT параметри:

CREATE PROCEDURE [dbo].[CreateOrder]
    @CustomerId   INT,
    @TotalAmount  DECIMAL(12,2),
    @OrderId      INT OUTPUT,
    @OrderNumber  NVARCHAR(20) OUTPUT
AS
BEGIN
    -- Генеруємо OrderNumber
    SET @OrderNumber = 'ORD-' + FORMAT(GETUTCDATE(), 'yyyyMMdd') + '-' +
                       RIGHT('000000' + CAST(NEXT VALUE FOR order_seq AS VARCHAR), 6)

    INSERT INTO Orders (CustomerId, TotalAmount, OrderNumber, Status, PlacedAt)
    VALUES (@CustomerId, @TotalAmount, @OrderNumber, 'Pending', GETUTCDATE())

    SET @OrderId = SCOPE_IDENTITY()
END

// OUTPUT параметри через SqlParameter з direction
var orderIdParam = new SqlParameter("@OrderId", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};
var orderNumberParam = new SqlParameter("@OrderNumber", SqlDbType.NVarChar, 20)
{
    Direction = ParameterDirection.Output
};

await context.Database.ExecuteSqlRawAsync(
    "EXEC [dbo].[CreateOrder] @CustomerId, @TotalAmount, @OrderId OUTPUT, @OrderNumber OUTPUT",
    new SqlParameter("@CustomerId",  customerId),
    new SqlParameter("@TotalAmount", totalAmount),
    orderIdParam,
    orderNumberParam
);

// Зчитуємо результати OUTPUT параметрів
int createdOrderId     = (int)(orderIdParam.Value!);
string createdOrderNumber = (string)(orderNumberParam.Value!);

Console.WriteLine($"Created Order #{createdOrderNumber} (Id={createdOrderId})");

SP через DbCommand напряму

Для складних сценаріїв — відкрити з'єднання і виконати напряму:

// Прямий DbCommand через EF Core connection
var connection = context.Database.GetDbConnection();
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "[dbo].[ComplexBusinessOperation]";
command.CommandType = CommandType.StoredProcedure;
command.Transaction = context.Database.CurrentTransaction?.GetDbTransaction();

command.Parameters.Add(new SqlParameter("@Input",  inputValue));

var outputParam = new SqlParameter("@Output", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};
command.Parameters.Add(outputParam);

await command.ExecuteNonQueryAsync();

int result = (int)outputParam.Value!;

DbFunction: маппінг SQL-функцій у LINQ

HasDbFunction дозволяє пов'язати C# метод зі SQL-функцією бази даних. Після маппінгу — метод можна використовувати у LINQ-виразах і він транслюватиметься у відповідний SQL.

Скалярна SQL функція

-- SQL Server: власна функція для форматування ціни
CREATE FUNCTION [dbo].[FormatPrice](@Price DECIMAL(12,2), @CurrencyCode NVARCHAR(3))
RETURNS NVARCHAR(50)
AS
BEGIN
    RETURN @CurrencyCode + ' ' + FORMAT(@Price, 'N2')
END

// Реєстрація у DbContext
public class AppDbContext : DbContext
{
    // C# метод-заглушка: назва і параметри мають відповідати SQL функції
    // Виклик поза LINQ → NotSupportedException
    public static string FormatPrice(decimal price, string currencyCode)
        => throw new NotSupportedException("Тільки для LINQ-виразів у EF Core");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(typeof(AppDbContext).GetMethod(nameof(FormatPrice))!)
                    .HasName("FormatPrice")
                    .HasSchema("dbo");

        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

// Використання у LINQ — транслюється у SQL
var products = await context.Products
    .Select(p => new
    {
        p.Id,
        p.Name,
        FormattedPrice = AppDbContext.FormatPrice(p.Price, "UAH") // ← SQL функція
    })
    .ToListAsync();
// SQL: SELECT Id, Name, [dbo].[FormatPrice](Price, N'UAH') AS FormattedPrice
//      FROM Products

Таблична SQL функція

-- SQL Server: таблична функція (TVF)
CREATE FUNCTION [dbo].[GetRecentOrdersByCustomer](
    @CustomerId INT,
    @DaysBack   INT
)
RETURNS TABLE AS RETURN
(
    SELECT o.Id, o.OrderNumber, o.TotalAmount, o.Status, o.PlacedAt
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.PlacedAt >= DATEADD(day, -@DaysBack, GETUTCDATE())
      AND o.IsDeleted = 0
)

// Keyless Entity для результату TVF
public class RecentOrder
{
    public int Id { get; set; }
    public string OrderNumber { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public string Status { get; set; } = string.Empty;
    public DateTime PlacedAt { get; set; }
}

public class AppDbContext : DbContext
{
    // IQueryable<T> метод для TVF
    public IQueryable<RecentOrder> GetRecentOrdersByCustomer(int customerId, int daysBack)
        => FromExpression(() => GetRecentOrdersByCustomer(customerId, daysBack));

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Keyless для результату TVF
        modelBuilder.Entity<RecentOrder>().HasNoKey();

        // Реєстрація TVF
        modelBuilder.HasDbFunction(
            typeof(AppDbContext).GetMethod(nameof(GetRecentOrdersByCustomer))!)
            .HasName("GetRecentOrdersByCustomer")
            .HasSchema("dbo");
    }
}

// Виклик TVF у LINQ
var recentOrders = await context
    .GetRecentOrdersByCustomer(customerId: 42, daysBack: 30)
    .Where(o => o.Status == "Delivered")
    .OrderByDescending(o => o.TotalAmount)
    .ToListAsync();
// SQL: SELECT * FROM [dbo].[GetRecentOrdersByCustomer](42, 30)
//      WHERE Status = 'Delivered'
//      ORDER BY TotalAmount DESC

ExecuteUpdate та ExecuteDelete (EF Core 7+)

EF Core 7 ввів два нових методи для bulk операцій без завантаження entity в пам'ять: ExecuteUpdateAsync і ExecuteDeleteAsync. Вони набагато ефективніші за завантаження → зміна → SaveChanges для великих наборів даних.

ExecuteDeleteAsync: видалення без завантаження

// Видалити всі застарілі записи (без завантаження в пам'ять)
int deleted = await context.AuditLogs
    .Where(log => log.CreatedAt < DateTime.UtcNow.AddYears(-2))
    .ExecuteDeleteAsync();
// SQL: DELETE FROM AuditLogs WHERE CreatedAt < @p0
// ЖОДНОГО SELECT! ЖОДНОГО Change Tracker!

// З додатковими умовами
int softDeleted = await context.Products
    .Where(p => p.CategoryId == oldCategoryId && p.Stock == 0)
    .ExecuteDeleteAsync();
// SQL: DELETE FROM Products WHERE CategoryId = @p0 AND Stock = 0

ExecuteUpdateAsync: оновлення без завантаження

// Підвищити ціну на 10% у всій категорії
int updated = await context.Products
    .Where(p => p.CategoryId == 5 && p.IsActive)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.Price,     p => p.Price * 1.10m)  // Price = Price * 1.10
        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow)        // UpdatedAt = NOW
    );
// SQL: UPDATE Products
//      SET Price = Price * 1.10, UpdatedAt = @p0
//      WHERE CategoryId = 5 AND IsActive = 1

// Bulk status update
int statusUpdated = await context.Orders
    .Where(o => o.Status == "Pending" &&
                o.PlacedAt < DateTime.UtcNow.AddDays(-7))
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(o => o.Status, "Cancelled")
        .SetProperty(o => o.CancelledAt, DateTime.UtcNow)
        .SetProperty(o => o.CancellationReason, "Auto-cancelled: no payment")
    );

ExecuteUpdate vs SaveChanges: коли що

// Individual update: кілька записів, потрібна бізнес-логіка
var orders = await context.Orders.Where(o => ...).ToListAsync();
foreach (var order in orders)
{
    order.Status = "Processing";
    order.ProcessingStartedAt = DateTime.UtcNow;
    _eventPublisher.Publish(new OrderStartedEvent(order.Id)); // Бізнес-подія!
}
await context.SaveChangesAsync();
// Краще коли: є Interceptors, Domain Events, Concurrency Tokens, аудит через CH

// Bulk update: сотні тисяч записів, проста зміна значення
await context.Products
    .Where(p => p.CategoryId == oldId)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.CategoryId, newId));
// Краще коли: великий обсяг, проста операція, без DA events, максимальна швидкість

	ExecuteUpdate/Delete	SaveChanges (Load+Modify)
SQL	Один UPDATE/DELETE	N SELECT + N UPDATE
Change Tracker	Не використовує	Використовує
Interceptors	❌ Не викликаються	✅ Викликаються
Domain Events	❌ Не спрацьовують	✅ Спрацьовують
Concurrency Token	❌ Не перевіряється	✅ Перевіряється
Для великих наборів	⭐⭐⭐ Ідеально	⭐ Повільно
Для бізнес-логіки	❌	✅

Soft Delete через ExecuteUpdate

// Soft Delete для великого набору без завантаження
public async Task SoftDeleteCategoryProductsAsync(int categoryId, string deletedBy)
{
    await context.Products
        .Where(p => p.CategoryId == categoryId && !p.IsDeleted)
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(p => p.IsDeleted,  true)
            .SetProperty(p => p.DeletedAt,  DateTime.UtcNow)
            .SetProperty(p => p.DeletedBy,  deletedBy)
        );
}
// SQL: UPDATE Products
//      SET IsDeleted=1, DeletedAt=@p0, DeletedBy=@p1
//      WHERE CategoryId=@p2 AND IsDeleted=0

Практичні завдання (Частина 2)

Рівень 1 — Базовий

Завдання 1.1: Stored Procedure з OUTPUT параметром

Напишіть SP CreateCustomer(@Name, @Email, @CustomerId OUTPUT, @CustomerNumber OUTPUT) що:

Генерує CustomerNumber як CUS-{YYYYMM}-{6-digit-seq}
Вставляє Customer
Повертає Id і Number через OUTPUT

Викличте з EF Core через ExecuteSqlRaw і SqlParameter з Direction = Output. Перевірте значення OUTPUT параметрів.

Завдання 1.2: DbFunction для розрахунку

Реалізуйте SQL функцію CalculateDiscount(@Price DECIMAL, @CustomerLevel INT) що повертає суму знижки залежно від рівня клієнта (Bronze=5%, Silver=10%, Gold=15%). Зареєструйте через HasDbFunction. Використайте у LINQ-запиті для кожного продукту.

Завдання 1.3: ExecuteDeleteAsync для архівування

Реалізуйте ArchiveOldSessionsAsync(int daysOld):

Через ExecuteDeleteAsync — видалити сесії старіші N днів
Поверніть кількість видалених
Порівняйте з варіантом через ToListAsync() + RemoveRange() + SaveChangesAsync() — яка різниця у SQL?

Рівень 2 — Логіка

Завдання 2.1: TVF для ієрархічного пошуку

Реалізуйте SQL TVF GetProductsInCategoryTree(@RootCategoryId INT) що через рекурсивний CTE повертає всі продукти з категорії і всіх її підкатегорій (будь-який рівень вкладеності). Зареєструйте через HasDbFunction і використайте у LINQ.

Завдання 2.2: Bulk Update з умовами

Реалізуйте метод AdjustInventoryAsync(Dictionary<int, int> productQuantityChanges):

// Для кожного ProductId змінити Stock на дельту
// ProductId=1: +5, ProductId=2: -3, ProductId=5: +10

Розгляньте варіанти:

Завантажити всі продукти → foreach → SaveChanges (N SELECT + N UPDATE)
Один ExecuteUpdateAsync (можливо? як?)
ExecuteSqlRaw з VALUES і MERGE/JOIN UPDATE

Реалізуйте варіант 3. Оцініть складність і безпечність.

Рівень 3 — Архітектура

Завдання 3.1: Database Function Registry

Реалізуйте generic DbFunctionRegistry що:

Сканує Assembly в пошуку методів з кастомним атрибутом [DbFunction(name, schema)]
Автоматично реєструє їх через HasDbFunction у OnModelCreating
Документує зареєстровані функції через ILogger

[DbFunction("GetFullName", "dbo")]
public static string GetFullName(string firstName, string lastName)
    => throw new NotSupportedException();

Реалізуйте, зареєструйте у DI і напишіть тест що перевіряє трансляцію функції у SQL.

Підсумок статті 18

Ця стаття розкрила всі аспекти Raw SQL у EF Core:

Частина 1:

FromSqlRaw/FromSqlInterpolated: SQL з маппінгом на entity. Параметризація обов'язкова. LINQ можна додавати зверху.
SqlQueryRaw<T>: довільний тип результату, не entity. Для звітів, агрегацій.
ExecuteSqlRaw: DDL, DML без результату. Повертає affected rows.
Database Views: HasNoKey() + ToView(). Міграція через migrationBuilder.Sql().
CTE і Window Functions: через SqlQueryRaw<T> для аналітичних запитів.

Частина 2:

Stored Procedures: FromSqlRaw для SP з результатом, ExecuteSqlRaw для SP без, OUTPUT параметри через SqlParameter(Direction.Output).
HasDbFunction: маппінг C# методу на SQL-функцію. Скалярні і табличні (TVF) функції. Транслюються у LINQ.
ExecuteUpdateAsync: bulk UPDATE одним SQL без завантаження. SetProperty ланцюжок.
ExecuteDeleteAsync: bulk DELETE одним SQL. Ideal для архівування, cleanup.
Порівняльна таблиця: ExecuteUpdate vs SaveChanges — коли що обирати.

Наступна стаття — Advanced Queries: Compiled Queries, Split Queries та Bulk (стаття 19) — дослідить Compiled Queries, Bulk Insert Extension, Pagination patterns та Query Optimization Tips.

Додаткові ресурси

Edit this pageorReport an issue

Raw SQL, Views та Stored Procedures (Частина 1)

FromSqlRaw і FromSqlInterpolated в EF Core — виконання сирого SQL зі збереженням усіх переваг ORM. Маппінг Database Views, Keyless Entity Types, параметризація запитів і захист від SQL Injection.

Продвинуті Запити — Compiled Queries, Bulk та Оптимізація (Частина 1)

Compiled Queries для повторюваних запитів, асинхронне перерахування через IAsyncEnumerable, Keyset Pagination замість OFFSET, проєкція vs Include — порівняння підходів і інструменти діагностики продуктивності.