Entity Framework Core Interview Question #99

How can raw SQL be used for schema or data operations in an EF Core migration?

Migrations, Database Schema & Data Management Mid-Level Intermediate

Quick Interview Answer

A migration can execute SQL directly through migrationBuilder.Sql when an operation cannot be conveniently expressed by standard migration APIs.

Detailed Explanation

A migration can execute SQL directly through migrationBuilder.Sql when an operation cannot be conveniently expressed by standard migration APIs.

This can be useful for controlled data transformations, provider-specific objects, or special schema operations.

Raw SQL makes the migration more database-specific and must be written carefully. Consider repeatability, transaction behavior, escaping, execution time, and how rollback will work.

Code Example

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        "UPDATE Products SET IsPublished = 0 " +
        "WHERE IsPublished IS NULL");
}