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