At the end of November we released Beta 1 of Code First Migrations. At the time we released Code First Migration Beta 1 we also announced that we would be rolling the migrations work into the main EntityFramework NuGet package and releasing it as EF 4.3.

Today we are making Beta 1 of EF 4.3 available. This release also includes a number of bug fixes for the DbContext API and Code First.

We are planning for this to be the last pre-release version of migrations and our next release will be the final RTM of EF 4.3.

 

What Changed

This release has been primarily about integrating migrations into the EntityFramework NuGet package, improving quality and cleaning up the API surface ready to RTM.

Notable changes to Code First Migrations include:

• New Enable-Migrations command. You now use the Enable-Migrations command to add the Migrations folder and Configuration class to your project. This command will also automatically fill in your context type in the Configuration class (provided you have a single context defined in your project).
• Update-Database.exe command line tool. In addition to the power shell commands this release also includes Update-Database.exe which can be used to run the migrations process from a command line. You can find this tool in the ‘packages\EntityFramework.4.3.0-beta1\tools\’ under your solutions directory. The syntax for this command line tool is very similar to the Update-Database power shell command. Run ‘Update-Database /?’ from a command prompt for more information on the syntax.
• Migrations database initializer. This release includes the System.Data.Entity.MigrateDatabaseToLatestVersion database initializer that can be used to automatically upgrade to the latest version when your application launches.
• Complete xml documentation. This release now includes xml documentation (IntelliSense) for the migrations API surface.
• Improved logging. If you specify the –Verbose flag when running commands in Package Manager Console we now provide more information to help with debugging.

Other notable changes in EF 4.3 include:

• Removal of EdmMetadata table. If you allow Code First to create a database by simply running your application (without using Migrations) the creation is now performed as an Automatic Migration. You can then enable migrations and continue evolving your database using migrations.
• Bug fix for GetDatabaseValues. In earlier releases this method would fail if your entity classes and context were in different namespaces. This issue is now fixed and the classes don’t need to be in the same namespace to use GetDatabaseValues.
• Bug fix to support Unicode DbSet names. In earlier releases you would get an exception when running a query against a DbSet that contained some Unicode characters. This issue is now fixed.
• Data Annotations on non-public properties. Code First will not include private, protected or internal properties by default. If you manually include them in your model Code First used to ignore any Data Annotations on those members. This is now fixed and Code First will process the Data Annotations.
• More configuration file settings. We’ve enabled more Code First related settings to be specified in the App/Web.config file. This gives you the ability to set the default connection factory and database initializers from the config file. You can also specify constructor arguments to be used when constructing these objects. More details are available in the EF 4.3 Configuration File Settings blog post.

 

Getting Started

You can get EF 4.3 Beta 1 by installing the latest pre-release version of the EntityFramework NuGet package.

You will need NuGet 1.6 installed and specify the –IncludePrerelease flag at the Package Manager Console to get this pre-release version. Pre-release packages can only be installed from the Package Manager Console.

There are two walkthroughs for EF 4.3 Beta 1. One focuses on the no-magic workflow that uses a code-based migration for every change. The other looks at using automatic migrations to avoid having lots of code in you project for simple changes.

• EF 4.3 Beta 1: Code-Based Migrations Walkthrough
• EF 4.3 Beta 1: Automatic Migrations Walkthrough

 

Upgrading From ‘Code First Migrations Beta 1’

If you have Code First Migrations Beta 1 installed you will need to uninstall the EntityFramework.Migrations package by running ‘Uninstall-Package EntityFramework.Migrations’ in Package Manager Console.

You can then install EF 4.3 Beta 1 using the ‘Install-Package EntityFramework –IncludePrerelease’ command.

You will need to close and re-open Visual Studio after installing the new package, this is required to unload the old migrations commands.

 

RTM Timeline

We are planning for this to be the last pre-release version of migrations and are still on-track to get a full supported, go-live, release of EF 4.3 published this quarter (first quarter of 2012).

 

MSDeploy Provider Update

We originally blogged about our plans to deliver an MSDeploy provider that could be used to apply migrations to a remote server. After many long hours iterating on this and working with the MSDeploy team we’ve concluded that we can’t deliver a good MSDeploy story for Migrations at this stage.

The primary issues arise from us needing to execute code from your application assemblies on the remote server, in order to calculate the SQL to apply to the database. This is a requirement that other MSDeploy providers have not had in the past. We are going to continue working with the MSDeploy team to see if we can deliver something in the future, but unfortunately we won’t be shipping an MSDeploy provider in the immediate future.

