Select data with a stored procedure with Entity Framework Core 5

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!

 

13 thoughts on “Select data with a stored procedure with Entity Framework Core 5

    1. Michał Białecki Post author

      Hi Erik,
      What exactly do you mean? Somehow create DbSet and model for a stored procedure output?

      Reply
      1. Erik Ejlskov Jensen

        Yes, it creates the result model and a method wrapper to call it (and by magic, no DbSet is needed!) – try it out!

        Reply
        1. Michał Białecki Post author

          Erik, I looked through the Internet and also what CLI tool offers, but I could not find how to scaffold usage of a stored procedure in EF Core 5. Can you help me out and send a link to documentation or article?

          Reply
  1. Felix Rabinovich

    Hi. Maybe I misunderstand the intent of this example – but when you DbSet to DbContext, the next migration will create a table.
    I thought the whole point is to get the data through Stored Proc *without* the need of creating (I assume, forever empty) table, no?

    Reply
  2. Steve Brailsford

    >> “It probably would be better to use SqlParameter class with Date datatype, but I couldn’t get it to work.”

    I have been searching for a solution to this problem. I have a Core 3.1 app which I get/insert/update/delete using stored procs and calling FromSqlInterpolated. When I pass in a ‘{date}’ to be updated, I get an error that it can’t convert nvarchar to a datetime. The proc parameter is a datetime. If I change the call to FromSqlRaw and pass in the the ToString() of the FormattableString. This seems like a bug in EF to me. I have not found a way to use SqlParameter to specify the parameter datatype in EF Core 3.

    Reply
  3. Dat

    I wat filter data from result query stored procedure. anyone have way?
    my mean is i don’t transmission data in syntax procedure after get data handle that follow my idea

    Reply
  4. Maximiliano Herrera

    Putting single quote inside FromSqlInterpolated like ‘{date}’ throws me an error, the method don’t need any quotes inside, automatically recongnizes the type of the data.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *