Stored procedures are an integral part of any MS SQL database. They are perfect to wrap complicated SQL into a database object, that we can reuse. How to execute a stored procedure in Entity Framework Core 5? Let’s have a look.

Adding a stored procedure

First of all we need to add a stored procedure. The best way to do so is to add a database migration with an appropriate SQL. Let’s start by adding a migration with EF Core global tool command:

dotnet ef migrations add spUpdateProfilesCountry

This will generate a migration, that we can put our SQL into. Let’s see how it may look:

public partial class spUpdateProfilesCountry : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql = @"
            IF OBJECT_ID('UpdateProfilesCountry', 'P') IS NOT NULL
            DROP PROC UpdateProfilesCountry
            GO

            CREATE PROCEDURE [dbo].[UpdateProfilesCountry]
                @StardId int
            AS
            BEGIN
                SET NOCOUNT ON;
                UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > @StardId
            END";

        migrationBuilder.Sql(sql);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP PROC UpdateProfilesCountry");
    }
}

This is a simple SQL code, that first checks if procedure exists and if so, it deletes it. Then it creates a new procedure with UpdateProfilesCountry name, which will update Country column for every Profile that phone number starts from 48.

When this migration will be run on the database, it will create UpdateProfilesCountry stored procedure as in my case.

Running the stored procedure

There is no dedicated method to run a stored procedure, so in the case where a stored procedure doesn’t return data, we can just invoke it as a raw SQL. This can be achieved like this:

await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
    "UpdateProfilesCountry @p0",
    parameters: new[] { minimalProfileId.ToString() });

When I query the database for all numbers starting with 48, I will see that the country has been updated to Poland. This means that our procedure was executed successfully.

BTW. Don’t worry, those are fake data, generated with Bogus 🙂

Summary

Entity Framework Core can handle stored procedures quite well. There is no dedicated method to run the procedure, but it can be run as a standard raw SQL. However, when you’d like to query the database with a stored procedure, you must use a different approach. I have described that in a separate post: Select data with a stored procedure with Entity Framework Core 5.

What’s more, you can handle adding or updating stored procedures with EF Core migrations, which means that all work required can be done with EF Core.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!