If you are able to connect to the remote database from the machine you are deploying from, then you can use the Update-Database.exe command line tool to perform the upgrade process. You can also use the System.Data.Entity.Migrations.DbMigrator class to write your own code that performs the migration process.

 

EF 5.0 (Enum support is coming… finally!)

We’ve been working on a number of features that required updates to some assemblies that are still part of the .NET Framework. These features include enums, spatial data types and some serious performance improvements.

As soon as the next preview of the .NET Framework 4.5 is available we will be shipping EF 5.0 Beta 1, which will include all these new features.

 

Support

This is a preview of features that will be available in future releases and is designed to allow you to provide feedback on the design of these features. It is not intended or licensed for use in production. If you need assistance we have an Entity Framework Pre-Release Forum.

 

ADO.NET Entity Framework Team

We have released the final preview of the Code First Migrations work as part of Entity Framework 4.3 Beta 1.

This post will provide an overview of the functionality that is available inside of Visual Studio for interacting with migrations. We will focus on the code-based workflow for using migrations. In this workflow each change is written out to a code-based migration that resides in your project.

There is a separate EF 4.3 Beta 1: Automatic Migrations Walkthrough that shows how this same set of changes can be applied using a mixture of code-based and automatic migrations.

This post assumes you have a basic understanding of Code First, if you are not familiar with Code First then please complete the Code First Walkthrough.

 

Building an Initial Model

Before we start using migrations we need a project and a Code First model to work with. For this walkthrough we are going to use the canonical Blog and Post model.

1. Create a new MigrationsCodeDemo Console application.
   .
2. Add the latest prerelease version of the EntityFramework NuGet package to the project.
   • Tools –> Library Package Manager –> Package Manager Console.
   • Run the ‘Install-Package EntityFramework –IncludePrerelease’ command.
     .
3. Add a Model.cs class with the code shown below. This code defines a single Blog class that makes up our domain model and a BlogContext class that is our EF Code First context.
   
   

   using System.Data.Entity;
   using System.Collections.Generic;
   using System.ComponentModel.DataAnnotations;
   using System.Data.Entity.Infrastructure;
   
   namespace MigrationsCodeDemo
   {
       public class BlogContext : DbContext
       {
           public DbSet<Blog> Blogs { get; set; }
       }
   
       public class Blog
       {
           public int BlogId { get; set; }
           public string Name { get; set; }
       }
   }

Enabling Migrations

Now that we have a Code First model let’s enable Migrations to work with our context.

1. Run the ‘Enable-Migrations’ command in Package Manager Console.
   .
2. This command has added a Migrations folder to our project. At the moment this folder just contains a single Configuration class. The Configuration class allows you to configure how Migrations behaves for your context.
   Because there is just a single Code First context in your project, Enable-Migrations has automatically filled in the context type in the base class and Seed method for you.

   namespace MigrationsCodeDemo.Migrations
   {
       using System;
       using System.Data.Entity;
       using System.Data.Entity.Migrations;
       using System.Linq;
   
       internal sealed class Configuration : DbMigrationsConfiguration<MigrationsCodeDemo.BlogContext>
       {
           public Configuration()
           {
               AutomaticMigrationsEnabled = false;
           }
   
           protected override void Seed(MigrationsCodeDemo.BlogContext context)
           {
               //  This method will be called after migrating to the latest version.
   
               //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
               //  to avoid creating duplicate seed data. E.g.
               //
               //    context.People.AddOrUpdate(
               //      p => p.FullName,
               //      new Person { FullName = "Andrew Peters" },
               //      new Person { FullName = "Brice Lambson" },
               //      new Person { FullName = "Rowan Miller" }
               //    );
               //
           }
       }
   }

Our First Migration

Code First Migrations has two commands that you are going to become familiar with. Add-Migration will scaffold the next migration based on changes you have made to your model. Update-Database will apply any pending changes to the database.

1. We haven’t generated any migrations yet so this will be our initial migration that creates the first set of tables (in our case that’s just the Blogs table). We can call the Add-Migration command and Code First Migrations will scaffold a migration for us with its best guess at what we should do to bring the database up-to-date with the current model.
   
   The Add-Migration command allows us to give these migrations a name, let’s just call ours MyFirstMigration.
   • Run the ‘Add-Migration MyFirstMigration’ command in Package Manager Console.
     .
