###### 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="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:

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