How to run SQL scripts in a file while performing the code first 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, seeding set of data 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 in Package Manager Console.
Add-Migration SQLScript_MigrationOpen the empty migration file (this will be opened by default) 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.
If you want to write cleaner code or you just want to have tips like this every week, so you can follow me on medium will be more likely to show you the next article..
If you would like to buy me a coffee just click here.
Drop me a ‘Hi’ on:
WhatsApp: +94719994818
Facebook: iAbu94
Email: iabu94.dev@gmail.com