2. In the Migrations folder we now have a new MyFirstMigration migration. The migration is pre-fixed with a timestamp to help with ordering.
   

   namespace MigrationsCodeDemo.Migrations
   {
       using System.Data.Entity.Migrations;
       
       public partial class MyFirstMigration : DbMigration
       {
           public override void Up()
           {
               CreateTable(
                   "Blogs",
                   c => new
                       {
                           BlogId = c.Int(nullable: false, identity: true),
                           Name = c.String(),
                       })
                   .PrimaryKey(t => t.BlogId);
               
           }
           
           public override void Down()
           {
               DropTable("Blogs");
           }
       }
   }

3. We could now edit or add to this migration but everything looks pretty good. Let’s use Update-Database to apply this migration to the database.
   • Run the ‘Update-Database’ command in Package Manager Console.
     .
4. Code First Migrations has now created a MigrationsCodeDemo.BlogContext database on our local SQL Express instance. We could now write code that uses our BlogContext to perform data access against this database.
   
   

 

Customizing Migrations

So far we’ve generated and run a migration without making any changes. Now let’s look at editing the code that gets generated by default.

1. It’s time to make some more changes to our model, let’s introduce a Blog.Rating property and a new Post class.
   

   public class Blog
   {
       public int BlogId { get; set; }
       public string Name { get; set; }     
       public int Rating { get; set; }
       public List<Post> Posts { get; set; }
   }
   
   public class Post
   {
       public int PostId { get; set; }
       [MaxLength(200)]
       public string Title { get; set; }
       public string Content { get; set; }
   
       public int BlogId { get; set; }
       public Blog Blog { get; set; }
   }  
   

2. Let’s use the Add-Migration command to let Code First Migrations scaffold its best guess at the migration for us. We’re going to call this migration MySecondSetOfChanges.
   • Run the ‘Add-Migration MySecondSetOfChanges’ command in Package Manager Console.
     .
3. Code First Migrations did a pretty good job of scaffolding these changes, but there are some things we might want to change:
   • First up, let’s add a unique index to Posts.Title column.
   • We’re also adding a non-nullable Blogs.Rating column, if there is any existing data in the table it will get assigned the CLR default of the data type for new column (Rating is integer, so that would be 0). But we want to specify a default value of 3 so that existing rows in the Blogs table will start with a decent rating.
     
     (These changes to the scaffolded migration are highlighted)
     
     

     namespace MigrationsCodeDemo.Migrations
     {
         using System.Data.Entity.Migrations;
     
         public partial class MySecondSetOfChanges : DbMigration
         {
             public override void Up()
             {
                 CreateTable(
                     "Posts",
                     c => new
                         {
                             PostId = c.Int(nullable: false, identity: true),
                             Title = c.String(maxLength: 200),
                             Content = c.String(),
                             BlogId = c.Int(nullable: false),
                         })
                     .PrimaryKey(t => t.PostId)
                     .ForeignKey("Blogs", t => t.BlogId, cascadeDelete: true)
                     .Index(t => t.BlogId)
                     .Index(p => p.Title, unique: true);
     
                 AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
             }
     
             public override void Down()
             {
                 DropIndex("Posts", new[] { "BlogId" });
                 DropForeignKey("Posts", "BlogId", "Blogs");
                 DropColumn("Blogs", "Rating");
                 DropTable("Posts");
             }
         }
     }

4. Our edited migration is looking pretty good, so let’s use Update-Database to bring the database up-to-date. This time let’s specify the –Verbose flag so that you can see the SQL that Code First Migrations is running.
   • Run the ‘Update-Database –Verbose’ command in Package Manager Console.

 

Data Motion / Custom SQL

So far we have just looked at migration operations that don’t change or move any data, now let’s look at something that needs to move some data around. There is no native support for data motion yet, but we can run some arbitrary SQL commands at any point in our script.

1. Let’s add a Post.Abstract property to our model. Later, we’re going to pre-populate the Abstract for existing posts using some text from the start of the Content column.
   

   public class Post
   {
       public int PostId { get; set; }
       [MaxLength(200)]
       public string Title { get; set; }
       public string Content { get; set; }
       public string Abstract { get; set; }     
   
       public int BlogId { get; set; }
       public Blog Blog { get; set; }
   }

2. Let’s use the Add-Migration command to let Code First Migrations scaffold its best guess at the migration for us. We’re going to call this migration AddPostAbstract.
   • Run the ‘Add-Migration AddPostAbstract’ command in Package Manager Console.
     
