Entity Framework Migrations – SQL Scripts Support

Problem

Entity Framework Code-First is very useful in Domain Driven Design and most of the recent .Net applications are using it. As part of Enterprise applications, we are creating views, stored procedures, functions, post deployment scripts, and seed data. There are many advantages of Entity Framework Code-First, but I am not going to cover any of those in this tip. Instead I want to talk about the below problems:

1. As part of the EF Migrations there is no simple way to create and manage the following SQL objects with dependencies:
   • Pre-deployment scripts
   • Views
   • Stored Procedures
   • Functions
   • Seed Data
   • Post-deployment scripts
2. As part of the deployment there is no simple way to run SQL scripts automatically

In this tip, I will show you how to handle the above scenarios.

Solution

Entity Framework Code-First provides a powerful tool for managing database objects such as creating, altering, and deleting tables and seed data with migrations. However, there is no simple way to create views, stored procedures, functions, bulk seed data, and post deployment scripts as part of the migrations. Entity Framework migrations support the Sql and SqlFile methods to run scripts as part of the migrations, but they are not powerful enough to manage the below SQL objects that may have dependencies on one another for enterprise applications.

To solve this problem, I have created a simple utility which can run SQL scripts with dependencies. Integration into new and existing projects is simple and is described below.

Steps to integrate the solution into a Project

1. Download the DbMigrationBase class (Root\efcodefirstrunsqlscripts-master\EFMigrations\DbMigrationBase.cs) and add it to your project which contains your Entity Framework migrations.

2. For any migrations that need to run SQL scripts with dependencies do the following:
   • In the migration class inherit from the DbMigrationBase class.

3. Create a folder structure like in the image below and add your scripts to the appropriate folders.

4. For each script that you add be sure to update the properties in Visual Studio, setting Build Action to Content and Copy to Output Directory as Copy Always or Copy if Newer as shown in the below image.

5. In the migration class, call the method ExecuteSqlFilesWithinMigrationsFolder by passing in the top-level folder name created above.

Now you are all set. When you next update the database then all the pending migration(s) will apply to the database along with the associated SQL scripts.

1. If you need to run any SQL scripts as part of seed data then add the ExecuteSqlFilesWithinMigrationsFolder method to the Seed method found in DbMigrationsConfiguration<T>. As above, create the appropriate folder structure, add the script files, set the properties, and pass in the correct folder name.

Algorithm to run ordered SQL Scripts

To manage dependencies, scripts will run according to the sorting order of directory names and file names within the directories as the following describes:

1. Loop through and execute all the SQL files ordered by file name in the folder.
2. Then loop through all the sub-directories ordered by directory name.
3. In each sub-directory, go back to step 1.

If any script file(s) have dependencies on other file(s) then follow the file naming order as outlined in the below example.

Example: The “v_PeopleActive” view depends on the “v_People” view. To create these views successfully, we need to create the “v_People” view first and then create the “v_PeopleActive” view. To handle this scenario, simply update the file name order like in the image below. The same logic applies to directories as well.

The source code to navigate and loop through all the directories and files is shown below:

internal void ExecuteSqlFilesWithinMigrationsFolder(string migrationsFolderName) 
{ 
    if (migrationsFolderName.StartsWith(@"\")) 
        migrationsFolderName = migrationsFolderName.Substring(1); 
  
    var fullFolderPath = Path.Combine(MigrationsRootFolderPath, migrationsFolderName); 
    RecursivelyExecuteSqlFilesInFolder(fullFolderPath); 
} 
  
  
private void RecursivelyExecuteSqlFilesInFolder(string fullFolderPath) 
{ 
    if (!Directory.Exists(fullFolderPath)) 
    { 
        return; 
    } 
  
    var files = Directory.EnumerateFiles(fullFolderPath, "*.*", SearchOption.TopDirectoryOnly) 
         .Where(f => f.ToLowerInvariant().EndsWith(".sql")).OrderBy(f => f); 
  
    foreach (var file in files) 
    { 
        ExecuteSqlScriptInFile(file); 
    } 
  
    Directory.EnumerateDirectories(fullFolderPath).OrderBy(d => d).ToList().ForEach(directory => 
        { 
            RecursivelyExecuteSqlFilesInFolder(directory); 
        } 
    ); 
} 

The source code to retrieve the Migrations folder base path, shown below, will support scripts run from the package manager console or web project.

protected const string MIGRATION_SCRIPTS_BASE_DIRECTORY = @"Migrations"; 
  
public string MigrationsRootFolderPath 
{ 
    get 
    { 
        var baseDirectory = AppDomain.CurrentDomain.BaseDirectory; 
        if (HttpContext.Current != null) 
            baseDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "bin"); 
        return Path.Combine(baseDirectory, MIGRATION_SCRIPTS_BASE_DIRECTORY); 
    } 
} 

Follow the below approach to manage SQL files for migrations

• Create a folder with same name as the migration name under the Migrations folder. In this example I have created the Initial folder for the Initial migration.
• Create any required sub folders (such as Views or SVFs) with appropriate names to handle dependencies.
• Add the required SQL files in appropriate folders.

Note: If there are any dependencies within the SQL files or folders then follow the naming/sorting standards explained above.

• Update the SQL file properties, setting Build Action to Content and Copy to Output Directory as Copy Always or Copy if Newer as shown in below image.

• Good Practice: As shown in below sample code, create scripts in an independent way to make updating your database with migrations simpler.

Views:

IF object_id('[dbo].[ViewName]') IS NULL 
EXEC ('CREATE VIEW [dbo].[ViewName] AS SELECT 1 AS Id') 
GO 
ALTER VIEW [dbo].ViewName 
AS 
SELECT * FROM table 
GO 
Functions: 
-- DROP and CREATE the functions will drop the security settings. 
IF object_id('[dbo].[FunctionName]') IS NULL 
    EXEC ('CREATE function [dbo].[FunctionName]() RETURNS DATETIME AS  BEGIN RETURN GETDATE() END') 
GO 
ALTER FUNCTION [dbo].[FunctionName]()
RETURNS DATETIME
AS
BEGIN 
DECLARE @Result DATETIME; 
RETURN @Result; 
END 

Next Steps

• This utility makes using Entity Framework Code-First much easier to manage running SQL scripts as part of the migrations or seed data.
• With this utility, we are running all the scripts as part of the migration without manually executing any of the scripts. This automation helps make the DevOps process simpler by avoiding manual step.
• I have created a sample project hosted in GitHub. If you would like to run it please use the following steps to download and integrate the sample application into your development environment:
  • Download or clone the source code
  • Open the project in Visual Studio
  • Set ‘web’ as the startup project
  • Open web.config file and update the below connection string entry

<connectionStrings> 
    <add name="EFEntities" connectionString="data source=.;initial catalog=EFDatabase;Integrated Security=SSPI;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" /> 
</connectionStrings>  
  

• Deploy the included database migrations (Initial and 2^nd) and verify that the database has been updated appropriately
  • Initial Migration will create views and functions
  • 2ndMigration will update the views created on Initial migration and run post deployment scripts