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!

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