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

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]

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.

To expand on bbodenmiller's answer, In Entity Framework 6, the DbMigration class has methods such as AlterStoredProcedure which allow for modification of stored procedures without having to drop all the way down to raw SQL.

Here's an example of an Up() migration method which alters an existing SQL Server stored procedure named EditItem which takes three parameters of type int, nvarchar(50), and smallmoney, respectively:

public partial class MyCustomMigration : DbMigration
{
    public override void Up()
    {
        this.AlterStoredProcedure("dbo.EditItem", c => new
        {
            ItemID = c.Int(),
            ItemName = c.String(maxLength:50),
            ItemCost = c.Decimal(precision: 10, scale: 4, storeType: "smallmoney")
        }, @" (Stored procedure body SQL goes here) "   
    }

    //...
}

On my machine, this migration script produces the following SQL:

ALTER PROCEDURE [dbo].[EditItem]
    @ItemID [int],
    @ItemName [nvarchar](50),
    @ItemCost [smallmoney]
AS
BEGIN
    (Stored procedure body SQL goes here)
END

EF code-first approach expects that there is no logic in the database. That means no stored procedures and no database views. Because of that code-first approach doesn't provide any mechanism to generate such constructs automatically for you. How could it do that if it means generating logic?

You must create them yourselves in custom database initializer by manual executing creation scripts. I don't think that this custom SQL constructs can be handled by SQL migrations.

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

Related

Recent Posts