Entity Framework Code First Migrations and Running SQL Scripts

I suspect I’m not alone in that even though I’m using Code First for my data access there are points where it just makes more sense to encapsulate a complex process in a stored procedure.  But getting a database created using Migrations and including the process of running all the required scripts into a database on initial creation isn’t the most obvious thing.  You also want it to be repeatable, consistent and you don’t want to forget any.

You may have other scripts that create triggers or functions in addition to stored procedures and I’ve written this into my Configuration class.  The initial problem is that you need to include in your scripts a check to make sure that the object you are about to create in your database doesn’t already exist or the creation process will error.  The exact way to do this depends on the type of object you are creating but an example is included here:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'usp_Create_Role')
SET NOEXEC ON
GO
CREATE PROCEDURE
...
GO

SET NOEXEC OFF
GO

The NOEXEC command will prevent the following statements from actually being executed until the NOEXEC is turned off.

The next part of this is to make use of the Seed method in the Configuration class and use of the #if DEBUG preprocessor directive. In my seed method I’ve included this:

#if DEBUG
RunExternalScripts(context);
#endif

The contents of this method is pretty simple and includes a process for enumerating the collection of scripts and then executing them, all pretty standard stuf really, my scripts are separated into directories by type (these are also all version controlled in SVN):

#if DEBUG

        /// <summary>
        /// Runs the external scripts.
        /// </summary>
        /// <param name="context">The context.</param>
        private void RunExternalScripts(MyDataDbContext context)
        {
            _subDirs = new[] { "data\schema\", "data\functions\", "data\storedprocedures\", "data\staticdata\" };
            _scriptRoot = "<ROOT PATH>";
            string currentScript = string.Empty;

            try
            {
                CompileSqlScripts();

                if (SqlScripts != null && SqlScripts.Count > 0)
                {
                    foreach (var sqlScript in SqlScripts)
                    {
                        currentScript = sqlScript;
                        ScriptExecutor.ExecuteFile(context.Database.Connection.ConnectionString, currentScript);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(string.Format("The database could not be initialized. Error running {0}.", currentScript), ex);
            }
        }
#endif

So now any time I want to delete my local database and start from scratch for any reason all my scripts are run into the database. Because all my scripts also check for the objects existence before running I can also happily run update-database commands in the Package Manager console with any worries (the seed command is run after every update). But also because all of these methods are within #if DEBUG directive none of this code will be compiled into the release version of the assemblies.