1// EF Core 9 — Raw SQL, Stored Procedures, Dapper
2
3// FromSqlRaw — materialized to tracked entities
4// ✅ Safe: SQL is fixed, params are parameterized
5var orders = await _dbContext.Orders
6    .FromSqlRaw(
7        "SELECT * FROM Orders WHERE CustomerId = {0} AND Status = {1}",
8        customerId, "Pending")
9    .Include(o => o.Items)  // Can chain Include() after FromSqlRaw!
10    .AsNoTracking()
11    .ToListAsync(ct);
12
13// FromSqlInterpolated — SAFE interpolation via FormattableString
14// EF extracts interpolated values as SQL parameters automatically
15var minAmount = 100m;
16var orders = await _dbContext.Orders
17    .FromSqlInterpolated(
18        $"SELECT * FROM Orders WHERE TotalAmount > {minAmount} AND Status = {'Pending'}")
19    .AsNoTracking()
20    .ToListAsync(ct);
21
22// ❌ SQL INJECTION — NEVER do this with FromSqlRaw:
23var status = Request.Query["status"];  // untrusted user input
24var orders = await _dbContext.Orders
25    .FromSqlRaw($"SELECT * FROM Orders WHERE Status = '{status}'")  // ← INJECTION
26    .ToListAsync(ct);
27// If status = "'; DROP TABLE Orders; --" you've lost your data
28
29// ExecuteSqlRaw — for non-query SQL (UPDATE, DELETE, INSERT)
30// EF Core 7+ bulk update via ExecuteUpdate (preferred over ExecuteSqlRaw for simple cases)
31var rowsAffected = await _dbContext.Orders
32    .Where(o => o.Status == "Abandoned" && o.PlacedAt < DateTime.UtcNow.AddDays(-30))
33    .ExecuteUpdateAsync(set => set
34        .SetProperty(o => o.Status, "Archived")
35        .SetProperty(o => o.ArchivedAt, DateTime.UtcNow), ct);
36// Single UPDATE statement — no entity loading, no change tracker
37// ⚠ Bypasses concurrency tokens — [Timestamp] NOT checked
38
39// ExecuteSqlRaw — raw UPDATE for complex expressions
40var affected = await _dbContext.Database.ExecuteSqlRawAsync(
41    "UPDATE Orders SET TotalAmount = TotalAmount * {0} WHERE CustomerId = {1}",
42    discountFactor, customerId, ct);
43
44// Window function — LINQ can't express this, use FromSqlRaw
45var ranked = await _dbContext.Database
46    .SqlQueryRaw<CustomerRankDto>(
47        @"SELECT c.Id, c.Name,
48               SUM(o.TotalAmount) AS LifetimeValue,
49               ROW_NUMBER() OVER (ORDER BY SUM(o.TotalAmount) DESC) AS Rank
50          FROM Customers c
51          JOIN Orders o ON o.CustomerId = c.Id
52         GROUP BY c.Id, c.Name")
53    .ToListAsync(ct);
54// SqlQueryRaw<T> (EF Core 8+) — for arbitrary result shapes, not entity types
55
56// Stored procedure — output parameter
57var outputParam = new SqlParameter("@TotalOrders", SqlDbType.Int) { Direction = ParameterDirection.Output };
58await _dbContext.Database.ExecuteSqlRawAsync(
59    "EXEC GetCustomerStats @CustomerId = {0}, @TotalOrders = @TotalOrders OUTPUT",
60    customerId, outputParam);
61var total = (int)outputParam.Value;
62
63// Dapper alongside EF — shared connection
64using var connection = _dbContext.Database.GetDbConnection();
65if (connection.State != ConnectionState.Open)
66    await connection.OpenAsync(ct);
67
68var results = await connection.QueryAsync<CustomerSummary>(
69    @"SELECT c.Id, c.Name, COUNT(o.Id) AS OrderCount
70        FROM Customers c
71        LEFT JOIN Orders o ON o.CustomerId = c.Id
72       GROUP BY c.Id, c.Name
73       ORDER BY OrderCount DESC
74       OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY",
75    new { offset = (page - 1) * pageSize, pageSize });
76// Dapper uses EF's connection — same transaction scope if wrapped in BeginTransaction()