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:

1

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:

1

2

3

await primeDbContext . Database . ExecuteSqlRawAsync (

“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 🙂

#asp.net core for .net 5 & ef core 5 #ef core 5 #stored procedure