Kodyaz Development Resources

EF 4.3 Automatic Migrations Walkthrough

Thu, 09 Feb 2012 14:08:58 GMT

We have released the first go-live release of our Code First Migrations work as part of Entity Framework 4.3.

There is a separate 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 & Database

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.

Create a new MigrationsAutomaticDemo Console application.
.
Add the latest version of the EntityFramework NuGet package to the project.
- Tools -> Library Package Manager -> Package Manager Console.
- Run the 'Install-Package EntityFramework' command.
  .

Add a Model.cs file 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; }
    }
}

Now that we have a model it's time to use it to perform data access. Update the Program.cs file with the code shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MigrationsAutomaticDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BlogContext())
            {
                db.Blogs.Add(new Blog { Name = "Another Blog " });
                db.SaveChanges();

                foreach (var blog in db.Blogs)
                {
                    Console.WriteLine(blog.Name);
                }
            }
        }
    }
}

Run your application and you will see that a MigrationsAutomaticDemo.BlogContext database is created on your local SQLEXPRESS instance.

Enabling Migrations

It's time to make some more changes to our model.

Let's introduce a Url property to the Blog class.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }
}

If you were to run the application again you would get an InvalidOperationException because the database no longer matches your model.

"The model backing the 'BlogContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269)."
.
As the exception suggests, it's time to start using Code First Migrations. The first step is to enable migrations for our context. Because we want to use automatic migrations we're going to specify the -EnableAutomaticMigrations switch.
- Run the 'Enable-Migrations -EnableAutomaticMigrations' command in Package Manager Console.
  .
This command has added a Migrations folder to our project. This new folder contains a Configuration class. This class allows you to configure how Migrations behaves for your context. For this walkthrough we will just use the default configuration.
Because there is just a single Code First context in your project, Enable-Migrations has automatically filled in the context type that this configuration applies to.

Your First Automatic 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.

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.

Let's use Update-Database to get Code First Migrations to push the changes to our model (the new Blog.Url property) to the database.
- Run the 'Update-Database' command in Package Manager Console.
  .
Code First Migrations has now updated the MigrationsAutomaticDemo.BlogContext database to include the Url column in the Blogs table.

Our Second Automatic Migration

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

Let's introduce a new Post class.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }    
    public string Url { 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; }
}

Now 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.

Let's add a Blog.Rating property.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; } 
    public int Rating { get; set; }

    public List<Post> Posts { get; set; }
}

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 AddBlogRating.
- Run the 'Add-Migration AddBlogRating' command in Package Manager Console.
  .
In the Migrations folder we now have a new AddBlogRating migration. The migration filename 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 AddBlogRating : DbMigration
    {
        public override void Up()
        {
            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
        }
        
        public override void Down()
        {
            DropColumn("Blogs", "Rating");
        }
    }
}
```
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

We are now free to 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.

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; }
}

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.

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.

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()
        {
        }
    }
}

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.

Let's say we want to migrate our database to the state it was in after running our AddBlogRating 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:"AddBlogRating" -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 -TargetMigration:$InitialDatabase -Force command.

Getting a SQL Script

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.

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 want a script to go from an empty database ($InitialDatabase) 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:$InitialDatabase' command in Package Manager Console.
  .
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.

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 to apply migrations to a database.

Rowan Miller
Program Manager
ADO.NET Entity Framework

EF 4.3 Code-Based Migrations Walkthrough

Thu, 09 Feb 2012 14:14:42 GMT

We have released the first go-live release of our Code First Migrations work as part of Entity Framework 4.3.

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 can reside in your project.

There is a separate 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 & Database

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.

Create a new MigrationsCodeDemo Console application.
.
Add the latest version of the EntityFramework NuGet package to the project.
- Tools -> Library Package Manager -> Package Manager Console.
- Run the 'Install-Package EntityFramework' command.
  .

Add a Model.cs file 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; }
    }
}

Now that we have a model it's time to use it to perform data access. Update the Program.cs file with the code shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MigrationsCodeDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BlogContext())
            {
                db.Blogs.Add(new Blog { Name = "Another Blog " });
                db.SaveChanges();

                foreach (var blog in db.Blogs)
                {
                    Console.WriteLine(blog.Name);
                }
            }
        }
    }
}

Run your application and you will see that a MigrationsCodeDemo.BlogContext database is created on your local SQLEXPRESS instance.

Enabling Migrations

It's time to make some more changes to our model.

Let's introduce a Url property to the Blog class.

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }
}

If you were to run the application again you would get an InvalidOperationException because the database no longer matches your model.

"The model backing the 'BlogContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269)."
.
As the exception suggests, it's time to start using Code First Migrations. The first step is to enable migrations for our context.
- Run the 'Enable-Migrations' command in Package Manager Console.
  . .
This command has added a Migrations folder to our project. This new folder contains two files:
- The Configuration class. This class allows you to configure how Migrations behaves for your context. For this walkthrough we will just use the default configuration.
  Because there is just a single Code First context in your project, Enable-Migrations has automatically filled in the context type that this configuration applies to.
- An InitialCreate migration. This migration was generated because we already had Code First create a database for us, before we enabled migrations. The code in this scaffolded migration represents the objects that have already been created in the database. In our case that is the Blog table with a BlogId and Name columns.

Your 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.

We need to scaffold a migration to take care of the new Url property we have added. The Add-Migration command allows us to give these migrations a name, let's just call ours AddBlogUrl.
- Run the 'Add-Migration AddBlogUrl' command in Package Manager Console.
  .

In the Migrations folder we now have a new AddBlogUrl migration. The migration filename is pre-fixed with a timestamp to help with ordering.

namespace MigrationsCodeDemo.Migrations
{
    using System.Data.Entity.Migrations;
    
    public partial class AddBlogUrl : DbMigration
    {
        public override void Up()
        {
            AddColumn("Blogs", "Url", c => c.String());
        }
        
        public override void Down()
        {
            DropColumn("Blogs", "Url");
        }
    }
}

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.
  .
Code First Migrations has now updated the MigrationsCodeDemo.BlogContext database to include the Url column in the Blogs table.

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.

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 string Url { 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; }
}

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 AddPostClass.
- Run the 'Add-Migration AddPostClass' command in Package Manager Console.
  .

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 AddPostClass : 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");
        }
    }
}

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 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.

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; }
}

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.
  .

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");
        }
    }
}

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.

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

This command will run the Down script for our AddBlogAbstract and AddPostClass migrations.

If you want to roll all the way back to an empty database then you can use the Update-Database -TargetMigration:$InitialDatabase command.

Getting a SQL Script

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 ($InitialDatabase) 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:$InitialDatabase -TargetMigration:"AddPostAbstract"' command in Package Manager Console.
  .
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.

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

EF 4.3 Released

Thu, 09 Feb 2012 14:23:00 GMT

Over the last six months we've released a series of previews of our Code First Migrations work. Today we are making the first fully supported go-live release of our migrations work available as part of EF 4.3.

What Changed Between EF 4.2 and EF 4.3

The notable changes between EF 4.2 and EF 4.3 include:

New Code First Migrations Feature. This is the primary new feature in EF 4.3 and allows a database created by Code First to be incrementally changed as your Code First model evolves.
Removal of EdmMetadata table. If you allow Code First to create a database by simply running your application (i.e. without explicitly enabling Migrations) the creation will now take advantage of improvements to database schema generation we have implemented as part of 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. Even if you manually included these members in your model, using the Fluent API in previous versions of Code First would ignore any Data Annotations on these members. This is now fixed and Code First will process the Data Annotations once the private, protected, or internal properties are manually included in the model.
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.

What Changed from EF 4.3 Beta 1

Aside from some minor bug fixes the changes to Code First Migrations since EF 4.3 Beta 1 include:

Enable-Migrations will now scaffold a code-based migration if the database has already been created. If you use Code First to create a database and then later enable migrations, a code-based migration will be scaffolded that represents the objects that have already been created in the database. (See the Enabling Migrations section of the Code-Based Migrations Walkthrough for more details).
You can use the -EnableAutomaticMigrations flag to avoid a code-based migration from being scaffolded, and have the creation of these objects be treated as an automatic migration.
Added $InitialDatabase constant. This can be used in place of "0" when specifying a source or target migration. For example, migrating down to an empty database can be performed with the Update-Database -TargetMigration:$InitialDatabase command.
Renamed Update-Database.exe to migrate.exe. The command line tool for applying migrations was originally named to be consistent with the Power Shell command. We've now changed it to be more consistent with other command line names.
Updated migrate.exe so it can be invoked from the NuGet tools folder. In Beta 1 you needed to copy migrate.exe to the same directory as the assembly that contained migrations, you can now invoke it directly from the tools folder. You will need to specify the directory containing your assembly in the /StartUpDirectory parameter. For example:
C:\MyProject\packages\EntityFramework.4.3.0\tools>migrate.exe MyAssembly /StartUpDirectory:C:\MyProject\MyProject\bin\debug
Fixed errors when running migrations commands from Package Manager Console. A number of folks reported errors when using the migrations commands in certain scenarios. We've fixed the underlying bugs causing these issues.
Fixed -Script issues. There were a number of bugs in the scripting functionality of EF 4.3 Beta 1 that prevented you generating a script starting from a migration other than an empty database. These bugs are now fixed.

Getting Started

You can get EF 4.3 by installing the latest version of the EntityFramework NuGet package.

These existing walkthroughs provide a good introduction to using the Code First, Model First & Database First workflows available in Entity Framework:

There are two new walkthroughs that cover the new Code First Migrations feature. 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 your project for simple changes.

Upgrading from 'EF 4.3 Beta 1'

If you have EF 4.3 Beta 1 installed you can upgrade to the new package by running Update-Package EntityFramework in Package Manager Console.

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

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 significant 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 release can be used in a live operating environment subject to the terms in the license terms. The ADO.NET Entity Framework Forum can be used for questions relating to this release.

ADO.NET Entity Framework Team

EF 4.3 Configuration File Settings

Thu, 12 Jan 2012 09:59:00 GMT

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:

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

EF 4.3 Beta 1: Automatic Migrations Walkthrough

Thu, 12 Jan 2012 10:00:00 GMT

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

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.

Create a new MigrationsAutomaticDemo Console application.
.
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.
  .

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.

Run the 'Enable-Migrations' command in Package Manager Console.
.

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.

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.
  .
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.

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; }
}

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.

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; }
}

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.
  .
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");
        }
    }
}
```
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.

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; }
}

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

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

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.

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()
        {
        }
    }
}

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.

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.

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.
  .
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

Rowan Miller
Program Manager
ADO.NET Entity Framework

EF 4.3 Beta 1: Code-Based Migrations Walkthrough

Thu, 12 Jan 2012 10:01:00 GMT

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

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.

Create a new MigrationsCodeDemo Console application.
.
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.
  .

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.

Run the 'Enable-Migrations' command in Package Manager Console.
.

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

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.
  .

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");
        }
    }
}

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.
  .
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.

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; }
}

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.
  .

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.

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");
        }
    }
}

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.

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; }
}

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.

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");
        }
    }
}

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.

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.

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.
  .
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.

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

EF 4.3 Beta 1 Released

Thu, 12 Jan 2012 10:02:00 GMT

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.

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!)

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

SQL Azure Federations and the Entity Framework

Tue, 10 Jan 2012 17:20:14 GMT

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

Code First Migrations: Beta 1 'With-Magic' Walkthrough (Automatic Migrations)

Tue, 29 Nov 2011 10:01:00 GMT

We have released the fourth preview of our migrations story for Code First development; Code First Migrations Beta 1. This release includes a preview of the developer experience for incrementally evolving a database as your Code First model evolves over time.

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 resides in your project. There is a Code First Migrations: Beta 1 'No-Magic' Walkthrough that shows how this same set of changes can be applied using just code-based migrations without any automatic migrations.

This post assumes you have a basic understanding of the Code First functionality that was included in EF 4.2, 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.

Create a new 'Beta1AutoDemo' Console application
.
Add the EntityFramework NuGet package to the project
- Tools -> Library Package Manager -> Package Manager Console
- Run the 'Install-Package EntityFramework' command
  .

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.

Note that we are removing the IncludeMetadataConvention to get rid of that EdmMetadata table that Code First adds to our database. The EdmMetadata table is used by Code First to check if the current model is compatible with the database, which is redundant now that we have the ability to migrate our schema. It isn't mandatory to remove this convention when using migrations, but one less magic table in our database is a good thing right!

using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

namespace Beta1AutoDemo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

Installing Migrations

Now that we have a Code First model let's get Code First Migrations and configure it to work with our context.

Add the EntityFramework.Migrations NuGet package to the project
- Run the 'Install-Package EntityFramework.Migrations' command in Package Manager Console
  .
The EntityFramework.Migrations package has added a Migrations folder to our project. At the moment this folder just contains a single Configuration class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context.

We'll just edit the Configuration class to specify our BlogContext and enable automatic migrations (highlighted below).

namespace Beta1AutoDemo.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    using System.Data.Entity.Migrations.Providers;
    using System.Data.SqlClient;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<BlogContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;

           // Seed data:
            //   Override the Seed method in this class to add seed data.
            //    - The Seed method will be called after migrating to the latest version.
            //    - You can use the DbContext.AddOrUpdate() helper extension method to avoid creating
            //      duplicate seed data. E.g.
            //
            //          myContext.AddOrUpdate(c => c.FullName,
            //              new Customer { FullName = "Andrew Peters", CustomerNumber = 123 },
            //              new Customer { FullName = "Brice Lambson", CustomerNumber = 456 },
            //              new Customer { FullName = "Rowan Miller", CustomerNumber = 789 }
            //          );
            //
        }
    }
}

Our First Automatic Migration

Let's use Update-Database to get Code First Migrations to push this our model to the database.
- Run the 'Update-Database' command in Package Manager Console
  .
