EF Core Raw SQL — FromSqlRaw, ExecuteSqlRaw, and Dapper Integration

EF Core LINQ is the first choice — it is type-safe and parameterized automatically.

Raw SQL is justified when:
  ✓ Complex queries that LINQ cannot translate (e.g., recursive CTEs, PIVOT)
  ✓ Stored procedures that return entity-shaped results
  ✓ Bulk operations (UPDATE all rows matching condition without loading them)
  ✓ Window functions (ROW_NUMBER, RANK, LAG/LEAD)
  ✓ Performance-critical queries where you need the exact SQL plan

Raw SQL is NOT justified when:
  ✗ "LINQ is confusing" — learn LINQ, do not escape to raw SQL
  ✗ Simple WHERE conditions — LINQ handles these cleanly
  ✗ Anywhere you concatenate user input into a query string (SQL injection risk)

// Query patients using raw SQL, EF Core maps result to Patient entities
// Parameters MUST use SqlParameter or interpolated overload — never string.Concat
var patients = await _db.Patients
    .FromSqlRaw(
        "SELECT * FROM patients WHERE ward_id = @wardId AND is_deleted = 0",
        new SqlParameter("@wardId", wardId.Value))
    .AsNoTracking()
    .ToListAsync(ct);

// Global query filters still apply unless IgnoreQueryFilters() is called
// You can chain LINQ after FromSqlRaw
var activeCount = await _db.Patients
    .FromSqlRaw("SELECT * FROM patients WHERE ward_id = @wardId",
        new SqlParameter("@wardId", wardId.Value))
    .Where(p => !p.IsDeleted)
    .CountAsync(ct);

// FromSqlInterpolated: automatically parameterizes the interpolated values
// SAFE — each {variable} becomes a SqlParameter
var patients = await _db.Patients
    .FromSqlInterpolated(
        $"SELECT * FROM patients WHERE ward_id = {wardId.Value} AND is_deleted = 0")
    .AsNoTracking()
    .ToListAsync(ct);

// NEVER use FromSqlRaw with string interpolation or concatenation:
// BAD — SQL injection vulnerability:
var bad = await _db.Patients
    .FromSqlRaw($"SELECT * FROM patients WHERE mrn = '{mrnInput}'")
    .ToListAsync(ct);
// If mrnInput = "'; DROP TABLE patients; --" → data loss

// EF Core 8+: query for non-entity types using SqlQuery<T>
// No longer restricted to entity types mapped in the model

var summary = await _db.Database
    .SqlQuery<PatientWardSummaryDto>($@"
        SELECT
            w.ward_name        AS {nameof(PatientWardSummaryDto.WardName)},
            COUNT(p.id)        AS {nameof(PatientWardSummaryDto.PatientCount)},
            AVG(i.inr_value)   AS {nameof(PatientWardSummaryDto.AverageInr)}
        FROM wards w
        LEFT JOIN patients p  ON p.ward_id = w.id AND p.is_deleted = 0
        LEFT JOIN inr_results i ON i.patient_id = p.id AND i.recorded_at > {cutoff}
        WHERE w.id = {wardId.Value}
        GROUP BY w.ward_name")
    .ToListAsync(ct);

// Column names in SQL must match DTO property names (case-insensitive)

// Bulk update without loading entities
var affected = await _db.Database.ExecuteSqlRawAsync(
    "UPDATE prescriptions SET is_active = 0 WHERE patient_id = @patientId AND expiry_date < @now",
    new SqlParameter("@patientId", patientId.Value),
    new SqlParameter("@now", DateTime.UtcNow));

// Stored procedure call
await _db.Database.ExecuteSqlRawAsync(
    "EXEC sp_ArchiveDischargedPatients @wardId = @wardId, @beforeDate = @date",
    new SqlParameter("@wardId", wardId.Value),
    new SqlParameter("@date", cutoffDate));

// ExecuteSqlRaw runs outside the change tracker — EF Core doesn't know what changed.
// If you update rows that are tracked in the current context,
// the tracked entities are now stale. Either reload them or dispose the context.

// Stored procedure that returns Patient-shaped rows
var patients = await _db.Patients
    .FromSqlRaw("EXEC sp_GetWardPatients @wardId",
        new SqlParameter("@wardId", wardId.Value))
    .AsNoTracking()
    .ToListAsync(ct);

// If stored procedure returns multiple result sets, use Dapper instead
// EF Core FromSqlRaw handles only the first result set

// Use Dapper when:
//   • Multiple result sets from one query
//   • Complex projections not matching any entity shape
//   • You want full control over mapping

// Dapper shares the same connection — works inside EF Core transaction
using var transaction = await _db.Database.BeginTransactionAsync(ct);

// EF operation
await _db.Prescriptions.AddAsync(prescription, ct);
await _db.SaveChangesAsync(ct);

// Dapper query on the same connection/transaction
var connection = _db.Database.GetDbConnection();
var summary    = await connection.QueryFirstOrDefaultAsync<WarfarinSummaryDto>(
    "SELECT * FROM v_warfarin_summary WHERE patient_id = @id",
    new { id = prescription.PatientId.Value },
    transaction.GetDbTransaction());

await transaction.CommitAsync(ct);