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!

Idioblast

Answer 1

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.

Chrisoula

Yes - the equivalent of a View in EF Code First is a table with a copy of the data. Your code is responsible for maintaining that secondary table. – Queenhood

Sorry to disagree but Stored procedures should be considered to be created also, so from my perspective that's a missing feature, and it should be added. Stored procedures are part of the database and also views. It is true they don't have to be OVER used but they exist and they have to be treated as first citizens they are in the database world – Fill

@EugenioMiró: Yes that is true but in such case you should use database first approach instead of code first. If you want to create database logic so do it but do it directly in the database and let EF create model from that database instead of hacking it in opposite direction. – Chrisoula

Is it possible to use the database approach first with the FluentAPI? – Geranium

Stored Procedures (or any arbitrary database structure) can be added to the Database using the DbMigration.Sql method in a migration EF5+. Alas, Code First still does not have a mechanism to track them. – Archfiend

Answer 2

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]

Monro

Why do I need a resource ? – Legerdemain

You don't. You just need the SQL. I stored it in a file and added it as a resource to access it in the program. You could just put the SQL as a string into the Up/Down methods. – Monro

Its better not to use file resource, but string resource. Files are linked, so changes are propaged over all migration resources. If you embed stored procedures as strings, you can then use resouce to add, drop, alter and unalter procedures in migration. I used your method to implement that and it works fine. My variation uses extensions on DbMigration class to implement create_procedure() and friends. – Legerdemain

I liked the file resource because then I could edit the file in the editor with syntax highlighting. What is the danger with file resource? Changes to the file are shared between all migrations? In my case, each SQL file is specific to a single stored procedure, so it is okay for the file to be shared across migrations so long as only one actually uses it. – Monro

That can't be right, because in one moment you may have need to change that stored procedure, which will require different migration to keep different version. – Legerdemain

In our case, if the stored procedure changes, a new migration must occur to change it. Same as if new index required or if column has new definition etc. – Monro

yes, a new migration, but since its already existing procedure you will have its first migration that introduced it, in its resource. Since you will now add it to another one, you will have 2 exactly the same files in 2 different resources. I.E as soon as you change stored procedure, the first resource will become invalid, so to speak. So this method you use is enough only to create or drop procedures, not to alter and un-alter them. For that, you need to keep them as strings, i.e. embedded fullly, not files which are linked. – Legerdemain

Nice solution. I like to have the SQL in separate files from the C#-code. Next time you update the SP, couldnt you just add another sql-file with "Alter SP" in the Up-method? Or create a new migration and do the Down method first when doing the Up. – Sherrie

@Legerdemain I don't get what you're saying. Maybe someone else does. As I see it, if you have three migrations introducing/modifying a procedure you have: (1) Up: Create SP, Down: Drop SP; (2) Up: Alter SP, Down: Alter SP to be like in step 1; (3) Up: Alter SP again, Down: Alter SP to be like in step 2. – Monro

@Carl, Yes, thats how I do it. – Legerdemain

you should use the seed() function and check if procedure exists like in this SO question: #2072586 . Then your procedure will recreate each time you run update database independently of your model changes. – Kellikellia

@Carl I succesfully used your solution for SP's & Functions. On local env. I get the migration records but when deploying through AzureDevOps PipeLine, these specific migration records are missing. Not sure why? – Agha

Answer 3

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.

Markusmarl

Thanks for sharing. This is similar to what I am trying to do. – Geranium

Nice answer, but drop and create indexes? Every time the app runs? Really? – Foible

You can leave the "GO" statements in the script if you split the file text on "GO" and execute each string in the array separately. I suggest using new Regex("GO", RegexOptions.IgnoreCase) and skip executing empty strings. – Sindhi

I use var baseDir = AppDomain.CurrentDomain.BaseDirectory.Replace("\\bin\\Debug", string.Empty) + "\\Migrations\\postDeploy";. I have a postdeploy folder with my scripts at solution level within my Migrations folder. This way I don't dump unneeded files with my release and i don't have to set them as resources. – Kellikellia

right now, if you release it that way, it won't work anyways as a release should be build in Release mode and you're replacing Debug. I don't see a real problem to copy these files to the output directory (they are not resources btw). – Markusmarl

Does this happen when doing migrations? Or when an app/site starts and the db context is first instantiated? – Felly

I've played around with this and it's a clever solution but it falls down when it comes to generating a deployment script for a pre-production or production environment.. the SPs are not considered as part of the migration and as such are not included in the update script. I think it comes down to the fact that the Seed method is more for the purposes of infrastructural type configuration rather than schema management. Nice idea though. Think I'll have to use the fluent api. – Felly

@daveL It happens when doing migrations as the seed will be executed. It will also happen on application startup as the migrations will be executed (depending on your migration strategy). I'm not sure what your deployment plan is but we are using this in production as it has the same database structure as dev / test / qa. – Markusmarl