Code First Migrations has now created a Beta1Demo.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.

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; }
}.

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.

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; }
}

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
  .
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 Beta1AutoDemo.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");
        }
    }
}
```
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

Let's add a Blog.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; }
}

Let's use Update-Database to get Code First Migrations to push this change to the database.

Run the 'Update-Database' command in Package Manager Console

Data Motion / Custom SQL

We just added the Abstract column but it would be handy to pre-populate it for existing posts using some text from the start of the Content column.

Let's use the Add-Migration command to let Code First Migrations add an empty migration for us. We're going to call this migration 'PopulatePostAbstract'.
- Run the 'Add-Migration PopulatePostAbstract' command in Package Manager Console

Update the migration to run some custom SQL that will populate the Abstract column.

namespace Beta1AutoDemo.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()
        {
        }
    }
}

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.

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.

We're just going to simulate deploying to a second database on the local SQL Express instance. Add an App.config file to your project and include a 'MySecondDatabase' connection string.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySecondDatabase"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=AnotherDatabase;Trusted_Connection=True;"/>
  </connectionStrings>
</configuration>

Now let's run the Update-Database command but this time we'll specify the -TargetDatabase flag to use the connection string we just added and the -Script flag so that changes are written to a script rather than just applied.
- Run the 'Update-Database -TargetDatabase:"MySecondDatabase" -Script' command in Package Manager Console
  .
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.
Looking at the generated file you will see that Code First Migrations has generated SQL for all the automatic and code-based migrations we performed in the same order that we performed them locally.

Summary

As always, we really want your feedback on what we have so far, so please try it out and let us know what you like and what needs improving.

Rowan Miller
Program Manager
ADO.NET Entity Framework

Code First Migrations: Beta 1 'No-Magic' Walkthrough

Tue, 29 Nov 2011 10:02:00 GMT

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 'no-magic' 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 Code First Migrations: Beta 1 'With-Magic' Walkthrough that shows how this same set of changes can be applied by making use of automatic migrations.

This post assumes you have a basic understanding of the Code First functionality that is included in EF 4.2, 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.

Create a new 'Beta1Demo' Console application
.
Add the EntityFramework NuGet package to the project
- Tools -> Library Package Manager -> Package Manager Console
- Run the 'Install-Package EntityFramework' command
  .

using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

namespace Beta1Demo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

Installing Migrations

Now that we have a Code First model let's get Code First Migrations and configure it to work with our context.

Add the EntityFramework.Migrations NuGet package to the project
- Run the 'Install-Package EntityFramework.Migrations' command in Package Manager Console
  .

The EntityFramework.Migrations package has added a Migrations folder to our project. At the moment this folder just contains a single Configuration class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context. We'll just edit the Configuration class to specify our BlogContext.

namespace Beta1Demo.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<BlogContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
           
            // Seed data: 
            //   Override the Seed method in this class to add seed data.
            //    - The Seed method will be called after migrating to the latest version.
            //    - You can use the DbContext.AddOrUpdate() helper extension method to avoid creating
            //      duplicate seed data. E.g.
            //
            //          myContext.AddOrUpdate(c => c.FullName,
            //              new Customer { FullName = "Andrew Peters", CustomerNumber = 123 },
            //              new Customer { FullName = "Brice Lambson", CustomerNumber = 456 },
            //              new Customer { FullName = "Rowan Miller", CustomerNumber = 789 }
            //          );
            //
        }
    }
}

Our First Migration

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 it's 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
  .

In the Migrations folder we now have a new MyFirstMigration migration. The migration is pre-fixed with a timestamp to help with ordering.

namespace Beta1Demo.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");
        }
    }
} .

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
  .
Code First Migrations has now created a Beta1Demo.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.

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; }
}

Let's use the Add-Migration command to let Code First Migrations scaffold it's 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
  .

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 below:

namespace Beta1Demo.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", "BlogId");
            DropColumn("Blogs", "Rating");
            DropTable("Posts");
        }
    }
}

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

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; }
}

Let's use the Add-Migration command to let Code First Migrations scaffold it's 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

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

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.

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.

We're just going to create a script to deploy to a second database on the local SQL Express instance. Add an App.config file to your project and include a 'MySecondDatabase' connection string.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySecondDatabase"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=AnotherDatabase;Trusted_Connection=True;"/>
  </connectionStrings>
</configuration>

Now let's run the Update-Database command but this time we'll specify the -TargetDatabase flag to use the connection string we just added to the configuration file. We'll also specify the -Script flag so that changes are written to a script rather than applied.
- Run the 'Update-Database -TargetDatabase:"MySecondDatabase" -Script' command in Package Manager Console
  .
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.

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 that represents the pending changes to a database.

As always, we really want your feedback on what we have so far, so please try it out and let us know what you like and what needs improving.

Rowan Miller
Program Manager
ADO.NET Entity Framework

Code First Migrations: Beta 1 Released

Tue, 29 Nov 2011 10:03:00 GMT

At the end of September we released Alpha 3 of Code First Migrations. Based on Alpha 2 and Alpha 3 feedback your telling us that we are building the right thing. so it's time to start improving quality and working towards our first go-live release. Today we are making Beta 1 available which is our first step in that direction.

What Changed

This release has been primarily about improving quality and cleaning up the API surface ready to RTM. There aren't any significant changes to the user experience.

Some more notable changes include:

Class and method renames. We've done a lot of renaming and refactoring since Alpha 3. There are some important notes in the next section about how these changes affect migrations that were generated with Alpha 3.
Migrations will now create indexes on foreign key columns.
Improved model change detection. We fixed a number of bugs in the way we look for changes in your model and scaffold migrations. This includes things such as detecting CascadeDelete changes on relationships.

Upgrading From Alpha 3

If you have Alpha 3 installed you can use the 'Update-Package EntityFramework.Migrations' command in Package Manager Console to upgrade to Beta 1. You will need to close and re-open Visual Studio after updating, this is required to reload the updated command assemblies.

You will also need to update any existing code to reflect a series of class and method renames:

The Settings class has been renamed to Configuration. When you update the NuGet package you will get a new Configuration.cs (or Configuration.vb) file added to your project. You will need to remove the old Settings file. If you added any logic for seed data etc. you will need to copy this over to the new Configuration class before removing Settings.
(This file rename is a result of us changing the base class for this class from DbMigrationContext to DbMigrationsConfiguration)
If you have existing migrations that call ChangeColumn you will need to update them to call AlterColumn instead.
There is a designer code file associated with each migration in your project, for migrations generated with Alpha 3 you will need to edit this file. You will need to add a using statement for System.Data.Entity.Migrations.Infrastructure and change the references to IDbMigrationMetadata to IMigrationMetadata.

RTM as EF4.3

So far we have been shipping Migrations as a separate EntityFramework.Migrations NuGet package that adds on to the EntityFramework package. As our team has been looking at the grow-up story to Migrations from a Code First database that was created by just running a Code First application it's becoming clear that Migrations is a very integral part of Code First. We've also heard feedback that we need to reduce the number of separate components that make up EF. Because of this we are planning to roll the migrations work into the EntityFramework NuGet package so that you get everything you need for Code First applications in a single package. This will be the EF4.3 release.

The timeline to our first RTM of Code First Migrations depends on the feedback we get on this release but we are currently aiming to have a go-live release available in early 2012. We're planning to make a Release Candidate available before we publish the final RTM.

What's Still Coming

Beta 1 only includes the Visual Studio integrated experience for Code First Migrations. We also plan to deliver a command line tool and an MSDeploy provider for running Code First Migrations.

We are planning to include the command line tool as part of the upcoming RTM.

We're working with the MSDeploy team to get some changes into the next release of MSDeploy to support our new provider. Our MSDeploy provider will be available when the next version of MSDeploy is published. This will be after our initial RTM of Code First Migrations.

Getting Started

There are two walkthroughs for 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.

Code First Migrations Beta 1 is available via NuGet as the EntityFramework.Migrations package.

Support

ADO.NET Entity Framework Team

Minimizing Connection Pool errors in SQL Azure

Sat, 05 Nov 2011 12:59:00 GMT

One of the most common errors observed when connecting to SQL Azure is – A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.). This issue happens when SqlClient grabs an invalid connection from the pool (connections in the pool become invalid due to throttling in the network or in SQL Azure itself) and returns it to the application. As a consequence, when it tries to effectively use the connection (like executing a command, for example); a SqlException exception is raised.

Let’s consider the code snippet below:

try

{

// Let’s assume the connection string uses default connection pool set to true

// and the connection pool was already previously created for this same

// connection string

using (SqlConnection connection = new SqlConnection("…"))

{

// If the connection pool in not empty,

// even if the connection returned above is dead,

// the SqlConnection.Open command below executes succesfully.

connection.Open();

SqlCommand command = new SqlCommand("select product_name from products");

command.Connection = connection;

command.CommandTimeout = 3;

// The Sqlexception gets thrown here,

// when it tries to send the command to SQL Server.

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

{

…

}

catch (SqlException ex)

{

MessageBox.Show(ex.Message);

}

Currently, the Open method always succeeds, deferring any exception to the command execution itself. To work around this situation, you must add retry-logic every time you connect to SQL Azure from your application. There are plenty of articles and guidance related to this topic and my goal here is not to add one more, but to tell you how to minimize this particular issue.

On August 9^th, 2011, Microsoft released the Reliability Update 1 for the .NET Framework 4 (found at http://support.microsoft.com/kb/2533523), which includes a fix to this problem. Basically, it forces SqlClient to check if the connection in the pool is dead before returning it to the application. If the connection is dead, SqlClient simply reconnects before returning it to the application. It’s important to note that that this fix does not add any additional roundtrip to the server. Instead, it just checks the socket status in the TCP layer, which is very fast and effective.

Now, it’s very important to have in mind that this fix does not substitute the need for retry-logic. This is still a recommended practice, especially when connecting to SQL Azure. Our intent is to just minimize the failures in order to improve the overall connectivity experience to SQL Server and SQL Azure.

Luiz Fernando Santos

ADO.NET PM

EF 4.2 Released

Tue, 01 Nov 2011 11:54:00 GMT

We recently posted about our plans to rationalize how we name, distribute and talk about releases. We heard a resounding 'Yes' from you so then we posted about our plans for releasing EF 4.2.

We then shipped a Beta and a Release Candidate of EF 4.2. Today we are making the final release of EF 4.2 available.

EF 4.2 = Bug Fixes + Semantic Versioning

When we released 'EF 4.1 Update 1' we introduced a bug that affects third party EF providers using a generic class for their provider factory implementation, things such as WrappingProviderFactory<TProvider>. We missed this during our testing and it was reported by some of our provider writers after we had shipped. If you hit this bug you will get a FileLoadException stating "The given assembly name or codebase was invalid". This bug is blocking some third party providers from working with 'EF 4.1 Update 1' and the only workaround for folks using an affected provider is to ask them to remain on EF 4.1. Third party provider writers then identified some areas in EF where it was hard to get EF to work with their providers, so we decided to address these issues in the EF 4.2 release. These provider related changes will be the only changes between 'EF 4.1 Update 1' and 'EF 4.2'.

Obviously a single bug fix wouldn't normally warrant bumping the minor version, but we also wanted to take the opportunity to get onto the semantic versioning path rather than calling the release 'EF 4.1 Update 2'.

Getting Started

The following walkthroughs are available for EF 4.2:

Getting EF 4.2

EF 4.2 is available via NuGet as the EntityFramework package. If you already have the EntityFramework package installed then updating to the latest version will give you EF 4.2.

Code First Migrations

To use Code First Migrations with EF 4.2 you will need to upgrade to the latest version of the EntityFramework.Migrations NuGet package.

Failure to update to the latest version of Code First Migrations will result in an error stating "Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information."

After updating to the latest Code First Migrations package you will need to close and re-open Visual Studio.

Model First & Database First Templates

The templates for using the DbContext API with Model First and Database First are now available under the "Online Templates" tab when "Right-Click -> Add Code Generation Item." is selected on the EF Designer.

Support

This release can be used in a live operating environment subject to the terms in the license terms. The ADO.NET Entity Framework Forum can be used for questions relating to this release.

What's Not in This Release?

Our Code First Migrations work is continuing and we are working to get the next release in your hands soon.

ADO.NET Entity Framework Team

How We Talk about EF and its Future Versions

Tue, 18 Oct 2011 18:02:00 GMT

The EF team has been working to become more agile and get new features into your hands faster. Our first two releases shipped as part of the .NET Framework but, as the result of an effort to release more often, in recent times we have been shipping a slew of “out-of-band” features that build on top of the functionality included in .NET.

These out-of-band releases caused some confusion because we didn’t have a consistent way of talking about versions. To avoid generating more of this confusion in the future we recently decided to adopt semantic versioning principles.

While consistent versioning is great we still have quite a few EF related packages that ship independently of each other. We need to rationalize how we talk about them and define what we are actually referring to when we talk about “Entity Framework” and future versions of it.

The purpose of this post is to provide additional clarity about how we plan to talk about our releases in the future and to get your feedback on the approach we are taking.

The Confusion

In order to deliver features as quickly as possible, we want to keep as much as possible outside of the .NET Framework. With EF continuing to have features that ship both inside and outside the .NET Framework we need to rationalize how we talk about these.

All the EF related libraries, packages and downloads we have today are:

Core EF libraries that ship in the .NET Framework (i.e. System.Data.Entity.dll, System.Data.Entity.Design.dll, System.Web.Entity.dll, etc.)
EntityFramework NuGet package which includes the DbContext API and Code First (i.e. EntityFramework.dll)
Entity Designer that ships in Visual Studio
T4 Templates for using DbContext API with Model First & Database First that ship on Visual Studio Gallery
EF Power Tools (still in Alpha stage) that ship on Visual Studio Gallery
Code First Migrations (still in Alpha stage) which ships via NuGet

The Clarity

Obviously it’s going to get very confusing if we release different versions of those at different times. To avoid this we are going to focus on the EntityFramework NuGet package as the primary deliverable that we refer to as “The Entity Framework” or “EF”. The version number of this package will now define the current version of EF (currently EF 4.1 and soon to be EF 4.2). This is the main point that will govern how we talk about EF, all of our blog posts, how-to videos, whitepapers etc. will guide you toward using the EntityFramework NuGet package.

We will generally treat the core Entity Framework libraries in the .NET Framework as we do any other .NET libraries, such as System.dll, System.Data.dll etc. Therefore when the next release of the .NET Framework comes out we will release an updated version of EF that takes advantage of the new features in the Framework (including new functionality in System.Data.Entity.dll). In situations when we need to refer explicitly to the EF specific assemblies in the .NET Framework we will call them the EF Core Libraries.

The Entity Designer will continue to be installed with Visual Studio and will work with the latest version of EF.

In the future we hope to move the classes from System.Data.Entity.dll into the EntityFramework NuGet package so that we can release features such as Enums without having to wait for a .NET Framework update.

The T4 Templates for DbContext will be available under the “Online” tab when you select “Add New Code Generation Item…” from the EF designer. The templates are located on the Visual Studio Gallery but you don’t need to be aware of this or visit the gallery to use them. We are leaving the templates on VS Gallery so that we can quickly and easily respond to bug fixes, feature requests etc.

The EF Power Tools are still in preview mode and we haven’t really decided where they belong yet. For the moment they will remain as a stand-alone download on Visual Studio Gallery as this is the easiest place to distribute them.

Code First Migrations will remain as a separate NuGet package and when Code First detects that you need to migrate the database schema it will direct you to this NuGet package. Keeping Code First Migrations as a separate package allows us to quickly iterate on this new feature.

Some Features Waiting on .NET Framework 4.5

Since we shipped EF 4.0 as part of .NET 4.0, we started talking about upcoming features as “EF vNext” features. When we then released EF 4.1 and more recently published the EF 4.2 previews, many of you logically asked why those features weren’t included.

While the out-of-band approach has worked well for the DbContext API, Code First and Code First Migrations, the fact is that there are some other features, such as enum type support, that inherently require us to update the core libraries that we previously shipped as part of .NET.

The obvious solution to this is to ship the entire Entity Framework independent of the .NET Framework. The June 2011 CTP was our first attempt at doing this. While we are still pursuing this option it has become clear that from a technical standpoint we are not ready to achieve this immediately. Because of this our new features that require updates to our core libraries will need to wait for the next .NET Framework release. This includes support for Enum Types, Spatial Types, Table-Valued Functions, Stored Procedures with Multiple Results and Auto-Compiled LINQ Queries.

These features will reappear in a preview of Entity Framework that we will ship alongside the next public preview of .NET 4.5.

Summary

In summary, when we talk about “Entity Framework” we are now going to be talking mainly about the out-of-band features that we ship as the EntityFramework NuGet package. Entity Framework is built on the .NET Framework and will make use of the EF core libraries that ship with the .NET Framework, including System.Data.Entity. The EF specific tooling that ships with Visual Studio will always work with the latest version of EF.

Announcing Microsoft SQL Server ODBC Driver for Linux

Thu, 13 Oct 2011 13:34:31 GMT

Greetings
Developer community:

We
heard yesterday and today at the PASS conference about the exciting new areas that we are investing in bringing the power of SQL Server to our customers. Many of our developers who rely on native connectivity to SQL Server primarily use ODBC for their connectivity needs. We have been supporting ODBC as a part of the SQL Native Access Client (SNAC) libraries. In our continued commitment to interoperability, today we also announced that we will be releasing the Microsoft SQL Server ODBC Driver for Linux. We will be releasing first community technology preview (CTP) around mid-November and will be available along with SQL Server 2012 when it is released. Please look for announcement on our SQL Connectivity home page and SQL Server blog page.

We will be showcasing Microsoft SQL Server ODBC Driver for Linux along with our Java and PHP solutions for SQL Server and Azure at PASS conference session “[AD-211-M] Developing Multi-Platform Applications for Microsoft SQL Server and Azure” on Thursday October 13th at 5:00PM at Washington State Convention Center Room #4C4. Also, if you have any questions or feedback on our multi-platform strategy as well as the entire gamut of support we provide to the application developers, I would encourage you to attend the PASS Panel Discussion with SQL Connectivity Leadership “[AD-101-M] SQL Connectivity Leadership Unplugged” on Friday, October 14, 2011, 2:30 PM - 3:45 PM at Washington State Convention Centre Room# 612 where I will be hosting a panel along with the rest of the leadership team that drives the strategy for our application platform.

Thanks,

Raghu Ram

PrincipalGroup Program Manager

SQL Server

EF 4.2 Release Candidate Available

Wed, 28 Sep 2011 11:01:00 GMT

We recently posted about our plans to rationalize how we name, distribute and talk about releases. We heard a resounding ‘Yes’ from you so then we posted about our plans for releasing EF 4.2. We then shipped EF 4.2 Beta 1.

Third party EF provider writers tried out EF 4.2 Beta 1 and identified a couple more areas that were causing issues for them. We have been working to improve these areas and today we are making EF 4.2 Release Candidate available. The final release of EF 4.2 will be available in the near future.

EF 4.2 = Bug Fixes + Semantic Versioning

When we released ‘EF 4.1 Update 1’ we introduced a bug that affects third party EF providers using a generic class for their provider factory implementation, things such as WrappingProviderFactory<TProvider>. We missed this during our testing and it was reported by some of our provider writers after we had shipped. If you hit this bug you will get a FileLoadException stating “The given assembly name or codebase was invalid”. This bug is blocking some third party providers from working with ‘EF 4.1 Update 1’ and the only workaround for folks using an affected provider is to ask them to remain on EF 4.1. Third party provider writers then identified some areas in EF where it was hard to get EF to work with their providers, so we decided to address these issues in the EF 4.2 release. These provider related changes will be the only changes between ‘EF 4.1 Update 1’ and ‘EF 4.2’.

Obviously a single bug fix wouldn’t normally warrant bumping the minor version, but we also wanted to take the opportunity to get onto the semantic versioning path rather than calling the release ‘EF 4.1 Update 2’.

Getting Started

The following walkthroughs are available for EF 4.2:

Getting EF 4.2 Release Candidate

EF 4.2 Release Candidate is available via NuGet as the EntityFramework.Preview package. If you already have the EntityFramework package installed then updating to the latest version will give you EF 4.2.

Model First & Database First Templates

The templates for using the DbContext API with Model First and Database First are now available under the “Online Templates” tab when “Right-Click –> Add Code Generation Item…” is selected on the EF Designer.

Support

What’s Not in This Release?

As covered earlier this release is just a small update to the DbContext & Code First runtime. The features that were included in EF June 2011 CTP require changes to the Core Entity Framework Libraries that are part of the .NET Framework and will ship at a later date. Note that Code First Migrations is not compatible with the EntityFramework.Preview package. Please continue to use the most recent EntityFramework package when working with Migrations. Our Code First Migrations work is continuing and we are working to get the next release in your hands soon.

ADO.NET Entity Framework Team

EF 4.2 Code First Walkthrough

Wed, 28 Sep 2011 11:00:00 GMT

Entity Framework 4.2 Release Candidate is now available and contains a small update to the DbContext API & Code First runtime that were released as part of EF 4.1.

This post will provide an introduction to Code First development and how it can be used with the DbContext API surface. Code First allows you to define your model using C# or VB.Net classes, optionally additional configuration can be performed using attributes on your classes and properties or by using a Fluent API. Your model can be used to generate a database schema or to map to an existing database.

You will need to have Visual Studio 2010 installed to complete this walkthrough.

Mapping to an Existing Database

This walkthrough is going to demonstrate Code First generating the database schema but the same principals apply to mapping to an existing database, with the exception of '6. Setting an Initialization Strategy' which does not apply to existing databases. If Code First is pointed at an existing database that it did not create then it will just attempt to use the specified configuration to access the database. The easiest way to point Code First to an existing database is to add a App/Web.config connection string with the same name as your derived DbContext, for example;

<connectionStrings> 
  <add name="MyProductContext"  
    providerName="System.Data.SqlClient" 
    connectionString="Server=.\SQLEXPRESS;Database=Products;Trusted_Connection=true;"/> 
</connectionStrings>

1. Create the Application

To keep things simple we're going to build up a basic console application that uses the Code First to perform data access:

Open Visual Studio 2010
File -> New -> Project.
Select "Windows" from the left menu and "Console Application"
Enter "CodeFirstSample" as the name
Select "OK"

2. Create the Model

Let's define a very simple model using classes. I'm just defining them in the Program.cs file but in a real world application you would split your classes out into separate files and potentially a separate project.

Below the Program class definition in Program.cs I am defining the following two classes:

public class Category 
{ 
  public string CategoryId { get; set; } 
  public string Name { get; set; }
  public virtual ICollection<Product> Products { get; set; }
}

public class Product 
{ 
  public int ProductId { get; set; } 
  public string Name { get; set; } 
  public string CategoryId { get; set; } 
  public virtual Category Category { get; set; } 
}

3. Create a Context

The simplest way to start using the classes for data access is to define a context that derives from System.Data.Entity.DbContext and exposes a typed DbSet<TEntity> for each class in my model.

We're now starting to use types from the Entity Framework so we need to add the EntityFramework NuGet package:

Project -> Add Library Package Reference.
Select the "Online" tab
Search for "EntityFramework.Preview" and select the package
Click "Install"

Add a using statement for System.Data.Entity at the top of Program.cs

using System.Data.Entity;

Add a derived context below the existing classes that we've defined

public class ProductContext : DbContext 
{ 
  public DbSet<Category> Categories { get; set; } 
  public DbSet<Product> Products { get; set; } 
}

That is all the code we need to write to start storing and retrieving data. Obviously there is quite a bit going on behind the scenes and we'll take a look at that in a moment but first let's see it in action.

4. Reading & Writing Data

I'm padding out the Main method in my program class as follows:

class Program 
{ 
  static void Main(string[] args) 
  { 
    using (var db = new ProductContext()) 
    { 
      // Add a food category 
      var food = new Category { CategoryId = "FOOD", Name = "Foods" }; 
      db.Categories.Add(food); 
      int recordsAffected = db.SaveChanges(); 
      Console.WriteLine( "Saved {0} entities to the database, press any key to exit.", recordsAffected); 
      Console.ReadKey(); 
    } 
  } 
}

You can now run the application and see that the new category is inserted.

Where's My Data?

By convention DbContext has created a database for you on localhost\SQLEXPRESS. The database is named after the fully qualified name of your derived context, in our case that is "CodeFirstSample.ProductContext". We'll look at ways to change this later in the walkthrough.

Model Discovery

DbContext worked out what classes to include in the model by looking at the DbSet properties that we defined. It then uses the default Code First conventions to find primary keys, foreign keys etc.

5. Reading & Writing More Data

Let's pad out the program we just wrote to show a bit more functionality. We are going to make use of the Find method on DbSet that will locate an entity based on primary key. If no match is found then Find will return null. We're also making use of LINQ to query for all products in the Food category ordered alphabetically by name.

I'm replacing the Main we wrote above with the following:

class Program 
{ 
  static void Main(string[] args) 
  { 
    using (var db = new ProductContext()) 
    { 
      // Use Find to locate the Food category 
      var food = db.Categories.Find("FOOD"); 
      if (food == null) 
      { 
        food = new Category { CategoryId = "FOOD", Name = "Foods" }; 
        db.Categories.Add(food); 
      }

      // Create a new Food product 
      Console.Write("Please enter a name for a new food: "); 
      var productName = Console.ReadLine(); 
      var product = new Product { Name = productName, Category = food }; 
      db.Products.Add(product); 
      int recordsAffected = db.SaveChanges(); 
      Console.WriteLine( "Saved {0} entities to the database.", recordsAffected);

      // Query for all Food products using LINQ 
      var allFoods = from p in db.Products 
        where p.CategoryId == "FOOD" 
        orderby p.Name select p;

      Console.WriteLine("All foods in database:"); 
      foreach (var item in allFoods) 
      { 
        Console.WriteLine(" - {0}", item.Name); 
      }

      Console.WriteLine("Press any key to exit."); 
      Console.ReadKey(); 
    } 
  } 
}

6. Setting an Initialization Strategy

In the next section we are going to start changing our model which in turn means the database schema needs to change as well. Currently there is no 'out of the box' solution to evolve your existing schema in place. Database evolution is something we are currently working on, a preview of our work in this space is available as Code First Migrations Alpha 3.

While there is no RTM migrations solution there is the opportunity to run some custom logic to initialize the database the first time a context is used in an AppDomain. This is handy if you want to insert seed data for test runs but it's also useful to re-create the database if the model has changed. In EF we include a couple of strategies you can plug in but you can also write custom ones.

For the walkthrough we just want to drop and re-create the database whenever the model has changed, so at the top of the Main method in my Program class I've added the following code

Database.SetInitializer<ProductContext>(
 new DropCreateDatabaseIfModelChanges<ProductContext>());

7. Data Annotations

So far we've just let EF discover the model using its default conventions but there are going to be times when our classes don't follow the conventions and we need to be able to perform further configuration. There are two options for this; we'll look at Data Annotations in this section and then the fluent API in the next section.

Let's add a supplier class to our model:

public class Supplier 
{ 
    public string SupplierCode { get; set; } 
    public string Name { get; set; } 
}

And we also need to add a set to our derived context

public class ProductContext : DbContext 
{ 
  public DbSet<Category> Categories { get; set; } 
  public DbSet<Product> Products { get; set; } 
  public DbSet<Supplier> Suppliers { get; set; } 
}

Now if we ran our application we'd get an InvalidOperationException saying "EntityType 'Supplier' has no key defined. Define the key for this EntityType." because EF has no way of knowing that SupplierCode should be the primary key for Supplier.

We're going to use Data Annotations now so we need to a using statement at the top of Program.cs:

using System.ComponentModel.DataAnnotations;

Now we can annotate the SupplierCode property to identify that it is the primary key:

public class Supplier 
{ 
  [Key] 
  public string SupplierCode { get; set; } 
  public string Name { get; set; } 
}

The full list of annotations supported in EF is;

KeyAttribute
StringLengthAttribute
MaxLengthAttribute
ConcurrencyCheckAttribute
RequiredAttribute
TimestampAttribute
ComplexTypeAttribute
ColumnAttribute
Placed on a property to specify the column name, ordinal & data type
TableAttribute
Placed on a class to specify the table name and schema
InversePropertyAttribute
Placed on a navigation property to specify the property that represents the other end of a relationship
ForeignKeyAttribute
Placed on a navigation property to specify the property that represents the foreign key of the relationship
DatabaseGeneratedAttribute
Placed on a property to specify how the database generates a value for the property (Identity, Computed or None)
NotMappedAttribute
Placed on a property or class to exclude it from the database

8. Fluent API

In the previous section we looked at using Data Annotations to supplement or override what was detected by conventions. The other way to further configure the model is via the Code First fluent API. The fluent API is considered a more advanced feature and we would recommend using Data Annotations unless your requirements require you to use the fluent API.

To access the fluent API we override the OnModelCreating method in DbContext, in the following code we are using the fluent API to configure the Name property on Supplier to be required.

public class ProductContext : DbContext 
{ 
  public DbSet<Category> Categories { get; set; } 
  public DbSet<Product> Products { get; set; } 
  public DbSet<Supplier> Suppliers { get; set; }

  protected override void OnModelCreating(DbModelBuilder modelBuilder) 
  {  
    modelBuilder.Entity<Supplier>()  
      .Property(s => s.Name)   
      .IsRequired(); 
  } 
}

Summary

In this walkthrough we looked at Code First development using EF. We looked at defining and configuring a model, storing and retrieving data, configuring the database connection, updating the database schema as our model evolved and validating data before it is written to the database.

Rowan Miller
Program Manager
ADO.NET Entity Framework

EF 4.2 Model & Database First Walkthrough

Wed, 28 Sep 2011 10:59:00 GMT

Entity Framework 4.2 Release Candidate is now available and contains a small update to the DbContext API & Code First runtime that were released as part of EF 4.1.

This post will provide an introduction to Model First and Database First development with the DbContext API, using the Entity Data Model Designer in Visual Studio.

You will need to have Visual Studio 2010 installed to complete this walkthrough.

1. Create the Application

To keep things simple we're going to build up a basic console application that uses the DbContext to perform data access:

Open Visual Studio 2010
File -> New -> Project.
Select "Windows" from the left menu and "Console Application"
Enter "ModelFirstSample" as the name
Select "OK"

2. Create the Model

Let's go ahead and add an Entity Data Model to our project;

Project -> Add New Item.
Select 'Data' from the left menu
Select 'ADO.NET Entity Data Model' from the list of available items
Name the model 'PersonModel.edmx'
Click 'Add'

We are going to use Model First for this walkthrough but if you are mapping to an existing database you would now select 'Generate from database', follow the prompts and then skip to step 4.

Select 'Empty model'
Click 'Finish'

Let's add a Person entity to our model:

On the design surface; Right Click -> Add -> Entity
Name the entity 'Person'
Click 'OK'
On the Person entity; Right Click -> Add -> Scalar Property
Name the property 'Full Name'

3. Create the Database

Now that we've defined the model we can generate a database schema to store our data:

On the design surface; Right Click -> Generate Database from Model
Click 'New Connection.'
Specify the details of the database you wish to create
Click 'OK'
If prompted to create the database; click 'Yes'
Click 'Next' then 'Finish'
On the generated script; Right Click -> Execute SQL.
Specify your database server and click 'Connect'

4. Swap to DbContext Code Generation

The PersonModel is currently generating a derived ObjectContext and entity classes that derive from EntityObject, we want to make use of the simplified DbContext API.

To use DbContext we need to install the EntityFramework NuGet package:

Project -> Add Library Package Reference.
Select the "Online" tab
Search for "EntityFramework.Preview" and select the package
Click "Install"

Now we can swap to using DbContext code generation templates:

On the design surface; Right Click -> Add Code Generation Item.
Select 'Online Templates' from the left menu
Search for 'DbContext'
Select 'ADO.NET C# DbContext Generator'
Name the item 'PersonModel.tt'
Click 'Add'

You'll notice that two items are added to your project:

PersonModel.tt
This template generates very simple POCO classes for each entity in your model
PersonModel.Context.tt
This template generates a derived DbContext to use for querying and persisting data

5. Read & Write Data

It's time to access some data, I'm padding out the Main method in Program.cs file as follows;

class Program 
{ 
  static void Main(string[] args) 
  { 
    using (var db = new PersonModelContainer()) 
    { 
      // Save some data 
      db.People.Add(new Person { FullName = "Bob" }); 
      db.People.Add(new Person { FullName = "Ted" }); 
      db.People.Add(new Person { FullName = "Jane" }); 
      db.SaveChanges();

      // Use LINQ to access data 
      var people = from p in db.People 
        orderby p.FullName 
        select p;

      Console.WriteLine("All People:"); 
      foreach (var person in people) 
      { 
        Console.WriteLine("- {0}", person.FullName); 
      }

      // Change someones name 
      db.People.First().FullName = "Janet"; 
      db.SaveChanges(); 
    }

    Console.WriteLine("Press any key to exit..."); 
    Console.ReadKey(); 
  } 
}

Summary

In this walkthrough we looked at Model First development using the DbContext API. We looked at building a model, generating a database, swapping to DbContext code generation and then saving and querying data.

Rowan Miller
Program Manager
ADO.NET Entity Framework

Code First Migrations: Alpha 3 Released

Thu, 22 Sep 2011 02:32:00 GMT

This preview is no longer current.

Code First Migrations Beta 1 is now available.

A couple of weeks ago we released Alpha 2 of Code First Migrations. Today we are happy to make Alpha 3 available to you.

The overall feedback we got on Alpha 2 was that you like the direction we are headed. We heard that there is definitely a use case for automatic migrations, and while not everyone will use them, we should continue to invest in them. You are asking us to look at the story for data migration that doesn't involve dropping down to SQL. Alpha 2 also had some commonly encountered bugs that were annoying and we needed to fix

Alpha 3 is still is primarily focused on the developer experience inside of Visual Studio. This release adds most of development time features we plan to include in the first RTM. Your feedback will guide any changes we make to the overall design. Apart from responding to your feedback our team is starting to focus on improving quality. We are also working on completing the deployment and team-build stories, including a command line tool and an MSDeploy provider.

What Changed

Alpha 3 is focused on adding the remaining features that you have been asking for around development time migrations. The primary changes in Alpha 3 are:

VB.NET migration generation is now available.
Upgrade/Downgrade to any migration is now available via the -TargetMigration switch.
Seed data can now be added by overriding the Seed method of the Settings class. Data is expressed in terms of the current model and the Seed method is called after upgrading to the latest migration.
We removed TargetDatabase from the Settings class. Instead you can use the -TargetDatabase switch to specify the name of a connection string from your App/Web.config file.
You can now start using Code First Migrations against an existing database that was created by Code First. To do this issue an Update-Database command when you model is the same as the model that created the database. To use this feature the EdmMetadata table must be present in the database. This original creation of the database and tables will be counted as an automatic migration, if you want a code-based migration for the initial create of the database and tables you must drop the database and begin using migrations against an empty or non-existent database.
Improved error logging when an exception is encountered.
The bugs that required SQL Compact and SQL Express to be available are now resolved.

Known Issues

Some known issues, and 'yet to be implemented' features include:

Code-based migrations and databases generated with Alpha 2 cannot be used with Alpha 3. Because Alpha 2 was such an early release we needed to make some changes to the table we use to store migrations and the way we generate and identify code-based migrations. If you were using Alpha 2 you will need to regenerate migrations and run them to create your database. We realize this is painful but it is one of the trade-offs associated with getting previews into your hands early in the development process. We are making an effort to reduce such changes in future previews.
If you are updating the EntityFramework.Migrations package you will need to close and re-open Visual Studio after updating. This is required to reload the updated command assemblies.
No outside-of-Visual-Studio experience. Alpha 2 only includes the Visual Studio integrated experience. We also plan to deliver a command line tool and an MSDeploy provider for Code First Migrations.

Feedback

We really want your feedback on Code First Migrations. Is the solution we are delivering what you want? Are there features we should add? Please comment on this post with any feedback you might have.

Getting Started

There are two walkthroughs for Alpha 3, 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.

Code First Migrations Alpha 3 is available via NuGet as the EntityFramework.Migrations package.

Prerequisites & Incompatibilities

Migrations is dependent on EF 4.1 Update 1, this updated release of EF 4.1 will be automatically installed when you install the EntityFramework.Migrations package.

Important: If you have previously run the EF 4.1 stand alone installer you will need to upgrade or remove the installation before using migrations. This is required because the installer adds the EF 4.1 assembly to the Global Assembly Cache (GAC) causing the original RTM version to be used at runtime rather than Update 1.

This release is incompatible with "Microsoft Data Services, Entity Framework, and SQL Server Tools for Data Framework June 2011 CTP".

Support

ADO.NET Entity Framework Team

Code First Migrations: Alpha 3 'No-Magic' Walkthrough

Wed, 21 Sep 2011 17:25:08 GMT

This preview is no longer current.

Code First Migrations Beta 1 is now available.

We have released the third preview of our migrations story for Code First development; Code First Migrations Alpha 3. This release includes a preview of the developer experience for incrementally evolving a database as your Code First model evolves over time.

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 'no-magic' 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 Code First Migrations: Alpha 3 'With-Magic' Walkthrough that shows how this same set of changes can be applied by making use of automatic migrations.

This post assumes you have a basic understanding of the Code First functionality that was included in EF 4.1, 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.

Create a new 'Alpha3Demo' Console application
.
Add the EntityFramework NuGet package to the project
- Tools -> Library Package Manager -> Package Manager Console
- Run the 'Install-Package EntityFramework' command
  .

using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

namespace Alpha3Demo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

Installing Migrations

Now that we have a Code First model let's get Code First Migrations and configure it to work with our context.

Add the EntityFramework.Migrations NuGet package to the project
- Run the 'Install-Package EntityFramework.Migrations' command in Package Manager Console
  .

The EntityFramework.Migrations package has added a Migrations folder to our project. At the moment this folder just contains a single Settings class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context. The Settings class also exposes the provider model for code generation and SQL generation. We'll just edit the settings class to specify our BlogContext.

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Alpha3Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = false;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();

            // Seed data: 
            //   Override the Seed method in this class to add seed data.
            //    - The Seed method will be called after migrating to the latest version.
            //    - The method should be written defensively in order that duplicate data is not created. E.g:
            //
            //        if (!context.Countries.Any())
            //        {
            //            context.Countries.Add(new Country { Name = "Australia" });
            //            context.Countries.Add(new Country { Name = "New Zealand" });
            //        }
            //
        }
    }
}

Our First Migration

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 it's best guess at what we should do to bring the database up-to-date with the current model. Once it has calculated what needs to change in the database, Code First Migrations will use the CSharpMigrationCodeGenerator that was configured in our Settings class to create the migration.

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
  .

In the Migrations folder we now have a new MyFirstMigration migration. The migration is pre-fixed with a timestamp to help with ordering.

namespace Alpha3Demo.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");
        }
    }
} .

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
  .
Code First Migrations has now created a Alpha3Demo.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.

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; }
}

Let's use the Add-Migration command to let Code First Migrations scaffold it's 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
  .

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 below:

namespace Alpha3Demo.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)
                .Index(p => p.Title, unique: true);
            
            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
        }
        
        public override void Down()
        {
            DropForeignKey("Posts", "BlogId", "Blogs", "BlogId");
            DropColumn("Blogs", "Rating");
            DropTable("Posts");
        }
    }
}

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 in Alpha 3, but we can run some arbitrary SQL commands at any point in our script.

Let's add a Blog.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; }
}

Let's use the Add-Migration command to let Code First Migrations scaffold it's 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

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

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.

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

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.

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. In this preview we need to generate a script by pointing to a database to migrate, but in the future you will be able to generate a script between two named versions without pointing to a database.

We're just going to simulate deploying to a second database on the local SQL Express instance. Add an App.config file to your project and include a 'MySecondDatabase' connection string.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySecondDatabase"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=AnotherDatabase;Trusted_Connection=True;"/>
  </connectionStrings>
</configuration>

Now let's run the Update-Database command but this time we'll specify the -TargetDatabase flag to use the connection string we just added to the configuration file. We'll also specify the -Script flag so that changes are written to a script rather than applied.
- Run the 'Update-Database -TargetDatabase:"MySecondDatabase" -Script' command in Package Manager Console
  .
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.

Summary

As always, we really want your feedback on what we have so far, so please try it out and let us know what you like and what needs improving.

Rowan Miller
Program Manager
ADO.NET Entity Framework

Code First Migrations: Alpha 3 'With-Magic' Walkthrough (Automatic Migrations)

Wed, 21 Sep 2011 17:24:26 GMT

This preview is no longer current.

Code First Migrations Beta 1 is now available.

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 resides in your project. There is a separate walkthrough that shows how this same set of changes can be applied using just code-based migrations without any automatic migrations.

This post assumes you have a basic understanding of the Code First functionality that was included in EF 4.1, 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.

Create a new 'Alpha3AutoDemo' Console application
.
Add the EntityFramework NuGet package to the project
- Tools -> Library Package Manager -> Package Manager Console
- Run the 'Install-Package EntityFramework' command
  .

using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

namespace Alpha3AutoDemo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

Installing Migrations

Now that we have a Code First model let's get Code First Migrations and configure it to work with our context.

Add the EntityFramework.Migrations NuGet package to the project
- Run the 'Install-Package EntityFramework.Migrations' command in Package Manager Console
  .

The EntityFramework.Migrations package has added a Migrations folder to our project. At the moment this folder just contains a single Settings class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context. The Settings class also exposes the provider model for code generation and SQL generation.

We'll just edit the setting class to specify our BlogContext and enable automatic migrations (highlighted below).

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Alpha3AutoDemo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = true;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();

            // Seed data: 
            //   Override the Seed method in this class to add seed data.
            //    - The Seed method will be called after migrating to the latest version.
            //    - The method should be written defensively in order that duplicate data is not created. E.g:
            //
            //        if (!context.Countries.Any())
            //        {
            //            context.Countries.Add(new Country { Name = "Australia" });
            //            context.Countries.Add(new Country { Name = "New Zealand" });
            //        }
            //
        }
    }
}

Our First Automatic Migration

Let's use Update-Database to get Code First Migrations to push this our model to the database.
- Run the 'Update-Database' command in Package Manager Console
  .
Code First Migrations has now created a Alpha3Demo.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.

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; }
}.

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.

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; }
}

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
  .
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 Alpha3AutoDemo.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");
        }
    }
}
```
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

