Entity framework 6; -Script for Migrate.exe (Update)

After Enity Framework 5, 6 came along and so the old EntityFramework.Commandline V5 needed an update. The code which is provided as-is and without any warranty can found here is;

• compiled against Entity Framework 6.0.0.0 (Adding the new "contextAssemblyName")
• targeting .net 4.5
• freed of a few bugs;
  • Putting .dll after the "assemblyName" is handled transparently
  • MultiShotMigration is a boolean
  • SQL script printing order is corrected

As a general quick try;

• Copy the dll's found in \packages\EntityFramework.6.0.X\lib\net45 to the build output folder of your database project (for example \Database\bin\Debug)
• Copy the EntityFramework.Commandline files (\EntityFramework.Commandline\bin\Debug) to the build output folder of your database project (for example \Database\bin\Debug)
• Open a command window on the same location (build output folder of your database project)
• Execute the command below;

EntityFramework.Commandline -ConnectionString="Server=.;Database=DbName;Integrated Security=True;" -ConnectionProviderName="System.Data.SqlClient" -AssemblyName="YourDatabaseAssemblyName" -Verbose -Unattended

Entity framework 5; -Script for Migrate.exe

An Entity Framework 6 update is available here.

My current project supports an extensive continuous build system with distribution to a large number of end-users. To be able to track and control this process coordinators preferred SQL-scripts over the "new & wild" concept of migration classes enabling them to verify generated scripts and use their trusted proven infrastructure.
Unfortunately, unlike the package manager, the command line migration.exe doesn't support the -script option. Knowing it was possible from within Visual Studio I set off on a decompilation & exploration journey through power shell scripts and entity framework dll's.

Once everything was clear I built a console application directly talking to the scripting functionality in the EntityFramework.dll   You can download the source here. Do remember, all the code is provided as-is and without any warranty.

Special thanks to NDesk for the Options class, it works like a charm!

Explain IT to the business: Threading

Imagine your program to be a factory. And in that factory you got a team of leprechauns to get a job done. Well those leprechauns are threads. And as in real life, engaging more leprechauns will (probably) get the job done faster. Since threads and leprechauns don't require actual pay, business managers often ask; "Then why not simply add more leprechauns?". Well, when leprechauns team up they need someone to manage their business. And as the business manager knows this is often a complicated undertaking. So imagine the complexity of programming such a leprechaun manager. This is why most programs are sole proprietorships. All being said, investing in a leprechaun manager might be a wise choice (sure the business manager will agree) but do accept that one such manager can only lead so many leprechauns and that for a certain job only so many leprechauns can be engaged before they start running in each other's way.
Conclusion; threads are a lot like leprechauns, magical when balanced and managed properly.
Next up: Inversion of control

Explain IT to the business

As a software architect, and especially during periods of stress you might find yourself in a position where you have to explain a technical detail to an involved business party. They won't help you program but they might need to be reassured why we decided to invest time (and therefore money) in something they can't grasp. I often found this more difficult than previously imagined so I decided to share some explanations. First off is "Threading". If you're a business fellow and you're still riddled or an architect and you find something wrong or too far fetched please let me know, I'm sure we'll align business and IT after all :)

Techdays 2013, The new oil is unprotected

When you dive into a dark theater on the first sunny day after a the darkest winter you've ever seen it better be good, and it was. First day schedules deep dives on ALM, security, SharePoint and Exchange. Not my favorites at first glance until I admitted I need the ALM testing cycle and I figured out there's only one way to learn security and that's how to break in. And which techy didn't dream of being the authority deifying hacker, breaking through NSA's security while being distracted by incredibly gorgeous women. I admit I did. But I never did see myself as an actual hacker. Somehow it all seemed too mathematical  unrealistic and complicated. And after seeing this morning sessions, I wish it was. I knew today's hackers were actually trained professionals, often military but I at least suspected it to be mathematical, unrealistic and complicated and I learned it's not.

Paula "never thrust a blond" Januszkiewicz, ruled on techdays 2013!

How do they do it? Well first they need to get their foot through the door. This often requires some "social engineering" which might feel like cheating to the hard core engineers but admit; how hard can it be to get a random secretary to open a perfectly honest, but urgent looking word or excel file. And rest assure, after this little mistake the floodgates are open. I won't get into details but they will. Enjoy I did.

On a final note, don't (always) blame the Chinese. Yes they are the guilty ones in many series and in the infamous Mandiant report. But many nations keep binary soldiers (Remember Stuxnet ?) and they should. Information is the new oil and as it seems, it's virtually unprotected.

Entity framework 5 Migrations, Multiple migrations.

When you run the Update-Database command (Update-Database -ConfigurationTypeName MyDbConfig) migrate.exe checks which migrations still need to be executed on the target database by comparing the migration scripts present to the subscriptions in the dbo._MigrationHistory table of the target database. By default all migrations are then applied. 

Take note that each of those migrations runs in it's own transaction. This could get you into trouble when you deploy a single release of your application with multiple migration files. If the last migration should fail, all previous migrations changes are kept, leaving your model and database unsynchronised. You can avoid this by removing all migrations and their corresponding entries in the dbo._MigrationHistory table and creating a big single migration for your application's release.

