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!

 

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!