Migrations script: wrap queries in `sp_executesql` when using `--idempotent`
- 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
Assessment
This issue has not been assessed yet.