@Markusmarl our deployments are managed by release management - they will only accept change scripts. There is not a cat's chance in hell that a developer would be allowed push changes into prod using the package manager console – Felly

How does this work when the most recent version of your stored procedure has become incompatible with previous versions of the database and you must return to a previous version? E.g.: you add a new column in a migration and you update your stored procedure to use it. Later you discover that there is a problem with the application so you roll-back the migration and redeploy the app code from the previous release. Now your new column is not present, but the stored procedure is still the most recent version, so will it not fail because of the missing column? – Monro

Great work, if anybody is also wondering how to run only on "update-database", here is the solution: https://mcmap.net/q/197173/-confusion-over-ef-auto-migrations-and-seeding-seeding-every-program-start @MartinCapodici – Volar

Answer 4

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

Teraterai

While I like Carl's answer, it looks like a lot of work to maintain for not much payoff. Thanks! – Krigsman

Answer 5

It appears to be poorly documented however it appears you can now do some Stored Procedure manipulation using AlterStoredProcedure, CreateStoredProcedure, DropStoredProcedure, MoveStoredProcedure, RenameStoredProcedure in Entity Framework 6. I haven't tried them yet so can't yet give an example of how to use them.

Leeth

You an find an example here: c-sharpcorner.com/UploadFile/ff2f08/… – Christ

Still poorly documented. Thanks for the example Jas – Jarl

Answer 6

emp's design works like a champion! I'm using his pattern but I also map stored procedures inside of my DbContext class which allows simply calling those context methods instead of using SqlQuery() and calling the procedures directly from my repository. As things can get a bit hairy when the application grows, I've created a check within my Seed method that makes sure the actual stored procedure parameter count match up to the parameter count on the mapping method. I've also updated the DROP loop emp mentioned. Instead of having to maintain a separate folder/file for the drop statements, I simply read the first line of each sql file and replace CREATE with DROP (just make sure the first line is always just CREATE PROCEDURE ProcName). This way all procedures in my StoredProcs folder get dropped and recreated each time Update-Database is ran. The drop is also wrapped in a try-catch block in case the procedure is new. For the procedure parameter count to work, you'll need to make sure you wrap a BEGIN/END block around your tsql since each line of the file is read up to BEGIN. Also make sure each sp parameter is on new line.

        // Drop Stored Procs
        foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\DataContext\\SiteMigrations\\StoredProcs"), "*.sql"))
        {
            // Try to drop proc if its already created
            // Without this, for new procs, seed method fail on trying to delete
            try
            {
                StreamReader reader = new StreamReader(file);
                // Read first line of file to create drop command (turning CREATE [dbo].[TheProc] into DROP [dbo].[TheProc])
                string dropCommand = reader.ReadLine().Replace("CREATE", "DROP");

                context.Database.ExecuteSqlCommand(dropCommand, new object[0]);
            }
            catch { }

        }

        // Add Stored Procs
        foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\DataContext\\SiteMigrations\\StoredProcs"), "*.sql"))
        {
            // File/Proc names must match method mapping names in DbContext
            int lastSlash = file.LastIndexOf('\\');
            string fileName = file.Substring(lastSlash + 1);
            string procName = fileName.Substring(0, fileName.LastIndexOf('.'));

            // First make sure proc mapping in DbContext contain matching parameters.  If not throw exception.
            // Get parameters for matching mapping
            MethodInfo mi = typeof(SiteContext).GetMethod(procName);

            if (mi == null)
            {
                throw new Exception(String.Format("Stored proc mapping for {0} missing in DBContext", procName));
            }

            ParameterInfo[] methodParams = mi.GetParameters();
            // Finished getting parameters

            // Get parameters from stored proc
            int spParamCount = 0;
            using (StreamReader reader = new StreamReader(file))
            {
                string line;                    
                while ((line = reader.ReadLine()) != null) 
                {
                    // If end of parameter section, break out
                    if (line.ToUpper() == "BEGIN")
                    {
                        break;
                    }
                    else
                    {
                        if (line.Contains("@"))
                        {
                            spParamCount++;
                        }
                    }                        
                }
            }
            // Finished get parameters from stored proc

            if (methodParams.Count() != spParamCount)
            {
                string err = String.Format("Stored proc mapping for {0} in DBContext exists but has {1} parameter(s)" +
                    " The stored procedure {0} has {2} parameter(s)", procName, methodParams.Count().ToString(), spParamCount.ToString());
                throw new Exception(err);
            }
            else
            {
                context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);
            }
        }

Enjoy!

Scolex

Answer 7

As Ladislav pointed out, DbContext in general does tend to minimize the logic in the database, but it is possible to execute custom SQL by using context.Database.ExecuteSqlCommand() or context.Database.SqlQuery().

Predikant