Entity Framework updating (Code First) existing entity using foreign key Id(s) mistakes

Recently we have come across with a scenario like below.

We had an entity (EF bounded), and we wanted to update the entity’s foreign key references from an existing entity. For example, say..

A Person has a Brazilian Passport, and that person need to update the Passport to an American Passport.

The domain/entity model looks like this

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

   public virtual Passport Passport {get; set; }
   public int PassportId { get; set; }
 }

public class Passport
{
  public int Id { get; set; }
  public string Number { get; set; }
  public string Nationality { get; set; }
}

Notice that Person has 2 properties relates to Passport foreign key references. One is a <a href=”http://blog.staticvoid.co.nz/2012/7/17/entity_framework-navigation_property_basics_with_code_first&#8221; target=”_blank”>Navigational Property</a> and the other is the foreign key id reference. Notice that it uses the {EntityName}Id as the property name. It need to be in this convention for EF to generate the foreign key. If anything other than that EF would create an additional key.

Once you run the <a href=”http://msdn.microsoft.com/en-au/data/jj591621.aspx&#8221; target=”_blank”>EF migration</a> you would see the tables generated as below..

ef_table

Now in order to update the Person’s (only person) passport to an American passport, the code would be something like this..

 using (var dbContext = new PersonContext())
 {
   Console.WriteLine("Update person with passport");
   var person = dbContext.Persons.First();
   var americanPassport = dbContext.Passports.
            Single(x => x.Nationality == "American");

   person.PassportId = americanPassport.Id; //Notice this line

   dbContext.SaveChanges();
 }

I personally don’t like this approach for couple of reasons.

a. It feels “hacky” that you have set the id explicity to update the FK referece.
b. See the entity model, Passport. There are two properties roughly to achieve the same thing. If you use the Navigational Property you can access the Id right? So why we need another Id property?
c. Suddenly your domain entity become database ‘aware’ of FKs etc.

My preferred approach is to leave entity as it is, and just use the Navigational property. Unless if you really have to no the id property for some other reason, which I can’t think of.

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

    public virtual Passport Passport {get; set; }
}

That’s it! EF would generate the Foreign Key for Passport Entity as usual.

Now you can just update the entity using the Navigational Property (not the FK property id)

 using (var dbContext = new PersonContext())
 {
    Console.WriteLine("Update person with passport");

    var person = dbContext.Persons.First();
    var americanPassport = dbContext.Passports.
        Single(x => x.Nationality == "American");
    person.Passport = americanPassport;

    dbContext.SaveChanges();
 }

This is lot cleaner than setting up ID reference. To perform the update like this you need to make sure your, both entities (Person and Passport), are in unchanged State.

Unchanged: the entity is being tracked by the context and exists in the database, and its property values have not changed from the values in the database

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.