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:

Selecting data with a stored procedure

When you look closely at the SQL, you will notice that we expect to receive a list of guests with fields: Forename, Surname, TelNo, From, To and RoomNumber. In order to use the stored procedure to query the database and map results into entities, we need to add an appropriate entity. In my case I’ll add GuestArrival, that looks like this:

[Keyless]
public class GuestArrival
{
    public string Forename { get; set; }

    public string Surname { get; set; }

    public string TelNo { get; set; }

    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public int RoomNumber { get; set; }
}

This class contains all columns that I’d like to map and also it has [Keyless] attribute. Keyless entities have most of the mapping capabilities as normal entities, but they are not tracked for changes in the DbContext. It also means that we won’t be able to perform insert, update, or delete on such entity.

We also need to add a DbSet to our PrimeDbContext.

public class PrimeDbContext : DbContext
{
    public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
        : base(options)
    {
    }

    // from stored procedures
    public virtual DbSet<GuestArrival> GuestArrivals { get; set; }
}

Now we can go ahead and use it. Here is how we can accomplish that:

var guests = primeDbContext.GuestArrivals.FromSqlInterpolated($"GetGuestsForDate '{date}'").ToList();

And if I place it in my API project, it will map results to entities:

In here I’m using date parameter in dd-mm-yyyy format, which I pass as a string into my stored procedure. Then inside I use CONVERT(date, @StartDate, 105), where 105 is a date format I’m going to parse. You can find more supported formats in this article. It probably would be better to use SqlParameter class with Date datatype, but I couldn’t get it to work. 

Summary

Entity Framework Core can handle data selected by stored procedures easily. There is no dedicated method to run the procedure, but it can be run as a standard raw SQL on a DbSet. However, if you’re interested only in executing the stored procedure, you don’t need a DbSet for it. You can check out the details in my previous post: Execute 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!