Let's add a Blog.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; }
}

Let's use Update-Database to get Code First Migrations to push this change to the database.

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 in Alpha 3, but we can run some arbitrary SQL commands at any point in our script.

We just added the Abstract column but it would be handy to pre-populate it for existing posts using some text from the start of the Content column.

Let's use the Add-Migration command to let Code First Migrations add an empty migration for us. We're going to call this migration 'PopulatePostAbstract'.
- Run the 'Add-Migration PopulatePostAbstract' command in Package Manager Console

Update the migration to run some custom SQL that will populate the Abstract column.

namespace Alpha3AutoDemo.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()
        {
        }
    }
}

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.

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.

We're just going to simulate deploying to a second database on the local SQL Express instance. Add an App.config file to your project and include a 'MySecondDatabase' connection string.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySecondDatabase"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=AnotherDatabase;Trusted_Connection=True;"/>
  </connectionStrings>
</configuration>

Now let's run the Update-Database command but this time we'll specify the -TargetDatabase flag to use the connection string we just added and the -Script flag so that changes are written to a script rather than just applied.
- Run the 'Update-Database -TargetDatabase:"MySecondDatabase" -Script' command in Package Manager Console
  .
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.
Looking at the generated file you will see that Code First Migrations has generated SQL for all the automatic and code-based migrations we performed in the same order that we performed them locally.