3. The generated migration takes care of the schema changes but we also want to pre-populate the Abstract column using the first 100 characters of content for each post. We can do this by dropping down to SQL and running an UPDATE statement after the column is added.
   

   namespace MigrationsCodeDemo.Migrations
   {
       using System.Data.Entity.Migrations;
       
       public partial class AddPostAbstract : DbMigration
       {
           public override void Up()
           {
               AddColumn("Posts", "Abstract", c => c.String());
   
               Sql("UPDATE Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
           }
           
           public override void Down()
           {
               DropColumn("Posts", "Abstract");
           }
       }
   }

4. Our edited migration looks good, so let’s use Update-Database to bring the database up-to-date. We’ll specify the –Verbose flag so that we can see the SQL being run against the database.

   • Run the ‘Update-Database –Verbose’ command in Package Manager Console.

 

Migrate to a Specific Version (Including Downgrade)

So far we have always upgraded to the latest migration, but there may be times when you want upgrade/downgrade to a specific migration.

1. Let’s say we want to migrate our database to the state it was in after running our MyFirstMigration migration. We can use the –TargetMigration switch to downgrade to this migration.
   • Run the ‘Update-Database –TargetMigration:"MyFirstMigration"’ command in Package Manager Console.

This command will run the Down script for our AddBlogAbstract and MySecondSetOfChanges migrations. If you want to roll all the way back to an empty database then you can use the Update-Database –TargetMigration:"0" command.

 

Getting a SQL Script

Now that we have performed a few iterations on our local database let’s look at applying those same changes to another database.

If another developer wants these changes on their machine they can just sync once we check our changes into source control. Once they have our new migrations they can just run the Update-Database command to have the changes applied locally. However if we want to push these changes out to a test server, and eventually production, we probably want a SQL script we can hand off to our DBA.

1. Now let’s run the Update-Database command but this time we’ll specify the –Script flag so that changes are written to a script rather than applied. We’ll also specify a source and target migration to generate the script for. We want a script to go from an empty database (migration “0”) to the latest version (migration “AddPostAbstract”).
   Note: If you don’t specify a target migration, Migrations will use the latest migration as the target.
   
   • Run the ‘Update-Database -Script -SourceMigration:"0" -TargetMigration:"AddPostAbstract"’ command in Package Manager Console.
     .
2. Code First Migrations will run the migration pipeline but instead of actually applying the changes it will write them out to a .sql file for you. Once the script is generated, it is opened for you in Visual Studio, ready for you to view or save.

NOTE: There are a number of bugs in the scripting functionality in EF 4.3 Beta1 that prevent you generating a script starting from a migration other than an empty database. These bugs will be fixed in the final RTM.

 

Summary

In this walkthrough you saw how to scaffold, edit and run code-based migrations to upgrade and downgrade your database. You also saw how to get a SQL script to apply migrations to a database.

Rowan Miller
Program Manager
ADO.NET Entity Framework

We have released the final preview of the Code First Migrations work as part of Entity Framework 4.3 Beta 1.

This post will provide an overview of the functionality that is available inside of Visual Studio for interacting with migrations. We will focus on the workflow that combines automatic and code-based migrations. In this workflow most changes can be automatically calculated and applied. More complex changes are written out to code-based migrations that reside in your project.

There is a separate EF 4.3 Beta 1: Code-Based Migrations Walkthrough that shows how this same set of changes can be applied using purely code-based migrations.

This post assumes you have a basic understanding of Code First, if you are not familiar with Code First then please complete the Code First Walkthrough.

 

Building an Initial Model

Before we start using migrations we need a project and a Code First model to work with. For this walkthrough we are going to use the canonical Blog and Post model.

1. Create a new MigrationsAutomaticDemo Console application.
   .
2. Add the latest prerelease version of the EntityFramework NuGet package to the project.
   • Tools –> Library Package Manager –> Package Manager Console.
   • Run the ‘Install-Package EntityFramework –IncludePrerelease’ command.
     .
3. Add a Model.cs class with the code shown below. This code defines a single Blog class that makes up our domain model and a BlogContext class that is our EF Code First context.
   
   

   using System.Data.Entity;
   using System.Collections.Generic;
   using System.ComponentModel.DataAnnotations;
   using System.Data.Entity.Infrastructure;
   
   namespace MigrationsAutomaticDemo
   {
       public class BlogContext : DbContext
       {
           public DbSet<Blog> Blogs { get; set; }
       }
   
       public class Blog
       {
           public int BlogId { get; set; }
           public string Name { get; set; }
       }
   }

Enabling Migrations

Now that we have a Code First model, let’s enable Migrations to work with our context.

1. Run the ‘Enable-Migrations’ command in Package Manager Console.
   .
2. This command has added a Migrations folder to our project. At the moment this folder just contains a single Configuration class. The Configuration class allows you to configure how migrations behaves for your context.
   Because there is just a single Code First context in your project, Enable-Migrations has automatically filled in the context type in the base class and Seed method for you.
   
   We’ll just edit the Configuration class to enable automatic migrations (highlighted below).
   
   

   namespace MigrationsAutomaticDemo.Migrations
   {
       using System;
       using System.Data.Entity;
       using System.Data.Entity.Migrations;
       using System.Linq;
   
       internal sealed class Configuration : DbMigrationsConfiguration<MigrationsAutomaticDemo.BlogContext>
       {
           public Configuration()
           {
               AutomaticMigrationsEnabled = true;
           }
   
           protected override void Seed(MigrationsAutomaticDemo.BlogContext context)
           {
               //  This method will be called after migrating to the latest version.
   
               //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
               //  to avoid creating duplicate seed data. E.g.
               //
               //    context.People.AddOrUpdate(
               //      p => p.FullName,
               //      new Person { FullName = "Andrew Peters" },
               //      new Person { FullName = "Brice Lambson" },
               //      new Person { FullName = "Rowan Miller" }
               //    );
               //
           }
       }
   }

 

Our First Automatic Migration

Code First Migrations has two commands that you are going to become familiar with. Add-Migration will scaffold a code-based migration based on changes you have made to your model. Update-Database will apply any pending changes to the database. We are going to avoid using Add-Migration (unless we really need to) and focus on letting Code First Migrations automatically calculate and apply the changes.

1. Let’s use Update-Database to get Code First Migrations to push our model to the database.
   • Run the ‘Update-Database’ command in Package Manager Console.
     .
2. Code First Migrations has now created a MigrationsAutomaticDemo.BlogContext database on our local SQL Express instance. We could now write code that uses our BlogContext to perform data access against this database. 
   
   

 

Our Second Automatic Migration

Let’s make another change and let Code First Migrations automatically push the changes to the database for us.

1. Let’s introduce a new Post class.
   

   public class Blog
   {
       public int BlogId { get; set; }
       public string Name { get; set; }    
    
       public List<Post> Posts { get; set; }
   }
   
   public class Post
   {
       public int PostId { get; set; }
       [MaxLength(200)]
       public string Title { get; set; }
       public string Content { get; set; }
   
       public int BlogId { get; set; }
       public Blog Blog { get; set; }
   } 

2. Let’s use Update-Database to bring the database up-to-date. This time let’s specify the –Verbose flag so that you can see the SQL that Code First Migrations is running.
   • Run the ‘Update-Database –Verbose’ command in Package Manager Console.

.

Adding a Code Based Migration

Now let’s look at something we might want to use a code-based migration for.

1. Let’s add a Blog.Rating property.
   

   public class Blog
   {
       public int BlogId { get; set; }
       public string Name { get; set; }
       public int Rating { get; set; }
   
   
       public List<Post> Posts { get; set; }
   }

2. We could just run Update-Database to push these changes to the database. However, were adding a non-nullable Blogs.Rating column, if there is any existing data in the table it will get assigned the CLR default of the data type for new column (Rating is integer, so that would be 0). But we want to specify a default value of 3 so that existing rows in the Blogs table will start with a decent rating.
   
   Let’s use the Add-Migration command to write this change out to a code-based migration so that we can edit it. The Add-Migration command allows us to give these migrations a name, let’s just call ours MyFirstCodeMigration.
   • Run the ‘Add-Migration MyFirstCodeMigration’ command in Package Manager Console.
     .
3. In the Migrations folder we now have a new MyFirstCodeMigration migration. The migration is pre-fixed with a timestamp to help with ordering. Let’s edit the generated code to specify a default value of 3 for Blog.Rating.
   
   The migration also has a code-behind file that captures some metadata. This metadata will allow Code First Migrations to replicate the automatic migrations we performed before this code-based migration. This is important if another developer wants to run our migrations or when it’s time to deploy our application.
   

   namespace MigrationsAutomaticDemo.Migrations
   {
       using System.Data.Entity.Migrations;
       
       public partial class MyFirstCodeMigration : DbMigration
       {
           public override void Up()
           {
               AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
           }
           
           public override void Down()
           {
               DropColumn("Blogs", "Rating");
           }
       }
   }

4. Our edited migration is looking pretty good, so let’s use Update-Database to bring the database up-to-date.
   • Run the ‘Update-Database’ command in Package Manager Console.

 

Back to Automatic Migrations

Fortunately we don’t have to keep using code-based migrations now, we can switch back to automatic migrations for our simpler changes. Code First Migrations will take care of performing the automatic and code-based migrations in the correct order based on the metadata it is storing in the code-behind file for each code-based migration.

1. Let’s add a Post.Abstract property to our model.

   public class Post
   {
       public int PostId { get; set; }
       [MaxLength(200)]
       public string Title { get; set; }
       public string Content { get; set; }
       public string Abstract { get; set; }     
   
       public int BlogId { get; set; }
       public Blog Blog { get; set; }
   }

2. Let’s use Update-Database to get Code First Migrations to push this change to the database using an automatic migration.

• Run the ‘Update-Database’ command in Package Manager Console.

 

Data Motion / Custom SQL

So far we have just looked at migration operations that can leave all the data in place, now let’s look at something that needs to move some data around. There is no native support for data motion yet, but we can run some arbitrary SQL commands at any point in our script.

We just added the Abstract column. Let’s pre-populate it for existing posts using text from the Content column.

1. Use the Add-Migration command to let Code First Migrations add an empty migration for us. We’re going to call this migration PopulatePostAbstract.
   (The migration will be empty because there are no pending model changes that haven’t been applied to the database)
   
   • Run the ‘Add-Migration PopulatePostAbstract’ command in Package Manager Console.
     
2. Update the migration to run some custom SQL that will populate the Abstract column.
   

   namespace MigrationsAutomaticDemo.Migrations
   {
       using System.Data.Entity.Migrations;
       
       public partial class PopulatePostAbstract : DbMigration
       {
           public override void Up()
           {
               Sql("UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
           }
           
           public override void Down()
           {
           }
       }
   }

3. Our edited migration looks good, so let’s use Update-Database to bring the database up-to-date. We’ll specify the –Verbose flag so that we can see the SQL being run against the database.

   • Run the ‘Update-Database –Verbose’ command in Package Manager Console.

 

Migrate to a Specific Version (Including Downgrade)

So far we have always upgraded to the latest migration, but there may be times when you want upgrade/downgrade to a specific migration.

1. Let’s say we want to migrate our database to the state it was in after running our MyFirstCodeMigration migration. We can use the –TargetMigration switch to downgrade to this migration. This is going to cause some columns that were added as part of an automatic migration to be dropped automatically on the way down. Code First Migrations won’t let this happen without you knowing about it, so we need to specify the –Force switch to acknowledge that we are OK with the potential data loss.
   • Run the ‘Update-Database –TargetMigration:"MyFirstCodeMigration" –Force’ command in Package Manager Console.

This command will run the Down script for our PopulatePostAbstract migration, then use the automatic pipeline to revert the addition of the Abstract column.

If you want to roll all the way back to an empty database then you can use the Update-Database –TagetMigration:"0" command.

 

Getting a SQL Script

Now that we have performed a few iterations on our local database let’s look at applying those same changes to another database.

If another developer wants these changes on their machine they can just sync once we check our changes into source control. Once they have our new migrations they can just run the Update-Database command to have the changes applied locally. However if we want to push these changes out to a test server, and eventually production, we probably want a SQL script we can hand off to our DBA.

1. Now let’s run the Update-Database command but this time we’ll specify the –Script flag so that changes are written to a script rather than applied. We’ll also specify a source migration to generate the script from. We want a script to go from an empty database (migration “0”) to the latest version.
   Note: You can also specify a target migration to generate a script to the database state at the end of a code-based migration. If you don’t specify a target migration, Migrations will use the latest version as the target (including any automatic migrations that have been applied since the last code-based migration).
   
   • Run the ‘Update-Database -Script -SourceMigration:"0"’ command in Package Manager Console.
     .
2. Code First Migrations will run the migration pipeline but instead of actually applying the changes it will write them out to a .sql file for you. Once the script is generated, it is opened for you in Visual Studio, ready for you to view or save.

NOTE: There are a number of bugs in the scripting functionality in EF 4.3 Beta1 that prevent you generating a script starting from a migration other than an empty database. These bugs will be fixed in the final RTM.

 

Summary

In this walkthrough you saw how to use automatic migrations to push model changes to the database. You saw how to scaffold and run code-based migrations when you need more control. You also saw how to upgrade and downgrade your database. Finally we looked at how to get a SQL script that represents the pending changes to a database.

Rowan Miller
Program Manager
ADO.NET Entity Framework

We recently released Entity Framework 4.3 Beta 1 which includes the ability to configure more DbContext and Code First related settings from your applications Web.config or App.config file.

 

These Settings are Optional

Code First and the DbContext API follow a ‘convention over configuration’ principle. All the settings discussed in this post have a default behavior, you only need to worry about changing the setting when the default no longer satisfies your requirements.

All of these settings can also be applied using code. The configuration file option allows these settings to be easily changed during deployment without updating your code.

 

The Entity Framework Configuration Section

In earlier versions of Entity Framework you could set the database initializer for a context using the appSettings section of the configuration file. In EF 4.3 we are introducing the custom entityFramework section to handle the new settings. Entity Framework will still recognize database initializers set using the old format, but we recommend moving to the new format where possible.

The entityFramework section was automatically added to the configuration file of your project when you installed the EntityFramework NuGet package.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, 
visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" 
      type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.3.0.0, 
Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>
</configuration>

Connection Strings

Setting a connection string for a context uses the same syntax as previous releases of Entity Framework.

Code First uses normal ADO.NET connection strings. For example:

<connectionStrings>
  <add name="BlogContext" 
        providerName="System.Data.SqlClient" 
        connectionString="Server=.\SQLEXPRESS;Database=Blogging;Integrated Security=True;"/>
</connectionStrings>

This post provides more details on how Code First determines the database to be used, including connection strings in the configuration file.

 

The Database First and Model First approaches with an EDMX file use special EF connection strings. For example:

<connectionStrings>
  <add name="BlogContext" 
        connectionString="metadata=res://*/BlogModel.csdl|
                                                              res://*/BlogModel.ssdl|
                                                              res://*/BlogModel.msl;
                                           provider=System.Data.SqlClient;
                                           provider connection string=
                                               &quot;data source=.\SQLEXPRESS;
                                               initial catalog=Blogging;
                                               integrated security=True;
                                               multipleactiveresultsets=True;&quot;" 
    providerName="System.Data.EntityClient" />
</connectionStrings>

 

Connection strings go in the standard connectionStrings element and do not require the new entityFramework section.

 

Code First Default Connection Factory

The new configuration section allows you to specify a default connection factory that Code First should use to locate a database to use for a context. The default connection factory is only used when no connection string has been added to the configuration file for a context.

To set a connection factory, you specify the assembly qualified type name in the deafultConnectionFactory element.
Note: An assembly qualified name is the namespace qualified name, followed by a comma, then the assembly that the type resides in. You can optionally also specify the assembly version, culture and public key token.

Here is an example of setting your own default connection factory:

<entityFramework>
  <defaultConnectionFactory type="MyNamespace.MyCustomFactory, MyAssembly"/>
</entityFramework>

 

The above example requires the custom factory to have a parameterless constructor. If needed, you can specify constructor parameters using the parameters element.

For example, the SqlCeConnectionFactory, that is included in Entity Framework, requires you to supply a provider invariant name to the constructor. The provider invariant name identifies the version of SQL Compact you want to use. The following configuration will cause contexts to use SQL Compact version 4.0 by default.

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
</entityFramework>

 

If you don’t set a default connection factory, Code First uses the SqlConnectionFactory, pointing to .\SQLEXPRESS. SqlConnectionFactory also has a constructor that allows you to override parts of the connection string. If you want to use a SQL Server instance other than .\SQLEXPRESS you can use this constructor to set the server.

The following configuration will cause Code First to use a LocalDB instance for contexts that don’t have an explicit connection string set.

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="Data Source=(localdb)\v11.0; Integrated Security=True; MultipleActiveResultSets=True" />
    </parameters>
  </defaultConnectionFactory>
</entityFramework>

 

By default, it’s assumed that constructor arguments are of type string. You can use the type attribute to change this.

<parameter value="2" type="System.Int32" />

 

Database Initializers

Database initializers are configured on a per-context basis. They can be set in the configuration file using the context element. This element uses the assembly qualified name to identify the context being configured.

By default, Code First contexts are configured to use the CreateDatabaseIfNotExists initializer. There is a disableDatabaseInitialization attribute on the context element that can be used to disable database initialization.

For example, the following configuration disables database initialization for the Blogging.BlogContext context defined in MyAssembly.dll.

<contexts>
  <context type=" Blogging.BlogContext, MyAssembly" disableDatabaseInitialization="true" />
</contexts>

 

You can use the databaseInitializer element to set a custom initializer.

<contexts>
  <context type=" Blogging.BlogContext, MyAssembly">
    <databaseInitializer type="Blogging.MyCustomBlogInitializer, MyAssembly" />
  </context>
</contexts>

 

Constructor parameters use the same syntax as default connection factories.

<contexts>
  <context type=" Blogging.BlogContext, MyAssembly">
    <databaseInitializer type="Blogging.MyCustomBlogInitializer, MyAssembly">
      <parameters>
        <parameter value="MyConstructorParameter" />
      </parameters>
    </databaseInitializer>
  </context>
</contexts>

 

You can configure one of the generic database initializers that are included in Entity Framework. The type name should specify your context as the TContext generic. The type attribute uses the .NET Framework format for generic types.

The following configuration sets the DropCreateDatabaseAlways<TContext> initializer for the BlogContext.
Note: The type name is wrapped in the example but must appear on a single line in your configuration file.

<contexts>
  <context type=" Blogging.BlogContext, MyAssembly">
    <databaseInitializer type="System.Data.Entity.DropCreateDatabaseAlways`1[
[Blogging.BlogContext, MyAssembly]], EntityFramework" />
  </context>
</contexts>

 

If you are using Code First Migrations, you can configure the database to be migrated automatically using the MigrateDatabaseToLatestVersion<TContext, TMigrationsConfiguration> initializer.
Note: The type name is wrapped in the example but must appear on a single line in your configuration file.

<contexts>
  <context type="Blogging.BlogContext, MyAssembly">
    <databaseInitializer type="System.Data.Entity.MigrateDatabaseToLatestVersion`2[[Blogging.BlogContext, 
MyAssembly], [Blogging.Migrations.Configuration, MyAssembly]], EntityFramework" />
  </context>
