Re-Creating table in migration results in 'Invalid column name User_Id'

Question

I open this question because of this unanswered/duplicate question of mine:

Multiple identity columns specified for table exception

The answer to this question is here:

in short: "I have to Re-Create my sql table in the migration Up method"

I have a User has many SchoolclassCode relation:

   public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<SchoolclassCode> SchoolclassCodes { get; set; }
    }

public class SchoolclassCode
{
    public int Id { get; set; }

    public string Schoolclass { get; set; }

    public string Type { get; set; }

    public User User { get; set; }

    public int UserId { get; set; }
}

That is my INIT migration

 public partial class Init: DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.SchoolclassCodes",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Schoolclass = c.String(),
                        Type = c.String(),
                        User_Id = c.Int(),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Users", t => t.User_Id)
                .Index(t => t.User_Id);

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

        }

        public override void Down()
        {
            DropForeignKey("dbo.SchoolclassCodes", "User_Id", "dbo.Users");
            DropIndex("dbo.SchoolclassCodes", new[] { "User_Id" });
            DropTable("dbo.Users");
            DropTable("dbo.SchoolclassCodes");
        }
    }

That is my Second migration which is throwing the error: invalid column name 'User_Id' when I do 'Update-database'

public partial class ReCreateTable : DbMigration
    {
        public override void Up()
        {
            // backup schoolclassCodes table
            DropTable("SchoolclassCodes");
            CreateTable("SchoolclassCodes",
                c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Schoolclass = c.String(maxLength: 3), // 12a,7b
                    Type = c.String(),
                    UserId = c.Int(nullable: false,identity:false)
                })
                .PrimaryKey(t => t.Id)
                .ForeignKey("Users", t => t.UserId, cascadeDelete: true)
                .Index(s => s.Schoolclass, unique: true);


            // Delete Table Users
            Sql("Delete from Users");

            // Re-Insert data
            SqlFile("./Migrations/data.sql");

        }

        public override void Down()
        {
            //
        }
    }

What do I wrong, that the update-database fails?

Can you check which specific query throws this error using `update-database -verbose`? — Red, Apr 03 '16 at 12:31

Steve Greene · Answer 1 · 2016-04-04T13:09:16.180

0

If you are going to delete everything you created in the first migration, drop them as described in the Down() code:

// backup schoolclassCodes table
DropForeignKey("dbo.SchoolclassCodes", "User_Id", "dbo.Users");
DropIndex("dbo.SchoolclassCodes", new[] { "User_Id" });
// DropTable("dbo.Users");
DropTable("dbo.SchoolclassCodes");

An observation, seems like you are coupling the need to keep your old data with migrations. I would separate the two.

First, save your old data. If the volume is low consider adding it to a Seed() method. Otherwise rename the tables in SQL and use SQL to repopulate them after migrations adds the corrected tables back.

Migrations are intended to let you build a database from your models, so I would delete both migrations and add a single new migration that represents your current model.

edited Apr 04 '16 at 13:09

answered Apr 03 '16 at 15:33

Steve Greene

12,029
1
33
54

This application already runs in the wild, the other developers wont let me delete both migrations ;-). I am not going to delete everything I created in the first migration. I do not want to delete the Users table. – Elisabeth Apr 04 '16 at 07:56
You should be able to keep the Users table, but you need to drop the FK constraint. – Steve Greene Apr 04 '16 at 13:10
Now I did as you said also drop the Users table in your order then I get the error: Could not drop object 'dbo.Users' because it is referenced by a FOREIGN KEY constraint. But BEFORe I do: DropForeignKey("dbo.Schoolclasscodes", "UserId", "dbo.Users"); Why this error? – Elisabeth Apr 07 '16 at 10:19
When I look under the Schoolclasscodes table Keys folder in SSMS: there is a black key with: FK_dbo.SchoolclassCodes_dbo.Users_Branch_Id – Elisabeth Apr 07 '16 at 10:25
When I do: EXEC sp_fkeys 'SchooclassCodes' to get all FK`s on the table there is nothing, but I see the key in the SSMStudio :P – Elisabeth Apr 07 '16 at 11:27

Re-Creating table in migration results in 'Invalid column name User_Id'

1 Answers1