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] 

Answer 2

At first sight I really like the approach of Carl G but it involves a lot of manual interaction. In my scenario, I always drop all stored procedures, views... and recreate them whenever there is a change in the database. This way we are sure everything is up-to-date with the latest version.

Recreation happens by setting the following Initializer:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>()); 

Then our seed method will get called whenever there is a migration ready

protected override void Seed(DeploymentLoggingContext context)     {         // Delete all stored procs, views         foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sql\\Seed"), "*.sql"))         {             context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);         }          // Add Stored Procedures         foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sql\\StoredProcs"), "*.sql"))         {             context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);         }     } 

SQL Statements are stored in *.sql files for easy editing. Make sure your files have "Build Action" set to "Content" and "Copy to Output Directory" set to "Copy Always". We lookup the folders and execute all scripts inside. Don't forget to exclude "GO" statements in your SQL because they cannot be executed with ExecuteSqlCommand().

My current directory layout is as follows:

Project.DAL
+ Migrations
+ Sql
++ Seed
+++ dbo.cleanDb.sql
++ StoredProcs
+++ dbo.sp_GetSomething.sql

Now you just need to drop extra stored procedures in the folder and everything will get updated appropriately.