EF 6 code-first with custom stored procedure

Question

I´m creating a MVC 5 app with a Code-First approach, but I also created some stored procedures on the SQL Server database, is there a way to also generate these stored procedures in c# when the database is created, maybe by executing a sql script, if so where should I do this?

Answer 1

I would use code migrations.

From your Nuget Package Manager you can set up a blank migration by typing

add-migration AddMyStoredProcedure

This should generate an empty class like so

public partial class AddMyStoredProcedure : DbMigration
{
    public override void Up()
    {
    }

    public override void Down()
    {
    }
}

All you need to do is add your stored procedure like so (remember to drop the stored procedure in the Down method in case you need to roll back the migration in the future).

    public partial class AddMyStoredProcedure : DbMigration
{
    public override void Up()
    {
        Sql(@"
            CREATE PROCEDURE dbo.GetMyAddress
            AS
            SELECT * FROM Person.Address");
    }

    public override void Down()
    {
        Sql("DROP PROCEDURE dbo.GetMyAddress");
    }
}

Finally update your database

update-database