Summary

As always, we really want your feedback on what we have so far, so please try it out and let us know what you like and what needs improving.

Rowan Miller
Program Manager
ADO.NET Entity Framework

EF Code First Fluent API with VB.NET

Tue, 13 Sep 2011 15:32:00 GMT

Entity Framework Code First is a development methodology available beginning with the Entity Framework 4.1. When using Code First development you usually begin by writing C# or Visual Basic .NET classes that define your conceptual (domain) model. Your model can then be used to generate a database schema or be mapped to an existing database. You can then load data from and persist data to database using objects that are instances of the types defined in the conceptual model.

By default, Code First conventions are used to automatically configure mapping between the conceptual model and database schema. Code First conventions will work in most mapping scenarios, however, you can override these conventions with data annotations or fluent API. For more information about conventions supported by Code First, see http://msdn.microsoft.com/en-us/library/hh161541(VS.103).aspx.

This post shows how to override Code First conventions using the fluent API with VB.NET. The post also demonstrates the following:

Configuring database creation and initialization strategy.
Retrieving and persisting data.
Overriding the default Code First conventions to set the database name.

Prerequisites

To complete this walkthrough you must have the following installed:

Visual Studio 2010
Entity Framework 4.1

Defining the Model and overriding Code First conventions

In this step you will define VB.NET POCO entity types that represent the conceptual model. The classes do not need to derive from any base classes or implement any interfaces. To interact with data as objects that are instances of entity types for data access you would define a class that derives from DbContext and exposes System.Data.Entity.DbSet properties for the entity types defined in the model. System.Data.Entity.DbSet represents an entity set that is used to perform create, read, update, and delete operations.

