dotnet / dotnet/efcore

Migrations script: wrap queries in `sp_executesql` when using `--idempotent`

Open
#27,729 17 comments 7 reactions 0 assignees View on GitHub
area-migrations customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

## What problem are you trying to solve?

When generating migration scripts with `dotnet ef migrations script --idempotent`, the tool generates invalid SQL in some cases. Consider the following migration:

```csharp
public partial class CreateAView : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("create view MyView as select 1;");
```

This results in the following migration script:

```sql
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20220301060232_CreateAView')
BEGIN

create view MyView as select 1;

END;
GO
```

This script is invalid because `create view` must be the only statement in an SQL query batch; it cannot appear inside `IF`.

## Describe the solution you'd like

The easiest solution seems to me to wrap custom SQL inside `sp_executesql`. Something like this:

```sql
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20220301060232_CreateAView')
BEGIN

DECLARE @query nvarchar(max) = N'
create view MyView as select 1;
';
EXECUTE sp_executesql @query;

END;
GO
```

This works for Microsoft SQL server -- I'm sure other dialects have their own equivalent of dynamic SQL.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.