Raw SQL, FromSql, ExecuteSql — когда уходить от LINQ?

string term = request.Search;                       // пользовательский ввод

var products = await db.Products
    .FromSql($"""
        SELECT * FROM products
        WHERE to_tsvector('russian', name || ' ' || description) @@ plainto_tsquery('russian', {term})
        """)
    .Where(p => p.IsActive)                          // LINQ поверх SQL
    .OrderBy(p => p.Price)
    .Take(20)
    .AsNoTracking()
    .ToListAsync(ct);

SELECT p.* FROM (
    SELECT * FROM products
    WHERE to_tsvector('russian', name || ' ' || description) @@ plainto_tsquery('russian', @p0)
) AS p
WHERE p.is_active
ORDER BY p.price
LIMIT @p1

// БЕЗОПАСНО: интерполированная строка FormattableString → {term} станет параметром @p0
db.Products.FromSql($"SELECT * FROM products WHERE name = {term}");

// БЕЗОПАСНО: Raw с явными параметрами
db.Products.FromSqlRaw("SELECT * FROM products WHERE name = {0}", term);
db.Products.FromSqlRaw("SELECT * FROM products WHERE name = @name", new NpgsqlParameter("name", term));

// ОПАСНО: строка собрана заранее — интерполяция уже выполнена, EF получает готовый текст
var sql = $"SELECT * FROM products WHERE name = '{term}'";
db.Products.FromSqlRaw(sql);   // term = "x' OR 1=1; DROP TABLE products; --"

public sealed record SalesByDay(DateOnly Day, decimal Revenue, int Orders);

var stats = await db.Database.SqlQuery<SalesByDay>($"""
    SELECT date_trunc('day', created_at)::date AS "Day",
           SUM(total) AS "Revenue",
           COUNT(*)   AS "Orders"
    FROM orders
    WHERE created_at >= {from}
    GROUP BY 1
    ORDER BY 1
    """).ToListAsync(ct);

int count = await db.Database.SqlQuery<int>($"SELECT COUNT(*) AS \"Value\" FROM orders").SingleAsync(ct);

// Произвольная команда
int affected = await db.Database.ExecuteSqlAsync($"CALL recalc_customer_stats({customerId})", ct);

// EF Core 7+: массовые операции LINQ — без загрузки сущностей в память
await db.Sessions.Where(s => s.ExpiresAt < DateTime.UtcNow).ExecuteDeleteAsync(ct);

await db.Products
    .Where(p => p.CategoryId == categoryId)
    .ExecuteUpdateAsync(s => s
        .SetProperty(p => p.Price, p => p.Price * 1.1m)
        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow), ct);

public sealed class OrderReadQueries(ShopDbContext db)
{
    public async Task<IReadOnlyList<OrderSummary>> TopCustomersAsync(DateTime from, CancellationToken ct)
    {
        var conn = db.Database.GetDbConnection();              // то же соединение и транзакция EF
        const string sql = """
            SELECT c.id, c.name, SUM(o.total) AS total,
                   RANK() OVER (ORDER BY SUM(o.total) DESC) AS rank
            FROM customers c JOIN orders o ON o.customer_id = c.id
            WHERE o.created_at >= @from
            GROUP BY c.id, c.name
            LIMIT 50
            """;
        var rows = await conn.QueryAsync<OrderSummary>(new CommandDefinition(sql, new { from }, cancellationToken: ct));
        return rows.AsList();
    }
}