When you run your application, DbContext checks to see whether a model for the type of the derived context and the given connection string is already cached in the application domain. If it is, then that model is used. If not, DbContext works out what classes to include in the model based on what DbSet properties were defined. It then uses the default Code First conventions and additional configuration that you specified through annotations or fluent API to build the model (as shown later in this post, the protected OnModelCreating(DbModelBuilder) method on DbContext is overridden to modify the model using fluent API.) The System.Data.Entity.DbSet properties are automatically initialized when the instance of the derived DbContext class is created.

To create a class library project

Open Visual Studio 2010.
On the File menu, point to New, and then click Project.
In the left pane, click Other Languages -> Visual Basic, and then select the Console Application template.
Enter CodeFirstVBSample as the project and solution name.
Add references for the EntityFramework.dll and System.ComponentModel.DataAnnotations.dll assemblies. These assemblies contain types that will be used in this walkthrough.

To define the School model using Code First development

Add a new class to the CodeFirstVBSample project. Enter SchoolModel for the class name.
Replace the code generated by default with the following code that defines the model.

Public Class Department

Public Sub New()

Me.Courses = New List(Of Course)()

End Sub

' Primary key

Public Property DepartmentID() As Integer

Public Property Name() As String

Public Property Budget() As Decimal

Public Property StartDate() As Date

Public Property Administrator() As Integer?

Public Overridable Property Courses() As ICollection(Of Course)

End Class

Public Class Course

Public Sub New()

Me.Instructors = New HashSet(Of Instructor)()

End Sub

' Primary key

Public Property CourseID() As Integer

Public Property Title() As String

Public Property Credits() As Integer

' Foreign key that does not follow the Code First convention.

' The fluent API will be used to configure DepartmentID_FK to be the foreign key for this entity.

Public Property DepartmentID_FK() As Integer

' Navigation properties

Public Overridable Property Department() As Department

Public Overridable Property Instructors() As ICollection(Of Instructor)

End Class

Public Class OnlineCourse

Inherits Course

Public Property URL() As String

End Class

Partial Public Class OnsiteCourse

Inherits Course

Public Sub New()

Details = New OnsiteCourseDetails()

End Sub

Public Property Details() As OnsiteCourseDetails

End Class

' Complex type

Public Class OnsiteCourseDetails

Public Property Time() As Date

Public Property Location() As String

Public Property Days() As String

End Class

Public Class Person

' Primary key

Public Property PersonID() As Integer

Public Property LastName() As String

Public Property FirstName() As String

End Class

Public Class Instructor

Inherits Person

Public Sub New()

Me.Courses = New List(Of Course)()

End Sub

Public Property HireDate() As Date

' Navigation properties

Private privateCourses As ICollection(Of Course)

Public Overridable Property Courses() As ICollection(Of Course)

Public Overridable Property OfficeAssignment() As OfficeAssignment

End Class

Public Class OfficeAssignment

' Primary key that does not follow the Code First convention.

' The HasKey method is used later to configure the primary key for the entity.

Public Property InstructorID() As Integer

Public Property Location() As String

Public Property Timestamp() As Byte()

' Navigation property

Public Overridable Property Instructor() As Instructor

End Class

To define a derived DbContext class that supports the School model

Add a new class to the CodeFirstVBSample project. Enter SchoolContext for the class name.
Copy the following code that defines the context class and replace the SchoolContext class definition.

Imports System.Data.Entity

Imports System.Data.Entity.Infrastructure

Imports System.Data.Entity.ModelConfiguration.Conventions

' add a reference to System.ComponentModel.DataAnnotations DLL

Imports System.ComponentModel.DataAnnotations

Public Class SchoolContext

Inherits DbContext

Public Property OfficeAssignments() As DbSet(Of OfficeAssignment)

Public Property Instructors() As DbSet(Of Instructor)

Public Property Courses() As DbSet(Of Course)

Public Property Departments() As DbSet(Of Department)

Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)

End Sub

End Class

To override the default Code First conventions using the fluent API

This section demonstrates how to use the fluent APIs to configure types to tables mapping, properties to columns mapping, and relationships between tables\type in your model. The fluent API is exposed through the DbModelBuilder type and is most commonly accessed by overriding the OnModelCreating method on DbContext.

Copy the following code and add it to the OnModelCreating method defined on the SchoolContext class.

Comments explain what each mapping does.

' Configure Code First to ignore PluralizingTableName convention

' If you keep this convention then the generated tables

' will have pluralized names.

modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()

' Specifying that a Class is a Complex Type

' The model defined in this topic defines a type OnsiteCourseDetails.

' By convention, a type that has no primary key specified

' is treated as a complex type.

' There are some scenarios where Code First will not

' detect a complex type (for example, if you do have a property

' called ID, but you do not mean for it to be a primary key).

' In such cases, you would use the fluent API to

' explicitly specify that a type is a complex type.

modelBuilder.ComplexType(Of OnsiteCourseDetails)()

' Mapping a CLR Entity Type to a Specific Table in the Database.

' All properties of OfficeAssignment will be mapped

' to columns in a table called t_OfficeAssignment.

modelBuilder.Entity(Of OfficeAssignment)().ToTable("t_OfficeAssignment")

' Mapping the Table-Per-Hierarchy (TPH) Inheritance

' In the TPH mapping scenario, all types in an inheritance hierarchy

