Entity Framework Code First Automatic Migration & Existing Tables

We have been smooth sailing with EF6 Code First migration, but recently had few issues to dealt with migrating existing db tables.

Basically we have the below requirements.

  1. All new tables we create use Code First migration.  We want to avoid migration any existing db tables. Also we still want the EF mappings to configure for the existing tables, and have those entities in DbContext so we can work with those tables.
  2. We want to use the automatic migration. – this seems to be working well within the team environment.
  3. We want the migration to work only in our Dev Env, i.e not in QA, UAT, or Prod Envs.

Below are couple of  related SO questions..

http://stackoverflow.com/questions/19964679/ef-5-code-migration-errors-there-is-already-an-object-named-in-the-datab

http://stackoverflow.com/questions/15303542/entity-framework-automatic-migrations-existing-database

The problem is that the EF migration does not appear to be seamlessly working with existing table structure.

When we run the automatic migration for the very first time, we want the existing table structure to be unchanged. But we also want the EF to be aware of the existing tables so we can perform operations on those tables.  It seems like we can only have one way or the other but not both.

Old and new Entities

(I have simplified the code so we can focus on the problem)

public class NewTable : BaseEntity
{
    public string Title { get; set; }
}

public class OldTable
{
     public int Id { get; set; }
     public string Name { get; set; }
}

BaseEntity contains some common properties such as ID, DateModified etc

Below is how the Mappings look like

    public class NewTableMap : EntityMap<NewTable>
    {
        public NewTableMap()
        {
            Property(x => x.Title).HasMaxLength(40);
        }
    }

    public class OldTableMap : EntityTypeConfiguration<OldTable>
    {
        public OldTableMap()
        {
            HasKey(t => t.Id);
            ToTable("OldTable");
            Property(x => x.Id).HasColumnName("fldId");
        }
    }

Note that I have explicitly specified the OldTable entity has been mapped to “OldTable” And the Id has the db column name “fldId”

DBContext has the standard operations including the model binding.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Configurations.Add(new OldTableMap());
     modelBuilder.Configurations.Add(new NewTableMap());

     base.OnModelCreating(modelBuilder);
}

The SQL Database would just have the old table

Since we starting from the scratch let’s enable the migration.

PM> Enable-Migrations -EnableAutomaticMigrations

The above command would create a new Migration Configuration with AutomaticMigration set to True.

We have not created any tables yet! So let’s run the migration command.

PM> Update-Database

We get the below error..
There is already an object named ‘OldTable’ in the database.

As you know there is our existing table in the db.
The script that EF creates had a CreateTable(“dbo.OldTable”), the migration throws the above error.

How can we tell the EF migration to ignore my OldTable??? Honestly don’t know the answer for this. This why the below work around may help you.

First create a specific migration.

PM> Add-Migration FirstMigration

This will create a migration script within the Migration folder. The script has 2 create table functions. One for the NewTable, one for the OldTable.


public override void Up()
{
     CreateTable(
                "dbo.NewTable",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Title = c.String(maxLength: 40),
                        IsActive = c.Boolean(nullable: false),
                        DateModified = c.DateTime(nullable: false,
                                    precision: 7, storeType: "datetime2"),
                        DateCreated = c.DateTime(nullable: false,
                                    precision: 7, storeType: "datetime2"),
                        UserId = c.Guid(nullable: false),
                        MarkAsDeleted = c.Boolean(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

  CreateTable(
                "dbo.OldTable",
                c => new
                    {
                        fldId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.fldId);

}

If we were to run this migration (as above) we would get exactly the same error. So let’s modify the above migration script, for example by removing the CreateTable(“dbo.NewTable”,()… DropTable(“dbo.OldTable”); We leave the scripts for creation of new Tables as it is.

Now if you run the

PM> Update-Database
Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201407120749125_FirstMigration].
Applying explicit migration: 201407120749125_FirstMigration.
Running Seed method.

Now if you check the database, the NewTable has been created and the existing table has not been changed.

Handling practical problems

It is very likely that someone else in your team has a different model changes to your model changes. If that’s the case, you also want to make sure that you don’t commit your initial migration script. You can treat it as your private migration script. This also assumes that you have non-shared database for development.

You team would also have to follow the same process. You can add this migration file to the exclusion list of the source control so the file won’t get committed to the repository.

Now you can make changes to the model as you would normally do and running the automatic migration would apply those changes accordingly.
It will never attempt to create your existing table as the automatic migration is now based on the modified FirstMigration. Subsequent migrations would use the last successful migration.

Just be cautious that in your next automatic migration…
Changing an entity which already mapped to an existing table would also make changes to the existing table.
If you use Ignore() method i.e ( Ignore(x => x.Name)) on the existing entity, it will also exclude that property from the existing table.
If you Ignore() method on the entire existing table during model binding i.e modelBuilder.Ignore(); it will just remove your exiting table. If this happens, simply remove the .Ignore and run the migration.