Code First Migrations and Stored Procedures

I've done this like so...

In the current migration class -

public partial class MyMigration : DbMigration{    public override void Up()    {        ... other table creation logic        // This command executes the SQL you have written        // to create the stored procedures        Sql(InstallScript);        // or, to alter stored procedures        Sql(AlterScript);    }    public override void Down()    {        ... other table removal logic        // This command executes the SQL you have written        // to drop the stored procedures        Sql(UninstallScript);        // or, to rollback stored procedures        Sql(RollbackScript);    }    private const string InstallScript = @"        CREATE PROCEDURE [dbo].[MyProcedure]        ... SP logic here ...    ";    private const string UninstallScript = @"        DROP PROCEDURE [dbo].[MyProcedure];    ";    // or for alters    private const string AlterScript = @"        ALTER PROCEDURE [dbo].[AnotherProcedure]        ... Newer SP logic here ...    ";    private const string RollbackScript = @"        ALTER PROCEDURE [dbo].[AnotherProcedure]        ... Previous / Old SP logic here ...    ";}

I am using EF6 and the DbMigration class provides methods to Create/Alter/Delete stored procedures

• Create a new stored procedure

  public partial class MyFirstMigration : DbMigration{    public override void Up()    {        // Create a new store procedure        CreateStoredProcedure("dbo.DequeueMessages"        // These are stored procedure parameters        , c => new{                            MessageCount = c.Int()        },        // Here is the stored procedure body        @"        SET NOCOUNT ON;        SELECT TOP (@MessageCount)            *        FROM            dbo.MyTable;        ");    }    public override void Down()    {        // Delete the stored procedure        DropStoredProcedure("dbo.DequeueMessages");                    }}

• Modify a stored procedure

  public partial class MySecondMigration : DbMigration{    public override void Up()    {        // Modify an existing stored procedure        AlterStoredProcedure("dbo.DequeueMessages"        // These are new stored procedure parameters        , c => new{                            MessageCount = c.Int(),            StatusId = c.Int()        },        // Here is the new stored procedure body        @"        SET NOCOUNT ON;        SELECT TOP (@MessageCount)            *        FROM            dbo.MyTable        WHERE            StatusId = @StatusId;        ");    }    public override void Down()    {        // Rollback to the previous stored procedure        // Modify an existing stored procedure        AlterStoredProcedure("dbo.DequeueMessages"        // These are old stored procedure parameters        , c => new{                            MessageCount = c.Int()        },        // Here is the old stored procedure body        @"        SET NOCOUNT ON;        SELECT TOP (@MessageCount)            *        FROM            dbo.MyTable;        ");                  }}

namespace QuickProject.Migrations{    using System;    using System.Data.Entity.Migrations;public partial class CreateStoredProcedure_GellAllAgents : DbMigration{    public override void Up()    {        CreateStoredProcedure("dbo.GellAllAgents", c => new        {            DisplayLength = c.Int(10),            DisplayStart = c.Int(0),            UserName = c.String(maxLength: 255, defaultValueSql: "NULL"),            FullName = c.String(maxLength: 255, defaultValueSql: "NULL"),            PhoneNumber = c.String(maxLength: 255, defaultValueSql: "NULL"),            LocationDescription = c.String(maxLength: 255, defaultValueSql: "NULL"),            AgentStatusId = c.Int(defaultValueSql: "NULL"),            AgentTypeId = c.Int(defaultValueSql: "NULL")        }, StoredProcedureBody);    }    public override void Down()    {        DropStoredProcedure("dbo.GellAllAgents");    }    private const string StoredProcedureBody = @"Declare @FirstRec int, @LastRec intSet @FirstRec = @DisplayStart;Set @LastRec = @DisplayStart + @DisplayLength;With CTE_AspNetUsers as(     Select ROW_NUMBER() over (order by AspNetUsers.Id) as RowNum,         COUNT(*) over() as TotalCount, AspNetUsers.Id, AspNetUsers.FullName, AspNetUsers.UserName, AspNetUsers.PhoneNumber, Locations.Desciption as LocationDescription, Cities.Name as LocationCity, AgentStatus.Name as AgentStatusName, AgentTypes.Name as AgentTypeName         from AspNetUsers     join Locations on AspNetUsers.LocationId = Locations.id     join Cities on Locations.CityId = Cities.Id     join AgentStatus on AspNetUsers.AgentStatusId = AgentStatus.Id     join AgentTypes on AspNetUsers.AgentTypeId = AgentTypes.Id     where (Discriminator = 'Agent'          and (@UserName is null or UserName like '%' + @UserName + '%')         and (@FullName is null or FullName like '%' + @FullName + '%')         and (@PhoneNumber is null or PhoneNumber like '%' + @PhoneNumber + '%')         and (@LocationDescription is null or  @LocationDescription like '%' + (select Cities.Name from Cities where Locations.CityId = Cities.Id) + '%' or  @LocationDescription like '%' + Desciption + '%')         and (@AgentStatusId is null or AgentStatusId = @AgentStatusId)         and (@AgentTypeId is null or AgentTypeId = @AgentTypeId)     )     group by AspNetUsers.Id, AspNetUsers.FullName,AspNetUsers.UserName, AspNetUsers.PhoneNumber, Locations.Desciption, Cities.Name, AgentStatus.Name, AgentTypes.Name)Select *from CTE_AspNetUserswhere RowNum > @FirstRec and RowNum <= @LastRec";}}

Result, When you view/modify the SP in SQL server, that's why it shows "ALTER PROCEDURE"