</contexts>

 

Summary

In this walkthrough you saw how to use the configuration file to set connection strings, default connection factory and database initializers.

Rowan Miller
Program Manager
ADO.NET Entity Framework

The recent SQL Azure Q4 2011 Service Release includes the new feature SQL Azure Federations which enables greater scalability and performance from the database tier of your application through horizontal partitioning. One or more tables within a database are split by row and partitioned across multiple databases (Federation members). This type of horizontal partitioning is often referred to as ‘sharding’. Detailed information on the SQL Azure Federations feature is available here.

The current release of Entity Framework can be used to work with SQL Azure Federations, however a federated database cannot be created by the Entity Framework. Our Customer Advisory Team has started a series of blog posts with the goal of providing guidance around common scenarios and issues that arise when using the Entity Framework with SQL Azure Federations.

The first blog post in this series, SQL Azure Federations with Entity Framework Code-First, is a great getting started guide. It explains the correct procedure to submit the USE FEDERATION statement before sending queries to the database via the Entity Framework (query execution or update operations).

The next post, Understanding SQL Azure Federations No-MARS Support and Entity Framework, explains the impact of the lack of support for MARS on Entity Framework applications.

We are working with the Customer Advisory Team to continue adding posts to this series. While these blog posts provide concrete scenarios detailed walkthroughs and code samples, here are some general guidelines/considerations:

• The Entity Framework based application needs to be aware and manage the access to the different federation members. What this means is that the application would have to explicitly open the store connection with which the context is associated and issue the “USE FEDERATION” statement to connect to the correct federation member before interacting with the database via the Entity Framework.
• Any needed database transaction would have to be started after the “USE FEDERATION” statement is issued. This is because federated databases do not support the “USE FEDERATION” statement in a transaction.
• Any connection retries would also need to be handled by the application.
• Instances of the context class should not span across federation members. In general, this also means that all changes managed by the context should be associated with a single federation member. This is because at the time SaveChanges is invoked, it would issue the corresponding database data modification operations only to the federation member to which the associated store connection currently points.

In the future, our plan is to provide more integrated support for SQL Azure Federations. We would love to hear your experiences on using the Entity Framework with federated databases as well as your suggestions on how we can improve it.

ADO.NET Entity Framework Team