EF migration, reading and executing SQL from external file, quote problem-CodePudding

I wanted to use Entity Framework migrations in my .NET 6 application for creating and altering views. I found a relevant article covering this for stored procedures at https://dotnetthoughts.net/creating-stored-procs-in-efcore-migrations/. Following the approach there (except for the EmbeddedResource part, I'm not sure what that's for), for testing purposes I've created two scripts. One, x1.sql, creates a view and the other, x0.sql, drops it. These files are in a scripts subfolder of the Migrations folder where all the migration class files are stored.

x1.sql

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].XYZ AS 
SELECT Panels.PanelId, Panels.Name
FROM Panels

GO

x0.sql

DROP VIEW IF EXISTS [dbo].[XYZ]
GO

For the migration file, with no database changes having been made since the last database update, I added a migration, dotnet ef migrations add CreateXyzView. I edited the resulting migration file to read as follows:

using System.Reflection;

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace MyProject.Migrations
{
    public partial class CreateXyzView : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            using StreamReader reader = new StreamReader("Migrations/scripts/x1.sql");
            var sqlScript = reader.ReadToEnd();
            migrationBuilder.Sql($"EXEC(N'{sqlScript}')");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            using StreamReader reader = new StreamReader("Migrations/scripts/x0.sql");
            var sqlScript = reader.ReadToEnd();
            migrationBuilder.Sql($"EXEC(N'{sqlScript}')");
        }
    }
}

Now, when I run dotnet ef database update, I'm getting an error:

Error Number:105,State:1,Class:15
Unclosed quotation mark after the character string 'SET ANSI_NULLS ON
'.
Incorrect syntax near 'SET ANSI_NULLS ON
'.

Since it's repeating the first line of each of my SQL scripts, this tells me at least that it's reading one of them, presumably x1.sql, but it isn't dealing with the content successfully as I'd expected it to based on the source article. Any ideas what's wrong here, whether this can be fixed? There aren't even any quotes in my scripts, so that can't be what's throwing things off!

CodePudding user response：

Try something like this.

protected override void Up(MigrationBuilder migrationBuilder)
{
    // use the correct path to file here
    var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, baseFolder, subFolder, fileName); 

    if (File.Exists(path))
    {
        migrationBuilder.Sql(File.ReadAllText(path));
    }
}

And set the following properties for the sql file

Build action : Embedded resource

Copy to Output Directory : Copy if newer

CodePudding user response：

SQL Server Utilities Statements - GO. GO is not a Transact-SQL statement. You should not use it in your SQL.

The CREATE VIEW must be the first statement in a query batch. Therefore, you will have to split your SQL into several parts and execute each of them separately.