Enabling Migrations in Entity Framework

This article will attempt to explain with a example console application how to enable code first migrations and how exactly it works. Lets create the application by following the following steps

Opening Visual Studio 2017 and creating a new Console Application, we will call this solution EFCodeFirstMigrationExample.

Add a new Class Library Project to the solution, we will call this new library Entities.

Create a new Class, lets name it Customer.cs and remove the generated Class.cs

public class Customer
{
    public Guid PersonId { get; set; }
    public string Name { get; set; }
    public string SurName { get; set; }
}

Add another Class Libraty Project to the solution, we will call this new library DataStore.

Select the DataStore project, right click on the project and select Manage Nuget Packages.

In the search bar of the Nuget Package Manager type in Entity Framework, you will be presented with a list of options the first one will be EntityFramework, choose the newest version and click install. The package will now be downloaded, you will be prompt to accept the licence agreement, after a little while you will see the Entity Framework being installed in the DataStore project. If you expand the References folder of this project in the Solution Explorer you will see that the entity framework .dll files has been added to the project. Nuget created a folder named packages at the same location as the solution, inside this folder you will find the configuration file used by Nuget and Entity Framework. Every new package you install in this solution will have its on folder inside the packages folder with its own configuration files.

In the DataStore project you will now find two new files that have been created called the packages.config file that is needed by Nuget to keep track of packages installed in the project. You will also find a file called the App.config, lets take a closer look at this file and how we are going to use it to add our connection string and create our database connection.

If SQL Express is installed on your system, then Visual Studio will choose your local SQL Express instance (./SQLEXPRESS) to create the new database, If SQLExpress is not installed on your system Code First will use LocalDB ((localdb)\v11.0) included with Visual Studio from 2012 edition.

In our example we use the app.config file from our EFCodeFirstMigrationExample console application, so we can delete the app.config file in the Datastore library created by Nuget.

Now we need to specify the connection string that will be used by our application to establish a connection to the database. We do this by adding the following code to the section of the app.config file. Be sure to specify the credentials of a User that has database creation permisions

Now lets add our DataContext to our DataStore project, but first we need to import the necessary references and namespaces.

Select Refereces folder in the DataStore library, right click and select Add Reference ...
On the Reference Manager Dialog box click on the projects tab, select Entities and click OK

Create a new Class, lets name it DataContext.cs and remove the generated Class.cs

public class DataContext : DbContext
{
    public DataContext () : base("TestConnectionString") {}
 
    public DbSet Customers { get; set; }
}

In the constructor we explicitly invoke the base (DbContext) and therefor allows us to specify the connection string to be used, by doing this we bypass the convention that expects a connection string with the same name as the DbContext class (i.e : DataContext). The connection string name we use here is the connection string we added to the app.config.

Now we are ready to create a instance of our context and enumerate a resultset, by doing this we invoke the default DatabaseInitializer which is the method that creates our database.

Follow the same steps as you did when you added EntityFramework to the Datastore library, to add EntityFramework to the EFCodeFirstMigrationExample console application.

Add the following code to the Program.cs file and run the application:

static void Main(string[] args)
{
    try
    {
        using (var context = new PersonContext())
        {
            context.Persons.Add(new Person { Surname = "Smith" });
            context.SaveChanges();
 
            foreach (var person in context.Persons)
            {
                Console.WriteLine(person.Surname);
            }
        }
    }
    catch (Exception exception)
    {
        Console.WriteLine(exception.ToString());
    }
}

In your database you will now find two tables, one is called Customers and the other one is a auto generated by the database initializer named _MigrationHistory, this table will be used by Entity Framework to check the database version an apply schema migrations. The table also contains its first entry which contains the current version of the databse.

Lets now change our Customer.cs class to include a telephone number and a address.

public class Customer
{
    public Guid Id { get; set; }
 
    public string Name { get; set; }
 
    public string Surname { get; set; }

    public string TelNr { get; set; }
 
    public string Address { get; set; }
}

If you try to run the application now, and you have the default Database Initialiazer (CreateDatabaseIfNotExist) used by Entity Framework, you will receive the following error:

System.InvalidOperationException: The model backing the ‘DataContext’ context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).”

If you are still developing your application you don't nee to create a new Migration for any changes in the model, you just need to configure the Database Initializer you want to use. For the purposes of this example we are going to recreate the database everytime the model changes so we will add the following line of code just before the using statement in the Program.cs in our EFCodeFirstMigrationExample console application. If you run the application now you will see that the database has been regenerated with the two new colums Address and Telnr in the Customers table.

Now lets comment out the address and telephone nr fields and rerun the application, you will now see the database has been regenerated and restored to the first version we had. By enabling migrations we can now save this schema as the first migration and save it in a Visual Studio class for later use.

On the Visual Studio Menu select the tools tab, scroll down to the Nuget Package Manager dropdown and select Package Manager Console.

