c# - Can you create sql views / stored procedure using Entity Framework 4.1 Code first approach

Question

Entity Framework 4.1 Code First works great creating tables and relationships. Is it possible to create sql views or stored procedure using Code first approach? Any pointers regarding this will be highly appreciated. Thanks a lot!

Answer 1

We support stored procedures in our Entity Framework Code First Migrations. Our approach is to create some folder to hold the .sql files (~/Sql/ for example). Create .sql files in the folder for both creating and dropping the stored procedure. E.g. Create_sp_DoSomething.sql and Drop_sp_DoSomething. Because the SQL runs in a batch and CREATE PROCEDURE.. must be the first statement in a batch, make the CREATE PROCEDURE... the first statement in the file. Also, don't put GO after the DROP.... Add a resources file to your project, if you don't have one already. Drag the .sql files from solution explorer into the Files view of the Resources designer. Now create an empty migration (Add-Migration SomethingMeaningful_sp_DoSomething) and use:

namespace MyApplication.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class SomethingMeaningful_sp_DoSomething : DbMigration
    {
        public override void Up()
        {
            this.Sql(Properties.Resources.Create_sp_DoSomething);
        }

        public override void Down()
        {
            this.Sql(Properties.Resources.Drop_sp_DoSomething);
        }
    }
}

~/Sql/Create_sp_DoSomething.sql

CREATE PROCEDURE [dbo].[sp_DoSomething] AS
BEGIN TRANSACTION
-- Your stored procedure here
COMMIT TRANSACTION
GO

~/Sql/Drop_sp_DoSomething.sql

DROP PROCEDURE [dbo].[sp_DoSomething]