kostyl.dev
Ef Core

10.10. Raw SQL та Stored Procedures

10.10. Raw SQL та Stored Procedures

Вступ: Коли LINQ недостатньо

LINQ to Entities покриває більшість запитів, але є сценарії, де потрібний чистий SQL: складні аналітичні запити, виклик stored procedures, оптимізований SQL з hints, використання database-specific функцій. EF Core надає кілька способів виконання Raw SQL, зберігаючи переваги маппінгу та параметризації.

Передумови: 10.5. LINQ to Entities, знання SQL.

FromSqlRaw та FromSqlInterpolated

FromSqlInterpolated (рекомендований — безпечний)

using var context = new LibraryContext();

string author = "Мартін";
int minYear = 2000;

// Інтерполяція автоматично створює SqlParameter
var books = context.Books
    .FromSqlInterpolated($@"
        SELECT * FROM Books
        WHERE Author LIKE {'%' + author + '%'}
        AND Year >= {minYear}")
    .ToList();
// SQL: SELECT * FROM Books WHERE Author LIKE @p0 AND Year >= @p1
// Параметри: @p0 = '%Мартін%', @p1 = 2000
FromSqlInterpolatedавтоматично перетворює {value} на SqlParameter. Це безпечно від SQL Injection, незважаючи на те, що виглядає як рядкова інтерполяція!

FromSqlRaw (для динамічного SQL)

// Коли потрібен динамічний SQL
string tableName = "Books"; // УВАГА: не від користувача!
var books = context.Books
    .FromSqlRaw($"SELECT * FROM {tableName} ORDER BY Title")
    .ToList();

// З ручними параметрами
var books2 = context.Books
    .FromSqlRaw(
        "SELECT * FROM Books WHERE Year BETWEEN @start AND @end",
        new SqlParameter("@start", 2000),
        new SqlParameter("@end", 2024))
    .ToList();
FromSqlRaw з рядковою інтерполяцією НЕ створює параметри — це пряма конкатенація і потенційна SQL Injection! Використовуйте FromSqlInterpolated або явні SqlParameter.

Composable запити

Запити FromSql можна комбінувати з LINQ:

// FromSql + LINQ = комбінований запит
var result = context.Books
    .FromSqlInterpolated($"SELECT * FROM Books WHERE Year > {2020}")
    .Where(b => b.IsAvailable)           // Додає AND IsAvailable = 1
    .OrderBy(b => b.Title)               // Додає ORDER BY
    .Select(b => new { b.Title, b.Year }) // Тільки потрібні стовпці
    .ToList();
// SQL: SELECT Title, Year FROM (SELECT * FROM Books WHERE Year > @p0) AS b
//      WHERE b.IsAvailable = 1 ORDER BY b.Title

ExecuteSqlRaw / ExecuteSqlInterpolated

Для операцій, що не повертають сутності (INSERT, UPDATE, DELETE):

// Масове оновлення
int affected = context.Database
    .ExecuteSqlInterpolated($@"
        UPDATE Books SET IsAvailable = {false}
        WHERE Year < {1990}");
Console.WriteLine($"Оновлено: {affected}");

// Виклик stored procedure
context.Database
    .ExecuteSqlInterpolated($"EXEC sp_ArchiveOldBooks @CutoffYear = {2000}");

Stored Procedures

Виклик stored procedure для читання

// Stored procedure повертає дані → FromSql
var books = context.Books
    .FromSqlInterpolated($"EXEC sp_GetBooksByAuthor @AuthorName = {"Мартін"}")
    .ToList();

Виклик з OUTPUT параметрами

var outputParam = new SqlParameter
{
    ParameterName = "@TotalCount",
    SqlDbType = SqlDbType.Int,
    Direction = ParameterDirection.Output
};

context.Database.ExecuteSqlRaw(
    "EXEC sp_GetBookStats @TotalCount = @TotalCount OUTPUT",
    outputParam);

int totalCount = (int)outputParam.Value!;

Keyless Entities (Views та Custom Queries)

Для SQL Views та запитів, що не мають Primary Key:

// Keyless entity
[Keyless]
public class BookStatistics
{
    public string Author { get; set; } = "";
    public int BookCount { get; set; }
    public int AvgYear { get; set; }
}

// DbContext
public DbSet<BookStatistics> BookStatistics => Set<BookStatistics>();

// Конфігурація
modelBuilder.Entity<BookStatistics>(entity =>
{
    entity.HasNoKey();
    entity.ToView("vw_BookStatistics"); // Маппінг на View
});

// Використання
var stats = context.BookStatistics.ToList();

SqlQuery<T> (EF Core 8+)

Для запитів, що повертають прості типи:

// Скалярний запит
var years = context.Database
    .SqlQuery<int>($"SELECT DISTINCT Year AS Value FROM Books ORDER BY Year")
    .ToList();

// Composable
var recentYears = context.Database
    .SqlQuery<int>($"SELECT DISTINCT Year AS Value FROM Books")
    .Where(y => y > 2010)
    .ToList();

Коли використовувати Raw SQL

СценарійLINQRaw SQL
Простий CRUD✅❌
WHERE + ORDER BY✅❌
GROUP BY + агрегація✅❌
Складні JOIN (5+ таблиць)⚠️✅
Window Functions (ROW_NUMBER)❌✅
PIVOT/UNPIVOT❌✅
Full-Text Search❌✅
Stored Procedures❌✅
Views✅ (Keyless)✅
Bulk UPDATE/DELETE✅ (EF7+)✅

Практичні завдання

Завдання 1: FromSql + LINQ

  1. Напишіть Raw SQL запит для пошуку книг за автором.
  2. Додайте LINQ .Where() та .OrderBy() поверх Raw SQL.
  3. Перевірте згенерований SQL (composable query).

Завдання 2: Stored Procedure

  1. Створіть SP sp_GetBooksByYear з параметром @Year.
  2. Викличте з EF Core через FromSql.
  3. Додайте OUTPUT параметр для загальної кількості.

Завдання 3: Keyless Entity + View

  1. Створіть SQL View vw_AuthorStats (Author, BookCount, AvgYear, LatestBook).
  2. Keyless entity AuthorStatView.
  3. Запит через context.Set<AuthorStatView>().ToList().

Резюме

FromSqlInterpolated

Безпечний Raw SQL з автоматичною параметризацією. Composable з LINQ.

ExecuteSql

INSERT/UPDATE/DELETE без повернення сутностей. Для масових операцій та SP.

Keyless Entities

HasNoKey() для Views та custom queries. Тільки read-only, без Change Tracking.

SqlQuery<T>

EF Core 8+. Запити з простими типами (int, string). Composable з LINQ.

10.9. Наслідування (Inheritance Mapping)

10.11. Продуктивність та оптимізація

Copyright © 2026