Migrations in Prod and Raw SQL

Two questions here, and both are really culture-fit probes for a team that owns a production SQL Server: "How do you deploy schema changes safely?" and "When would you drop below EF to raw SQL or a stored procedure?" The interviewer wants judgment, not dogma — neither "EF does everything" nor "we hand-write all SQL" survives contact with a real agency codebase.

Deploying schema changes without fear

Structure the answer as a pipeline, because that's how the team lives it:

1. Migrations live in source control and are reviewed like code — including the generated SQL, not just the C#.
2. Generate a script for deployment. dotnet ef migrations script --idempotent produces SQL that a DBA can read, approve, and run through the release pipeline. In an Azure DevOps shop, say exactly that: "the script is an artifact; the release stage applies it."
3. Never Database.Migrate() on app startup in production. Know why, out loud: multiple instances racing to migrate, app-pool identity needing DDL rights, no human gate, and a failed migration taking the site down with it. (It's fine for dev/local.)
4. Design for zero-downtime: additive changes first (add nullable column, backfill, then tighten), never rename-in-place, and treat destructive changes as a two-release dance. Mention that migrations must roll forward — "we'd write a new migration to undo, not restore a backup and pray."

Bonus credit: call out that rowversion, indexes, and check constraints belong in the migration history, not applied by hand — hand-applied changes are how model and database drift.

Dropping to raw SQL — and defending it

The strong answer names the legitimate triggers: set-based bulk operations (ExecuteUpdate/ExecuteDelete in modern EF, ExecuteSqlInterpolated before that), reporting queries the LINQ translator fights you on, window functions, table hints, and existing stored procedures the agency already audits. Then defend the boundary: raw SQL still goes through parameterized APIs (FromSqlInterpolated parameterizes for you — string concatenation is the sin, interpolation into the EF API is not), results still map to typed models, and each escape hatch is a documented, reviewed exception — not a habit.

The harder variant: "Our DBA says everything must be stored procedures." Don't fight it in the interview; show you can operate there — EF maps procs fine for commands, you lose composable LINQ for queries, and you'd negotiate the boundary per use case.

Answers that fall flat

• Database.Migrate() at startup, presented proudly, with no awareness of the failure modes.
• Editing an already-applied migration file instead of adding a new one.
• Concatenating user input into FromSqlRaw — instant fail in a security-conscious shop.

Practice prompts

• Rehearse the four-step "safe schema deploy" answer with Azure DevOps vocabulary.
• Explain the two-release plan for renaming a column on a live table.
• Defend using a stored procedure for a 40-table compliance report to a purist, and defend LINQ for CRUD to a proc purist.