' are mapped to a single table.

' A discriminator column is used to identify the type of each row.

' When creating your model with Code First,

' TPH is the default strategy for the types that

' participate in the inheritance hierarchy.

' By default, the discriminator column is added

' to the table with the name “Discriminator”

' and the CLR type name of each type in the hierarchy

' is used for the discriminator values.

' You can modify the default behavior by using the fluent API.

modelBuilder.Entity(Of Person)().

Map(Of Person)(Function(t) t.Requires("Type").

HasValue("Person")).

Map(Of Instructor)(Function(t) t.Requires("Type").

HasValue("Instructor"))

' Mapping the Table-Per-Type (TPT) Inheritance

' In the TPT mapping scenario, all types are mapped to individual tables.

' Properties that belong solely to a base type or derived type are stored

' in a table that maps to that type. Tables that map to derived types

' also store a foreign key that joins the derived table with the base table.

modelBuilder.Entity(Of Course)().ToTable("Course")

modelBuilder.Entity(Of OnsiteCourse)().ToTable("OnsiteCourse")

modelBuilder.Entity(Of OnlineCourse)().ToTable("OnlineCourse")

' Configuring a Primary Key

' If your class defines a property whose name is “ID” or “Id”,

' or a class name followed by “ID” or “Id”,

' the Entity Framework treats this property as a primary key by convention.

' If your property name does not follow this pattern, use the HasKey method

' to configure the primary key for the entity.

modelBuilder.Entity(Of OfficeAssignment)().

HasKey(Function(t) t.InstructorID)

' Specifying the Maximum Length on a Property

' In the following example, the Name property

' should be no longer than 50 characters.

' If you make the value longer than 50 characters,

' you will get a DbEntityValidationException exception.

modelBuilder.Entity(Of Department)().Property(Function(t) t.Name).

HasMaxLength(60)

' Configuring the Property to be Required

' In the following example, the Name property is required.

' If you do not specify the Name,

' you will get a DbEntityValidationException exception.

' The database column used to store this property will be non-nullable.

modelBuilder.Entity(Of Department)().Property(Function(t) t.Name).

IsRequired()

' Switching off Identity for Numeric Primary Keys

' The following example sets the DepartmentID property to

' System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.None to indicate that

' the value will not be generated by the database.

modelBuilder.Entity(Of Course)().Property(Function(t) t.CourseID).

HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)

'Specifying NOT to Map a CLR Property to a Column in the Database

modelBuilder.Entity(Of Department)().

Ignore(Function(t) t.Administrator)

'Mapping a CLR Property to a Specific Column in the Database

modelBuilder.Entity(Of Department)().Property(Function(t) t.Budget).

HasColumnName("DepartmentBudget")

'Configuring the Data Type of a Database Column

modelBuilder.Entity(Of Department)().Property(Function(t) t.Name).

HasColumnType("varchar")

'Configuring Properties on a Complex Type

modelBuilder.Entity(Of OnsiteCourse)().Property(Function(t) t.Details.Days).

HasColumnName("Days")

modelBuilder.Entity(Of OnsiteCourse)().Property(Function(t) t.Details.Location).

HasColumnName("Location")

modelBuilder.Entity(Of OnsiteCourse)().Property(Function(t) t.Details.Time).

HasColumnName("Time")

' Map one-to-zero or one relationship

' The OfficeAssignment has the InstructorID

' property that is a primary key and a foreign key.

modelBuilder.Entity(Of OfficeAssignment)().

HasRequired(Function(t) t.Instructor).

WithOptional(Function(t) t.OfficeAssignment)

' Configuring a Many-to-Many Relationship

' The following code configures a many-to-many relationship

' between the Course and Instructor types.

' In the following example, the default Code First conventions

' are used to create a join table.

' As a result the CourseInstructor table is created with

' Course_CourseID and Instructor_InstructorID columns.

modelBuilder.Entity(Of Course)().

HasMany(Function(t) t.Instructors).

WithMany(Function(t) t.Courses)

' Configuring a Many-to-Many Relationship and specifying the names

' of the columns in the join table

' If you want to specify the join table name

' and the names of the columns in the table

' you need to do additional configuration by using the Map method.

' The following code generates the CourseInstructor

' table with CourseID and InstructorID columns.

modelBuilder.Entity(Of Course)().

HasMany(Function(t) t.Instructors).

WithMany(Function(t) t.Courses).

Map(Sub(m)

m.ToTable("CourseInstructor")

m.MapLeftKey("CourseID")

m.MapRightKey("InstructorID")

End Sub)

' Configuring a foreign key name that does not follow the Code First convention

' The foreign key property on the Course class is called DepartmentID_FK

' since that does not follow Code First conventions you need to explicitly specify

' that you want DepartmentID_FK to be the foreign key.

modelBuilder.Entity(Of Course)().

HasRequired(Function(t) t.Department).

WithMany(Function(t) t.Courses).

HasForeignKey(Function(t) t.DepartmentID_FK)

' Enabling Cascade Delete

' By default, if a foreign key on the dependent entity is not nullable,

' then Code First sets cascade delete on the relationship.

' If a foreign key on the dependent entity is nullable,

' Code First does not set cascade delete on the relationship,

' and when the principal is deleted the foreign key will be set to null.

' The following code configures cascade delete on the relationship.

' You can also remove the cascade delete conventions by using:

' modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>()

' and modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>().

modelBuilder.Entity(Of Course)().

HasRequired(Function(t) t.Department).

WithMany(Function(t) t.Courses).

HasForeignKey(Function(d) d.DepartmentID_FK).

WillCascadeOnDelete(False)

To configure database creation and initialization strategy

In this step you will define a class (we will call it SchoolContextInitializer) that handles database creation and initialization strategy. The database initialization strategy is specified for the derived context type. This occurs only the first time the context is used in the application domain. To set the database initialization strategy call Database.SetInitializer with an IDatabaseinitializer instance.

The SchoolContextInitializer class derives from System.Data.Entity.DropCreateDatabaseIfModelChanges, an implementation of IDatabaseInitializer(Of TContext). When using this strategy, the database will be re-created and re-seeded with data only when the model changes (for example, if a property is added to an entity, or a fluent API is used to change configuration). Override the Seed method to add data to the context and then persist this data to the database during the initialization.

To instantiate this class in your program use: Database.SetInitializer<SchoolContext>(new SchoolContextInitializer())).

Other initialization strategies available in the Entity Framework 4.1 are CreateDatabaseIfNotExists and DropDatabaseAlways.

Add a new class to the CodeFirstVBSample project. Enter SchoolContextInitializer for the class name.
Copy and paste the following SchoolContextInitializer class definition.

Imports System.Data.Entity

Public Class SchoolContextInitializer

Inherits DropCreateDatabaseIfModelChanges(Of SchoolContext)

Protected Overrides Sub Seed(ByVal context As SchoolContext)

Dim english = New Department With {

.Name = "English",

.Budget = 120000D,

.StartDate = Date.Parse("2007-09-01")}

context.Departments.Add(english)

Dim onlineCourses = New OnlineCourse With {

.CourseID = 1,

.Title = "Composition",

.Credits = 4,

.Department = english,

.URL = "http://www.fineartschool.net/Composition"}

context.Courses.Add(onlineCourses)

Dim onsiteCourses = New OnsiteCourse With {

.CourseID = 3,

.Title = "Poetry",

.Credits = 4,

.Department = english,

.Details = New OnsiteCourseDetails With {

.Days = "MTW",

.Location = "123 Smith",

.Time = Date.Parse("11:30")}}

context.Courses.Add(onsiteCourses)

context.SaveChanges()

End Sub

End Class

To retrieve and persist data

In this section you will add the code that queries the School model, updates entities, and saves the data to the database.

The DbSet properties on the DbContext derived class represent a starting query that returns all entities of the specified type. This query can be further refined by using LINQ to Entities methods (for example, Dim query = From d In context.Departments Where d.Name = "English" Select d). A query is executed when: it is enumerated by a For Each statement; it is enumerated by a collection operation such as ToArray, ToDictionary, or ToList; LINQ operators such as First or Any are specified in the outermost part of the query; the Load extension method is called on DbSet.

Open the Module1.vb file where the Main function is defined.
Copy and paste the following Module1 definition.

Imports System.Data.Entity

Module Module1

Sub Main()

' Configure School database creation and initialization Strategy.

' The initialization strategy is called the first time

' the context is used in the application domain.

Database.SetInitializer(Of SchoolContext)(New SchoolContextInitializer())

Using context As New SchoolContext()

' Add instructors to courses.

Dim englishInstructor = New Instructor With {

.PersonID = 1,

.LastName = "Abercrombie",

.FirstName = "Kim",

.HireDate = Date.Parse("1995-03-11")}

Dim englishDepartment = (

From d In context.Departments

Where d.Name = "English"

Select d).FirstOrDefault()

For Each c In englishDepartment.Courses()

c.Instructors.Add(englishInstructor)

Next c

' Save new entities.

Dim recordsAffected As Integer = context.SaveChanges()

'Get the courses where the instructor is Abercrombie Kim

Dim instructor = (

From i In context.Instructors

Where i.FirstName = "Kim" AndAlso i.LastName = "Abercrombie"

Select i).FirstOrDefault()

For Each c In instructor.Courses()

Console.WriteLine(c.Title)

End Using

End Sub

End Module

Compile and run the application.

When you ran the application, the CodeFirstVBSample.SchoolContext database was created and you saw the data in the console window because SchoolContextInitializer seeded the database with data.

The Entity Framework used the default conventions to create the database on the localhost\SQLEXPRESS instance and names it after the fully qualified type name of the derived context (CodeFirstVBSample.SchoolContext). When the application is run subsequent times, unless the model changes, the existing database will be used.

If the model changes and you do not set the initializer, you will get the following exception: “The model backing the 'ContextName’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.”

To override the default Code First conventions to set the database name

One way to specify the database name for a new or existing database is to add an App.config or Web.config file that contains the connection string with the same name as your context type. The .config file should be added to the project that contains the executable assembly.

Add a new app.config file to the CodeFirstVBSample project. To do this: right-click the project, click Add, and then click New Item; select General and then Application Configuration File.
Copy and paste the following connectionStrings element as a child of configuration element in the app.config file that you just added.

<add name="SchoolContext" providerName="System.Data.SqlClient"

connectionString="Server=.\SQLEXPRESS;Database=School; Trusted_Connection=true;

Integrated Security=True;MultipleActiveResultSets=True"/>

</connectionStrings>

Compile and run the application.

When you ran the application, the School database was created. Unless you change your model the subsequent execution of this application will be mapping to the existing School database.

Summary

In this post we demonstrated how to create a model using Code First development using VB.NET. It also shows how to configure mapping between the conceptual model and database schema.

Julia Kornich

Programming Writer

Microsoft SQL Server OLEDB Provider Deprecation Announcement