In the Package Manager Console window type in the following command :

Enable-Migrations -ProjectName "DataStore" -StartUpProjectName "EFCodeFirstMigrationExample"

In the DataStore library you will see a autogenerated folder named Migrations with a configuration class inside that will represent how Migrations will behave for my DbContext.

Because we already generated the database by running the application, Enable-Migrations will automatically creates and intitial migration, named [timestamp]_InitialCreate.cs. If we didn't create the database yet, the initial migration will be included in the first Migration we generate.

The command parameters has the following meaning :

-ProjectName specifies the target project to create the migration in

-StartupProjectName specifies the target project to look for a configuration file containing a connection string. If you do not this name the Visual Studio Startup project is used

The project must contain the actual configuration file and not a link to it.

public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Customers",
            c => new
                {
                    PersonId = c.Long(nullable: false, identity: true),
                    Name = c.String(),
                    Surname = c.String(),
                })
            .PrimaryKey(t => t.Id);
         
    }
     
    public override void Down()
    {
        DropTable("dbo.Customers");
    }
}

If we want to add a a new Migration we need to run the following Add-Migration command in The Package Manager Console

Add-Migration YourMigrationName -ProjectName "DataContext" -StartUpProjectName "EFCodeFirstMigrationExample"

When invoking the Add-Migration just specify a name for your Migration (e.g : AddAddressAndTelephone and the two parameters specifying the project as in the Enable-Migrations command. The code for the new Migration will be scaffoleded, by comparing our model with the last Migration created, and generated code will be added to the DataStore library.

Now lets uncomment the Address and Telephone number property and release this new version of the schema as a new Migration. Run the following command:

Add-Migration AddAddressAndTelephoneNr -DataContext"DataStore" -StartUpProjectName "EFCodeFirstMigrationExample"

In the Migration folder, in the DataStore library, you will see a new file was autogenerated named [timestamp]_AddAddressAndTelephoneNr and it should look like this :

public partial class AddAddress : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Customers", "Address", c => c.String());
        AddColumn("dbo.Customers", "TelNr", c => c.String());
    }
     
    public override void Down()
    {
        DropColumn("dbo.Customers", "Address");
        DropColumn("dbo.TelNr", "Address");
    }
}

If you refresh you database you will find that the properties has not yet been added to the Customers table. Every time you create a new Migration, you need to update the database by runnning the Update-Database command in the Package Manager Console

Update-Database -ProjectName "DataStore" -StartUpProjectName "EFCodeFirstMigrationExample"

When invoking Update-Databse just specify the two parameteres specifying the projects as in the Enable-Migrations command. All the pending Migrations, after the last applied migration, will be applied to the Database found in the connection string specified in the Startup project specified in that parameter. We can also specify the name of the target Migration we want to update the Database with, by adding the following parameter to the Update-Database command:

–TargetMigration: AddAddress.

After running the Update-Database command and refreshing the Database, we will find the new properties added as colums to the 'Customers' table. If after that we want to revert to the previous database schema we can run the following command:

Update-Database -ProjectName "DataStore" -StartUpProjectName "EFCodeFirstMigrationExample" –TargetMigration: InitialCreate

If we do not want to apply our Migration directly in development, but would rather have release a script to our DBA, which is a very reasonable requirement for the production environment, we can modify the Update-Databse to generate a script and not apply any changes to the Database. We do this by adding the following parameter to the update command:

-Script

This parameter in the command will cause the migration to generate a Sql script that will be opened in Visual Studio. This generated script contains the migration scripts starting from the las applied migration. If we want to start from a specified Migration we can add the following parameter to the update command

-SourceMigration:MigrationName.

We can also star with an empty Database we can just add the first InitialMigration keyword to this parameter

-SourceMigration:$InitialDatabase

By running the following command, we will generate a script to release in a new production environment starting from an empty Database.

Update-Database -ProjectName "DataStore" -StartUpProjectName "EFCodeFirstMigrationExample" -Script -SourceMigration:$InitialDatabase

The generate script will look as folllow:

CREATE TABLE [dbo].[customer] (
    [Id] [uniqueidentifier] NOT NULL IDENTITY,
    [Name] [nvarchar](max),
    [Surname] [nvarchar](max),
    [Address] [nvarchar](max),
    [TelNr] [nvarchar](max),
    CONSTRAINT [PK_dbo.People] PRIMARY KEY ([PersonId])
)
CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId] [nvarchar](255) NOT NULL,
    [Model] [varbinary](max) NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
    CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY ([MigrationId])
)
BEGIN TRY
    EXEC sp_MS_marksystemobject 'dbo.__MigrationHistory'
END TRY
BEGIN CATCH
END CATCH
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ('201211152147401_InitialCreate', [hash], '5.0.0.net40')
ALTER TABLE [dbo].[People] ADD [Address] [nvarchar](max)
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ('201212032151179_AddAddress', [hash], '5.0.0.net40')