Stored Procedure in Entity Framework Core Migrations
SQL Server stored procedure is a set of SQL statements grouped as a logical unit and stored in the database. The stored procedure can accepts input parameters and executes the T-SQL statements in the procedure, can return the result. If you’re using Entity Framework Code first approach there is no direct way to create stored procedure in C# code. You can execute a stored procedure in Entity Framework with the help of
FromSqlInterpolated method. Here is an example.
usp_GetAllTodoItemsByStatus procedure is like this.
As mentioned earlier, there is no C# way is to create procedure. If you need to deploy the stored procedure as part of migrations you need to create an empty migration first, modify the
Down methods and execute it. So first you need to create an empty migration. You can do this with command
dotnet ef migrations add GetAllTodoItemsByStatusProc. Once you execute the command you will get an empty migration - if you have some other entity changes those changes also will be there. I recommend an empty migration. Once you execute this command you will get an empty migration like this.
Next you need to modify the
Down methods, like this.
Next execute the
dotnet ef database update command - which will apply the migration to the database. To deploy the migration using Azure DevOps, you need to create the SQL Scripts out of migration - You can find more details about deploying migration from Azure DevOps in this blog post. To generate the SQL Scripts you can execute the command -
dotnet ef migrations script --output script.sql -i. Once you execute this, dotnet ef will generate SQL file. The generated file will be something like this.
But if you try to execute this in your SQL Server Management Studio you will get error like
Must declare the scalar variable "@isCompleted" or
Incorrect syntax near the keyword 'OR'.
This issue can be fixed using the
EXEC command. You can modify the migration -
Up method like this.
Now if you generate the script - it will be something like this.
Because of the
EXEC statement - SQL Server doesn’t look into the variables and won’t raise any compile time errors. Also please try prefix Unicode character string constants with the letter N, like this -
@EXEC(N'CREATE OR ALTER PROCEDURE. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
In this blog post you learned about creating and deploying stored procedures with EF Core and Azure DevOps. In this scenario, you’re writing a stored procedure. If you’re getting the stored procedures from your DBA - as files - copy / pasting the code is error prone. There is an alternate approach. So you don’t need to copy paste the code in the migrations script. You need to add the SQL files as embedded resources.
And modify the Migration script
Up command like this.
Using this way you will be able to apply stored procedure migrations if you’re getting them as files also it will help you to keep your C# migration scripts clean.
Happy Programming :)