Tue, 13 Sep 2011 19:20:00 GMT

The commercial release of Microsoft SQL Server, codename “Denali,” will be the last release to support OLE DB. Support details for the release version of SQL Server “Denali” will be made available within 90 days of release to manufacturing. For more information on Microsoft Support Lifecycle Policies for Microsoft Business and Developer products, please see Microsoft Support Lifecycle Policy FAQ. This deprecation applies to the Microsoft SQL Server OLE DB provider only. Other OLE DB providers as well as the OLE DB standard will continue to be supported until explicitly announced.

It’s important to notice that this announcement does not affect ADO’s and ADO.NET’s roadmaps. In addition, while Managed OLEDB classes will continue to be supported, if you are using it to connect to SQL Server through the SNAC OLEDB Provider, you will be impacted.

If you use SQL Server as your RDBMS, we encourage you to use SqlClient as your .NET Provider. In case you use other database technologies, we would recommend that you adopt their native .NET Providers or Managed ODBC in the development of new and future versions of your applications. You don’t need to change your existing applications using OLE DB, as they will continue to be supported, but you may want to consider migrating them as a part of your future roadmap. Microsoft
is fully committed to making this transition as smooth and easy as possible. In order to prepare and help our developer community, we will be providing assistance throughout this process. This will include providing guidance via technical documentation as well as tools to jump start the migration process and being available right away to answer questions on the SQL Server Data Access forum.

Luiz Santos

ADO.NET Program Manager

Annual SQL Connectivity Customer Survey [Date extended]

Thu, 08 Sep 2011 01:19:34 GMT

Greetings to the Developer community:

Thank you very much for an overwhelming response on our survey. Feedback is continue to pour. We are extending the deadline to complete the survey to September 16^th, 2011 to allow everyone to provide input. Your feedback helps us on the broad SQL Connectivity components that address major development platforms, including ODBC, ADO.NET, JDBC and PHP.

We view your organization as a key stakeholder in the process that we have to identify areas for future investments. The feedback you provide is valuable and each response will be read and will be treated with utmost confidence. The survey can be found in the link below and will be available until 16^th September, 2011 5:00 PM PST.

http://www.zoomerang.com/Survey/WEB22CS45XT9FE/

Thanks

Luiz Santos

ADO.NET Program Manager

Code First Migrations: Alpha 2 Walkthrough

Tue, 06 Sep 2011 12:28:00 GMT

This preview is no longer current.

Code First Migrations Alpha 3 is now available.

We have released the second preview of our migrations story for Code First development; Code First Migrations Alpha 2. This release includes a preview of the developer experience for incrementally evolving a database as your Code First model evolves over time.

This post will provide an overview of the functionality that is available inside of Visual Studio for interacting with migrations. This post assumes you have a basic understanding of the Code First functionality that was included in EF 4.1, if you are not familiar with Code First then please complete the Code First Walkthrough.

Note: If you have received the following error when trying to use Code First Migrations Alpha 2 it is most likely due to bugs that were causing a dependency on SQL Compact and a dependency on a local SQL Express instance. We have released an updated package that resolves both these issues, please ensure you have updated the EntityFramework.Migrations package to version 0.6.2. You will need to close and re-open Visual Studio after updating the package, failure to do so will result in an error stating that "The project 'Demo' does not contain any migration contexts". If you continue to get errors after updating to the latest package and re-starting Visual Studio then please start a thread in our Pre-Release Forum.

Add-Migration : Exception has been thrown by the target of an invocation.
At line:1 char:14
+ Add-Migration <<<< Init2
+ CategoryInfo : NotSpecified: (:) [Add-Migration], TargetInvocationException
+ FullyQualifiedErrorId : System.Reflection.TargetInvocationException,System.Data.Entity.Migrations.AddMigrationCommand

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.

Create a new 'Demo' Console application
.
Add the EntityFramework NuGet package to the project
- Tools -> Library Package Manager -> Package Manager Console
- Run the 'Install-Package EntityFramework' command
  .

using System.Data.Entity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;

namespace Demo
{
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

Installing Migrations

Now that we have a Code First model let's get Code First Migrations and configure it to work with our context.

Add the EntityFramework.Migrations NuGet package to the project
- Run the 'Install-Package EntityFramework.Migrations' command in Package Manager Console
  .

The EntityFramework.Migrations package has added a Migrations folder to our project. At the moment this folder just contains a single Settings class, this class has also been opened for you to edit. This class allows you to configure how migrations behaves for your context. The Settings class also exposes the provider model for code generation and SQL generation. We'll come back to these settings a little later on, for the moment we'll just make edit the setting class to specify our BlogContext (highlighted below).

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = false;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();
        }
    }
}

Scaffolding & Running Migrations

We're going to start by looking at the purely imperative workflow (that's where every migration operation is explicitly defined in code). This workflow gives you the most control over the migration process and involves the least magic.

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. Update-Database currently only support migrating your database 'up' to the latest version. Migrating 'up' and 'down' to any version will be supported in the future.

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 it's best guess at what we should do to being the database up-to-date with the current model. Once it has calculated what needs to change in the database, Code First Migrations will use the CSharpMigrationCodeGenerator that was configured in our Settings class to create the migration. The Add-Migrationcommand allows us to give these migrations a name, let's just call ours 'MyFirstMigration'.
- Run the 'Add-Migration MyFirstMigration' command in Package Manager Console
  .

In the Migrations folder we now have a new MyFirstMigration migration. The migration is pre-fixed with a timestamp to help with ordering. There is also a code behind file that stores some metadata about the migration, we won't cover that just yet but you can take a look if you want. Let's turn our attention to the code that Code First Migrations has scaffolded and opened for us.

namespace Demo.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()
        {
            // This preview of Code First Migrations does not support downgrade functionality.
        }
    }
}


 .

We could now edit or add to this migration but in this case 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
  .
Code First Migrations has now created a Demo.BlogContext database on our local SQL Express instance. We could now write code that uses our BlogContext to perform data access against this database.

.

It's time to make some more changes to our model. Let's change Blog.Name to have a maximum length of 100 and add in a Blog.Rating property. We're also going to introduce the Post class at this time.

public class Blog
{
    public int BlogId { get; set; }
    [MaxLength(100)]
    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; }
}

Let's use the Add-Migrationcommand to let Code First Migrations scaffold it's 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
  .

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 can do this inline with the CreateTable call.
We're also adding a non-nullable Blogs.Rating column, if there is any existing data in the table this will fail because it will try to use null for the value of this new column. Let's specify a default value of 3 so that existing rows in the Blogs table will get assigned a value.

Finally, let's also add a non-unique index to the Blogs.Name column.

These changes to the scaffolded migration are highlighted below:

namespace Demo.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)
                .Index(p => p.Title, unique: true);

            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
            ChangeColumn("Blogs", "Name", c => c.String(maxLength: 100));
            CreateIndex("Blogs", new[] { "Name" });
        }

        public override void Down()
        {
            // This preview of Code First Migrations does not support downgrade functionality. 
        }
    }
}

.

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 -Verboseflag so that you can see the SQL that Code First Migrations is running.
- Run the 'Update-Database -Verbose' command in Package Manager Console
  .
Code First Migrations has now updated the schema of our Demo.BlogContext database.

Automatic Migrations

So far you have seen how to scaffold, customize and run migrations. For a lot of folks this will be the complete workflow you use with Code First Migrations. There is another piece that you can opt into though, and that is to have some parts of the migration process happen without needing to have a migration file in your project. This is known as automatic migrations.

Automatic migrations can be combined with the imperative workflow we looked at in the last section. Code First Migrations uses that metadata it stored in the code behind file of each migration to replicate any automatic migrations you performed in-between each code-based migration.

Let's go ahead and add a Blog.Url property:

public class Blog
{
    public int BlogId { get; set; }
    [MaxLength(100)]
    public string Name { get; set; }
    public int Rating { get; set; }
    public string Url { get; set; }
    
    public List<Post> Posts { get; set; }
}

.

We could write this change to a migration file and then execute it, but at this stage we might be feeling pretty comfortable with how Code First Migrations handles column additions. Let's just try and issue an Update-Databasecommand.
- Run the 'Update-Database' command in Package Manager Console
  .
The update process gives us a warning letting us know that there are pending model changes that could not be applied to the database:
There are pending model changes but automatic migration is disabled. Either write these changes to a migration script or enable automatic migration.
To add a migration script with the pending changes use the Add-Migration command. To use automatic migrations edit the settings class in the Migrations directory to enable automatic migrations then re-run the Update-Database command.

Let's go ahead and enable automatic migrations in the Settings class:

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = true;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();
        }
    }
}

.

Now let's try and re-run the update process with automatic migrations enabled.
- Run the 'Update-Database' command in Package Manager Console
  .
This time the update process succeeds and the Url column is added to the Blogs table. If we now made additional changes we could either continue to use automatic migrations or write those changes out to a code-based migration file. If we opt to use a code-based migration for the next set of changes then Code First Migrations will still replicate this same automatic addition of the Url column between the two code-based migrations when upgrading another database instance. It uses the metadata from the code behind files either side of the automatic addition to ensure the automatic changes are applied in the correct order.

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. In this preview we need to generate a script by pointing to a database to migrate, but in the future you will be able to generate a script between two named versions without pointing to a database.

Getting a script is also an easy way to preview what Code First Migrations is going to do. This is especially useful when using automatic migrations.
1. We're just going to simulate deploying to a second database on the local SQL Express instance. We can change the database that migrations is targeting in the Settings class. We're still working through how to handle multiple target databases, so this is an area that will change in future releases.
```
using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Providers;
using System.Data.SqlClient;

namespace Demo.Migrations
{
    public class Settings : DbMigrationContext<BlogContext>
    {
        public Settings()
        {
            AutomaticMigrationsEnabled = true;
            SetCodeGenerator<CSharpMigrationCodeGenerator>();
            AddSqlGenerator<SqlConnection, SqlServerMigrationSqlGenerator>();
            TargetDatabase(@"Server=.\SQLEXPRESS;Database=BloggingTest;Trusted_Connection=True");

            // Uncomment the following line if you are using SQL Server Compact 
            // SQL Server Compact is available as the SqlServerCompact NuGet package
            // AddSqlGenerator<System.Data.SqlServerCe.SqlCeConnection, SqlCeMigrationSqlGenerator>();
        }
    }
}
```
2. 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 just applied.
  - Run the 'Update-Database -Script' command in Package Manager Console
    .
3. Code First Migrations will run the update database 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.
Summary

In this walkthrough you saw how to scaffold and run code-based migrations. You saw that parts of the migration process can be left up to automatic migrations and that code-based and automatic migrations can be intermingled. You also saw how to get a SQL script that represents the pending changes to a database.

As always, we really want your feedback on what we have so far, so please try it out and let us know what you like and what needs improving.

Rowan Miller
Program Manager
ADO.NET Entity Framework