How to run SQL scripts in a file using EF Core migrations?

The Entity Framework code-first approach gives powerful tools to create and manage database objects like tables, fields and their constraints, etc. This article will help you if your application uses stored procedures, functions, and similar database objects and you want to create these objects in the database as part of the migrations.

Entity Framework migrations support the Sql and SqlFile methods to run scripts as part of the migrations. Here, we will see how to read the scripts from a file and run it in the database.

First, you need to add the scripts to be deployed to an SQL file and copy it to one of the folders in your application. Then open your application in visual studio and create an empty migration file using the following command

Add-Migration SQLScript_Migration

Open the empty migration file and copy the following code to the Up function there

 protected override void Up(MigrationBuilder migrationBuilder)
        {
            var sqlFile = Path.Combine("Scripts/ScriptsCreate.Sql"); 
            migrationBuilder.Sql(File.ReadAllText(sqlFile));

        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            
        }

You can change the path based on where you kept your script file.
After this, run the Update-Database command to update the script to the database. This will deploy all the stored procedures, functions, etc., in the script file to the database.


Search