###### What's that you say Lassie? Timmy fell down the old well? AGAIN?!?!?! :wink:

There's days I truly do sympathize with good ol' Timmy; I fall down that darn well waaaayyy too often!!! :sunglasses:

Part of the reason is because I'm too stubborn to realize that I don't have to keep walking over those dried-up-rotting-just-waiting-to-break boards scattered across that dark dreary void; I could just walk around.

A heck of a metaphor to how I've always done database development, namely, individual stand-alone SQL scripts coupled to some outdated batch files that combine everything into a single neat package that I can email to the DBA for execution on the Production server.

1998 called ... they want their process back!!! :wink:

As I mentioned in Part 1, EF6 migration support for stored procedures and views and other non table-specific database objects has improved, but sometimes old dogs just can't learn new tricks.

And say what you will ... having individual objects isolated to individual files does kinda sorta mesh nicely with that whole version control thingamabob anyway.

Sometimes the baby don't need to be thrown out with the bath water ...
Just in case it wasn't immediately apparent from Part 1 ... I'm a migration fanboy. I figured there just had to be some way to meld the old and the new and come up with a win/win. (Sometimes I can be so naïve! :smile:)

Let's give it a try ...

Waving my magic wand ... the objective is to figure out a way to incorporate hand-crafted/file-based external SQL into the migration pipeline.

At the core of the migration ecosystem are the Up()/Down() methods that get populated when the Add-Migration operation is executed.

I stumbled on a neat article 1 by Rowen Miller from the EF team about writing custom migrations. That lead me to a judicious review of the EF6 source code to better understand the use of the AddMigration() method referenced in the article.

My Eureka moment
Timing is everything! Just about the moment I started realizing I needed this feature, the EF team accepted a pull request from contributor Brandon Dahler which included a neat little method named SqlFile() 2.

This method lives on the DbMigration class and can be used to load and execute the contents of an external file. I had found my hook. :smile:

The typical usage model is to add a SqlFile() call as part of the Up() method, with the primary input being the path to the file.

While this solved the interaction with the migration pipeline, I was loathe to add line-after-line-after-line of SqlFile() calls, since this would complicate the migration file regeneration workflow I have become so accustomed to using, i.e., Part 1.

Rather than just a single file reference, I had to figure out a convenient way to pass a list of files.

There's about 40-gazillion approaches to managing lists of files:

• Leverage the built-in file system folder construct
• Use a simple text file with a one-file-per-line approach
• Use a more structured file, e.g., Xml, Json, etc.

They're in that order on purpose ... because I ended up trying them all before settling on Xml. :wink:

Turn right at the next tangent ...
Before taking about the structure and contents of the file, I need to explain a little bit some usage decisions I made.

Back to the wand-waving, I wanted to have a very simple solution. Following is the single line of code to add to the bottom of the Up() method:

this.SqlFolder(MigrationDirection.Up);


And here's what needs to get added to the top of the Down() method:

this.SqlFolder(MigrationDirection.Down);


That's it.

The recommendation for placing these commands at the top/bottom of the respective methods is a requirement of the backing data store, e.g., when creating a view that references a table, it's kinda sorta important that the table first exists. :wink:

We now return you to your regularly scheduled programming ...
As mentioned above, I started with using a folder as the containment structure. That was before realizing that if I was executing some external files as part of the Up() method, at some point I was probably going to need to unwind that operation as part of the Down() method. This lead to the Duh! moment of understanding that I had up files and down files and I had to somehow differentiate.

A bit more mulling and experimentation and I settled on using an Xml file named Migration.xml, with the following structure:

<?xml version="1.0" encoding="utf-8" ?>
<Migration Name="SProcsV1" Description="">
<Up>
<File Name="Up File 1" />
<File Name="Up File 2" />
<File Name="Up File n" />
</Up>
<Down>
<File Name="Down File 1" />
<File Name="Down File 2" />
<File Name="Down File n" />
</Down>
</Migration>


(The Up and Down elements with n number of File children elements are required, but the Name and Description attributes are optional.)

