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?

Not yet, still looking for some approach... as this is a workaround but not the exact solution that we can rely on. — lokanath das, Nov 16 '20 at 06:11

score 2 · Accepted Answer · answered Nov 11 '20 at 14:10

2

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();

answered Nov 11 '20 at 14:10

Yinqiu

6,609
1
6
14

In this case,First we need to run the Add-Migration Command, then this Up & Down Methods will come, this is more or less like Workaround i think. As i mentioned , is there a compact way just like we add the Dbset for Tables? – lokanath das Nov 12 '20 at 06:40
As far as I know, there is no such method. – Yinqiu Nov 12 '20 at 07:24
I too didn't get much content related to this. – lokanath das Nov 12 '20 at 07:29
Generally use code first is to use a method like mine. – Yinqiu Nov 12 '20 at 07:32
what if we need procedure for different datatype that is not in Dataset? – Rajon Tanducar Nov 18 '22 at 10:28

score 0 · Answer 2 · answered Nov 05 '22 at 06:02

public partial class SPCreate : Migration
{
   protected override void Up()
   {
       Sql(@"CREATE PROCEDURE [dbo].[GetStudents]
          AS
          BEGIN
              select * from Students
          END");
    }

    protected override void Down()
    {
  
    }
}
//Calling on code 
List<Customer> list;
string sql = "EXEC GetStudents";
list = _context.Database.SqlQuery<Student>(sql).ToList();


List();

Najib · Answer 3 · 2023-01-12T08:01:27.403

Like the normal situation add a migration with a command like bellow:

Add-Migration "Migration Name"

Then in the Up section add as follows:

protected override void Up(MigrationBuilder migrationBuilder)
{
    var assembly = Assembly.GetExecutingAssembly();
    var resourceNames = assembly.GetManifestResourceNames().
        Where(str => str.EndsWith(".sql"));
    foreach (string resourceName in resourceNames)
    {
        using (Stream stream = assembly.GetManifestResourceStream(resourceName))
        using (StreamReader reader = new StreamReader(stream))
        {
            string sql = reader.ReadToEnd();
            migrationBuilder.Sql(sql);
        }
    }
}

This code finds all resources that end in “.sql” and runs each of them.

To make the SQL scripts embedded resources, you can just edit the .csproj file and make sure they are included like following:

<ItemGroup>
    <EmbeddedResource Include="SQL\{YourSqlFileName}.sql" />
    ...
</ItemGroup>

And finally run update database command as usual

this is not the way. really, really bad idea – pbordeaux Jan 18 '23 at 01:12 — pbordeaux, Jan 18 '23 at 01:12
@pbordeaux Could you please elaborate – Evgeny Gorbovoy May 04 '23 at 02:28 — Evgeny Gorbovoy, May 04 '23 at 02:28

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

3 Answers3