If you did get into trouble you can always rollback your application and revert to the corresponding migration. When reverting, the "Down" method implementation of the migration is used. This means that if you edited the "Up" as explained in Seed & Sql and forgot to implement the rollback in "Down" you might still be in trouble.

PM> Update-Database -ConfigurationTypeName MyDbConfig -targetMigration "Version2"

Specify the '-Verbose' flag to view the SQL statements being applied to the target database.

Reverting migrations: [000000000000000_Version3].

Reverting code-based migration: 000000000000000_Version3.

PM> 

Entity framework 5 Migrations,Seed & Sql

When executing Entity Framework 5 migrate the executable does several things;

• Check current migrations vs the build
• Execute all migrations that aren't already executed on the target database
• Execute the seed method

This seed method is what we'll focus on. You can access the method by overriding the seed method of the DbMigrationsConfiguration of your project.

Code Snippet

15. Protected Overrides Sub Seed(context As PocModel)        
16.     MyBase.Seed(context)
17. End Sub

Sorry for the vb.net

Seed will be executed regardless of the state of the target database.  This could be useful to run checks, log, send mails... Be careful however to use the seed method to alter the database (which is tempting considering you get the context model). Migrations and Seed each run in a different transaction. This means trouble if one of them fails and doesn't roll back the other. If you want to alter the database you should do so by adding code int the Up and Down method of the corresponding migration.

Code Snippet

1. Imports System
2. Imports System.Data.Entity.Migrations
3.  
4. Namespace Schema
5.     Partial Public Class Migration_42
6.         Inherits DbMigration
7.  
8.         Public Overrides Sub Up()
9.             'Here
10.             Sql("ALTER TABLE dbo.MeaningOfLife DROP COLUMN Famine")
11.         End Sub
12.  
13.         Public Overrides Sub Down()
14.             'And here
15.         End Sub
16.     End Class
17. End Namespace

Sorry for the vb.net

Take into account that executing sql like this is against the spirit of Entity Framework. All table alterations should be done by editing the domain and migrating the database. Unfortunately we don't always have the luxury to adhere to this philosophy. Sometimes legacy columns need to be addressed or we might really need that stored procedure. To accommodate this you'll find a helper class below which reads embedded sql files and splits them into single commands which you can then execute as shown above. Code is provided as-is without express or implied warranty of any kind.

Code Snippet

1. Imports System.IO
2. Imports System.Text
3. Imports System.Data.Entity.Migrations
4.  
5. Public Class SQLScriptHelper
6.  
7.     Public Function GetCommandsFromEmbeddedFile(uri As String) As List(Of String)
8.         Return ReadCommandsFromEmbeddedFile(uri)
9.     End Function
10.  
11.     Public Function ReadCommandsFromEmbeddedFile(uri As String) As List(Of String)
12.         Dim commands = New List(Of String)
13.         Dim command = New StringBuilder()
14.         Dim line As String = "/****** Initial ******/"
15.         Using stream As IO.Stream = Me.GetType().Assembly.GetManifestResourceStream("YourResourceNamespace." + uri)
16.             Using reader As StreamReader = New StreamReader(stream)
17.                 While Not reader.EndOfStream
18.                     line = reader.ReadLine()
19.                     If line.Equals("GO") Then
20.                         commands.Add(command.ToString())
21.                         command.Clear()
22.                     Else
23.                         command.AppendLine(line)
24.                     End If
25.                 End While
26.             End Using
27.         End Using
28.         Return commands
29.     End Function
30.  
31. End Class

Sorry for the vb.net

Entity framework 5 Migrations, Tips & Tricks

1. The migrations itself are transactional (so when everything goes wrong, you get another shot).
2. The migrations reinsert the data after the upgrade.
3. When adding a required property to an existing entity a not-null column is added to the table. Existing records get a default value.
4. When making an existing property required make sure all existing records  have a value for the soon to be not null column. If not the upgrade will result in an error.

Using Entity framework 5’s migrate.exe

Entity framework 5 provides migrate.exe to migrate a database using an external process. When implementing this I encountered several rather unexpected problems which I’ll list up for you, hoping you don’t have to suffer the same frustration.

1. When you used Nuget to integrate EF5 into your project you’ll find the Register.exe here {Project-root}/packages/EntityFramework.5.0.0/lib/net40/
2. When using the net40 you’ll need to rename the configuration file to migrate.exe.config (.net 4.5 should make this step unnecessary)
3. Now you’ll need to call register.exe on the .dll (or .exe) file containing your migrations .cs files. You can either copy the register.exe, config file and EntityFramework.dll to the target directory or you can use the /startUpDirectory parameter. Migrate.exe has a few options:

• /startupConfigurationFile : I tried this approach first being sure of my configuration file. The attempt failed as EF5 failed to get the connection string resulting in the error below:

ERROR: An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

• /connectionString: So I tried adding the connection string but his resulted in a NullReferenceException.
• /connectionProviderName: The NullReferenceException you can solve by adding the connectionProviderName
• /verbose: Writes comments and exception messages to the screen, always usefull

Resulting in a call that looked like this:

Migrate.exe Target.dll /connectionString="Data Source=Server\SQLSERVERNAME; Initial Catalog=DbName; Persist Security Info=True; User ID=sa;Password=xxxx" /connectionProviderName:"System.Data.SqlClient" /verbose

Hope it helps,
Greg