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 that returns data in Entity Framework Core 5? In my last post: Execute a stored procedure with Entity Framework Core 5 I showed how to run a stored procedure, but selecting the data it’s a different kind of story. 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 spGetGuestsForDate 

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

public partial class spGetGuestsForDate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql = @"
            IF OBJECT_ID('GetGuestsForDate', 'P') IS NOT NULL
            DROP PROC GetGuestsForDate
            GO
 
            CREATE PROCEDURE [dbo].[GetGuestsForDate]
                @StartDate varchar(20)
            AS
            BEGIN
                SET NOCOUNT ON;
                SELECT p.Forename, p.Surname, p.TelNo, r.[From], r.[To], ro.Number As RoomNumber
                FROM Profiles p
                JOIN Reservations r ON p.ReservationId = p.ReservationId
                JOIN Rooms ro ON r.RoomId = ro.Id
                WHERE CAST([From] AS date) = CONVERT(date, @StartDate, 105)
            END";
 
        migrationBuilder.Sql(sql);
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP PROC GetGuestsForDate");
    }
}

This is a simple SQL code, that first checks if a procedure exists and if so, it deletes it. Then it creates a new procedure with the name GetGuestsForDate, which will get all arriving guests for a given date.

When the migration will be executed on the database, this stored procedure will be present, which we can see here:

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