How to create a stored procedure from EF Core code first approach?

Question

In EF Core, we can add the tables using entity classes. Stored procedure is one of the useful component. So is there any way to create a stored procedure from the DbContext class (just like Using Dbset we can create the tables)?

Have gone through some links where in the EF6 there is a way to push the stored procedure, although its an workaround it seems.

Reference Link--> EF 6 code-first with custom stored procedure

Although I followed the above link, it means for EF6, where the migration files were inherited from DbMigration, in EF Core it's a Migration base class. I don't see a way & probably didn't find much article related to it.

Any help or suggestions?

Answer 1

Under normal circumstances, we use MigrationBuilder.Sql method in the Up method of your migration class to create a stored procedure, like below:

public partial class SPCreate : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
    //...

    var sp = @"CREATE PROCEDURE [dbo].[GetStudents]
        AS
        BEGIN
            select * from Students
        END";

    migrationBuilder.Sql(sp);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    //...
}
}

To execute a stored procedure, you can call FromSqlRaw method, like this:

var students= _dbcontext.Students.FromSqlRaw("EXECUTE GetStudents").ToList();