DEE-tails ... SCHMEE-tails ... I just wanna know if it works ...
(Sure ... you say that now ... but at 3am on Sunday morning with the CTO expecting a Monday morning launch ... you'll probably thank me for a wee bit more detail.:sunglasses:)

SqlFolder() is a DbMigration extension method with the following declaration:

/// <summary>
/// This method will attempt to locate a folder name (defined by the aSqlFolder
/// parameter) which is in the aRootFolder folder. If the requested folder is
/// located, an xml file (defined in the aMigrationManifestFile parameter) will be
/// loaded and the list of File entries of the \Migration\<aDirection> element will
/// be executed using the DbMigration.SqlFile() method.
/// </summary>
/// <param name="aDirection"></param>
/// <param name="aSqlFolder"></param>
/// <param name="aSuppressTransaction"></param>
/// <param name="aBaseDirectory"></param>
/// <param name="aMigrationManifestFile"></param>
/// <param name="aRootFolder"></param>
public static void SqlFolder(this DbMigration aMigration, MigrationDirection aDirection, string aSqlFolder = null, bool aSuppressTransaction = false, string aBaseDirectory = null, string aMigrationManifestFile = C_MigrationManifestFile, string aRootFolder = C_DefaultRootFolder)


The default behavior (all override-able) is as follows:

• In the same project that hosts your migration files is a sub-folder named SQL
• That folder has a sub-folder that matches the name of your migration file (without the timestamp prefix)
• In that folder can be found a Migration.xml file
• Also in that folder are all the files referenced in Migration.xml
• You have added the this.SqlFolder(MigrationDirection.Up); and this.SqlFolder(MigrationDirection.Down); calls in the appropriate places in the migration file

The optional argument values will resolve to the following defaults:

 Parameter Default aSqlFolder aMigration.GetType().Name aBaseDirectory RelativeSearchPath or BaseDirectory aMigrationManifestFile Migration.xml aRootFolder SQL

The sample project referenced below is self-contained, shows the canonical usage, and includes the standalone SqlFolderMigration.cs code file that you can include in your project(s) as appropriate. There's even a bunch of unit tests. :wink:

Dirty Harry is iconic!!!
A man's ...... GOT ...... to know ....... his limitations ... and lucky for me, I've got lots of friends that don't hesitate to keep reminding me of mine! :smile:

The SqlFolder functionality has a couple of limitations that merit additional clarification:

 Limitation Description Auto-Migration and source files If you are never ever possibly ever never ever going to combine SqlFolder and Auto-Migration, skip ahead to the next section. The other 99.99% of us ... please continue reading. :smirk:  I typically keep my EF Application Context-related code in its' own project. As a result, the migrations are contained within that project AND compiled into the resulting binary. This means that Auto Migration will always be able to find its' instructions.   Unfortunately, the standalone SQL files - while physically situated within both the project as well as file system structures - are not included as part of the binary. NOTE: This was a conscious decision based on a) deciding to leverage the built-in SqlFile() helper method, and b) not wanting to potentially bloat the binary size with single-use resources, with the commensurate performance impact that may cause. I'm somewhat chagrined to admit that I was using this functionality for a number of weeks before I first enabled Auto Migration and was then unpleasantly surprised when I broke the build server unit tests due to "missing files". The solution - (of course there's a solution! :smile:) - is to set the Copy to Output Directory build option to Copy Always or Copy if newer for both the Migration.xml and all SQL files. Of course, there's no such thing as a free lunch, so a different set of issues arises from automatically adopting this strategy and then packaging the SQL content as part of the "Production" deployable. From a Security perspective, it is always better to include less rather than more, but unfortunately it is not possible to set the Copy to Output Directory option based on build-type and/or destination. (This is an important consideration in any Production environment where SQL logic and/or configuration settings may contain confidential information AND access to web application intrinsics is managed differently on the web servers than it is on the database server.) One possible solution is to modify the Up() method of the migration class to remove the files once the SqlFolder(MigrationDirection.Up) operation has completed. But do this, and now you're opening a can of worms with regard to file system modification (as distinct from simply reading the file contents) and the whole thing slowly starts slipping back towards ComplexityVille. :smirk: The approach I took was to "pop the stack" up one level and turn this into a process problem instead of just being a technical one: for applications where security concerns are trés important for Production environments, I make a conscious decision to disable the MigrateDatabaseToLatestVersion initializer and to include a build task to delete the SQL content sub-folders prior to initiating the deployment. But what works for me might not work for you ... so YMMV! Keeping the Migration.xml file in-sync with new files Since I had already adopted a folder-based container relationship, I thought long-&-hard about just having an auto-discovery process. I then realized that this would mean I had to have some other mechanism for differentiating between Up-files and Down-files. Why not impose an Up/Down sub-folder hierarchy? Starting to slide down a slippery slope ... I figured that was just shifting complexity instead of eliminating it. Since there is already a requirement that the Migration.xml file adhere to a known structure, let’s turn a lemon into lemonade: rather than being just a maintenance burden, it now all of a sudden can become a tool for self-documentation. Ship it! :smirk: It could even serve double-duty as the source input for database testing, external static analysis functionality, and maybe a couple of other of cool points of extension To be fair, it's not like it's truly complicated to maintain, just one more task to keep track of. Kludgey support for unit testing This is yet another Auto Migration related issue, but strangely, unit testing is one of the best excuses to use Auto Migration anyway so I had to figure out a solution. I frequently use LocalDB for unit testing and just as frequently implement a create/delete approach to the database for important test runs, e.g., gated check-ins. The problem relates to needing to locate the required SQL files when creating the database. The SqlFolder() method is an extension method which lives in the SqlFolderMigrationHelpers static class. This class also exposes the C_BaseDirRelativePathForUnitTests static variable (defaulted to null). I use MSTest, but most of the other unit testing frameworks offer a similar capability which permits execution of functionality at the beginning of … some phase, e.g., Test Run, Test Class, Test Method, etc. MSTest uses the AssemblyInitialize attribute to invoke code when a test assembly is first loaded. I attach that to an AssemblySetup() method. Here’s what mine looks like:   [AssemblyInitialize] public static void AssemblySetup(TestContext aTestContext) {   // Set the DataDirectory value so that LocalDB can find the MDF.   AppDomain.CurrentDomain.SetData("DataDirectory",                   AppDomain.CurrentDomain.BaseDirectory);     // When Auto-Migration is set, the SqlFolderMigration classes   // need a valid reference to the BaseDirectory so that the SQL   // folder can be located. Make this relative to the ./Bin folder   // of the Unit Test assembly.   SqlFolderMigrationHelpers.C_BaseDirRelativePathForUnitTests =                Path.Combine(AppDomain.CurrentDomain.BaseDirectory,                         "..\\..\\..\\Core\\Data");   Depending on your solution/projects structure, the actual pathing will likely require a wee bit of experimentation, but luckily this only needs to be done once. (Twice if you count the build server! :smirk:) Manual insertion into the Up()/Down() methods IMO, this is the biggest limitation.  But since I also think it's the easiest one to get used to … maybe they cancel each other out. Here’s the scenario. You’ve followed the guidelines, i.e., created the “SQL” folder and then the folder based on the migration name and then the Migration.xml file and then a bunch of sprocs and views and stuff and finished up by inserting the appropriate SqlFolder() calls into the Up()/Down() methods. Yippee!!! Call it a day … go have a beer! :sunglasses: N days later, you’ve made some model changes and now you want to re-create/execute the migration script. No big deal … you say … I’ll just UD0 then AMS then UD and everything will just work … which you then proceed to do. You launch your app and a browser to test it and … DANGER DANGER WILL ROBINSON … all Hades breaks loose! :scream: Speaking from experience ... ahem!!! … (and it will only take you 3 or 4 times before you instantly recognize the problem) … you do a , open up the migration file, and paste in the code snippet which you always have at the ready because you’ve done this so many times before!!! Ta Da! You’re back in business! :sunglasses:

To Do ...
I've been waiting for EF7 to Solidifilize to see if there have been any significant changes to the DbMigration work flow.

Presuming that there are not ... my goal is to figure out an elegant approach to offer an event-based model for signalling completion of important phases in the migration cycle. The intent is to hook into that flow and be able to inject/add elements into the pipeline.

If I can figure out a suitable approach that will satisfy both EF6 and EF7 functionality, I'll submit a pull request to get this functionality incorporated into the core offerings.

In the meantime, adding the SqlFolder() extension method invocations as the final operation in the Up() and the first operation in the Down() methods isn't too horrible a price to pay.

Happy coding ... :wink:

SC

#### Source Code & Examples

Comments powered by Disqus