EF migration is dropping a column and trying to rename a nonexistent column back to Id

Question

I have a Code First MVC Project using EF.

There are two tables, TableA and TableB, and they currently have a one-to-many relationship:

public partial class TableA
{ 
    public TableA()
    {
        TableBs = new HashSet<TableB>();
    }

    public int Id { get; set; }
    public Virtual ICollection<TableB> TableBs { get; set; }
}

public partial class TableB
{
    public int Id { get; set; }
    public int TableAId { get; set; }
    public virtual TableA TableAs { get; set; }
}

    modelBuilder.Entity<TableA>()
        .HasMany(e => e.TableBs)
        .WithRequired(e => e.TableA)
        .HasForeignKey(e => e.TableAId);

---

I tried to change TableA to give it a one-to-zero-or-one relationship:

public partial class TableA
{ 
    public TableA() { }

    public int Id { get; set; }
    public int? TableBId { get; set; }
    public virtual TableB TableB { get; set; }
}

TableB stays the same, and I change my OnModelCreating like so:

    modelBuilder.Entity<TableA>()
        .HasOptional(e => e.TableBs);

    modelBuilder.Entity<TableB>()
        .HasRequired(e => e.TableAs);

The problem is that the generated migration is unexpected -- unexpected to me since I don't know what I'm doing, obviously. Instead of adding the FK and adding the columns it is trying to rename columns. Here is my migration:

 public override void Up()
    {
        DropForeignKey("dbo.TableA", "TableBId", "dbo.TableB");
        DropIndex("dbo.TableA", new[] { "TableBId" });
        DropColumn("dbo.TableB", "Id"); 
        RenameColumn(table: "dbo.TableB", name: "TableBId", newName: "Id");
        DropPrimaryKey("dbo.TableB");
        AlterColumn("dbo.TableA", "TableBId", c => c.Int());
        AlterColumn("dbo.TableB", "Id", c => c.Int(nullable: false));
        AlterColumn("dbo.TableB", "TableAId", c => c.Int(nullable: false));
        AddPrimaryKey("dbo.TableB", "Id");
        CreateIndex("dbo.TableB", "Id");
    }

I don't understand why is it trying to rename a nonexistent column and where are my new FKs?

Answer 1

A couple things might be causing your issues:

1. The fluent API calls describing the relationship

   modelBuilder.Entity<TableA>()
      .HasOptional(e => e.TableBs);
   
   modelBuilder.Entity<TableB>()
      .HasRequired(e => e.TableAs);
   

   Because you used two separate calls here that reference different properties, I'm thinking that there's a possibility EF is interpreting this as two separate relationships. On the other hand, since they're both one-to-one, it miiiight be okay. In any case, it would make more sense as:

   modelBuilder.Entity<TableA>()
      .HasOptional(e => e.TableBs)
      .WithRequired(t => t.TableAs);
   

2. Any kind of one-to-one relationship between entities require both entity types to share a primary key (where the dependent/optional side's primary key is also the foreign key), which is the main reason the generated migration is a mess.

3. I think it's also possible you've tried to run a migration in the past that created the column TableBId in dbo.TableA.

Answer 2

Based on what jjj said I had a serious problem with my relations, I was hoping a normal migration could solve it, but jjj was correct the FK and PK of the dependent table must be the same. I am using SQL server running on Azure so I couldn't just dropcolumn TableB.Id and rename TableB.TableAId to TableB.Id because it was giving me a clustered index error. He was correct my down statement didn't have the drop column so my TableBId was still in dbo.TableA. I rolled back my DB about 5 migrations back. My revision of TableA was fine with:

public partial class TableA
{ 

  public int Id { get; set; }
  public int? TableBId { get; set; }
  public virtual TableB TableB { get; set; }
}

But the problem was with TableB I had to remove TableAId from the Model I added columns to the sudo code just to make the migration more readable:

public partial class TableB
{
  public int Id { get; set; }
  public int Column1 { get; set; }
  public int Column2 { get; set; }
  public int Column3 { get; set; }
  public virtual TableA TableAs { get; set; }
}

Now in my DbContext I went with the one liner:

modelBuilder.Entity<TableB>()
            .HasRequired(e => e.TableA).WithOptional(x => x.TableB);

When I added the migration this is the newly created migration:

     public override void Up()
    {
        DropForeignKey("dbo.TableB", "TableAId", "dbo.TableAId");
        DropIndex("dbo.TableB", new[] { "TableAId" });
        DropColumn("dbo.TableB", "Id");
        RenameColumn(table: "dbo.TableB", name: "TableAId", newName: "Id");
        DropPrimaryKey("dbo.TableB");
        AddColumn("dbo.Tenants", "TableBId", c => c.Int());
        AlterColumn("dbo.TableB", "Id", c => c.Int(nullable: false));
        AlterColumn("dbo.TableB", "Id", c => c.Int(nullable: false));
        AddPrimaryKey("dbo.TableB", "Id");
        CreateIndex("dbo.TableB", "Id");
        AddForeignKey("dbo.TableB", "Id", "dbo.TableA", "Id");
    }

That might have worked in other versions of SQL but not on Azure because it doesn't allow heap tables, that I know of. So here is how I changed the migration code.

public override void Up()
    {
      <-- I am renaming TableB to TableB_Old just for the migration-->
     RenameTable(name: "dbo.TableB", newName: "TableB_Old");
        DropForeignKey("dbo.TableB", "TableAId", "dbo.TableA");
        DropIndex("dbo.TableB_Old", new[] { "TableAId" });
        AlterColumn("dbo.TableB_Old", "TableAId", c => c.Int(nullable: false));
<-- Create a new TableB with the correct layout notice TableAId is not in the column list, so this still matches up to my code model-->
        CreateTable(
           "dbo.TableB",
           c => new
           {
               Id = c.Int(nullable: false)
               Column1 = c.Int(nullable: false)
               Column2 = c.Int(nullable: false)
               Column3 = c.Int(nullable: false)
           })
           .PrimaryKey(t => t.Id)
           .ForeignKey("dbo.TableA", t => t.Id)
           .Index(t => t.Id);

        AddColumn("dbo.TableA", "TableBId", c => c.Int());
<-- This pulls the data from tableB_Old into the newly created TableB, since    TableA had a one-to-many relationship I had to get rid of duplicates this gets  the first record with TableAId and discards the rest. -->

Sql("INSERT INTO [dbo].[TableB] (Id,Column1,Column2,Column3) SELECT     TableAId,,Column1,Column2,Column3 FROM [dbo].[TableB_Old] where [Id] In (select min([Id]) from[dbo].[TableB_Old] as T2 where T2.TableAId = [dbo].[TableB_Old].TableAId)");

<--Now that we pulled all column data we needed from `TableB_Old` we can populate `TableA.TableBId`-->

Sql("UPDATE [TableA] SET [TableBId] = (SELECT [Id] FROM [TableB] WHERE [TableB].[Id] = [TableA].[Id])");

<-- `TableB_Old` was a good table, but it has out lived it's usefullness, it must be removed -->


        DropTable("dbo.TableB_Old");