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