How to create and add initial data row to new table using Entity Framework 6 Code First migrations

Question

Working with Entity Framework 6 Code First migrations, I need to add a new table to the DB and then add a foreign key to that new table in an existing table (with lots of data in it).

Since the existing table already has lots of data in it, and the FK to the new table will be NOT NULL, I need to be able to add a row to the new table and then add the key (identity) from this row to all of the existing rows in the "old" table before it is made NOT NULL.

Is there a way to do this in code, so that it will be performed every time I use the Update-Database command, or do I have to manually change the genrated SQL-script?

The existing table/class is named: Certificate The new table/class is named: CertificateRegistry The relationship is CertificateRegistry(One)-->Certificate(many). New column in Certificate would be CertificateRegistryId as a NOT NULL foreign key.

To clarify: the creation of tables and relationships is not a problem, but how I via the code can add a row to new table, and then update existing rows in the old table with the generated identity.

I have tried to search for answers to this without luck, so if you think this is a duplicate, please post a link to the answer.

Colin · Accepted Answer · 2015-03-05 17:23:32Z

2

You have to create the new foreign key as a nullable column, then run sql to update the values, then change the column to not nullable.

Create your model with the non-nullable key -> Add-Migration -> Alter the AddColumn statement by changing nullable from true to false -> Add your Sql("") -> add an AlterColumn statement

The code in your migrations should include something like this:

AddColumn("dbo.Foos", "NewForeignKey", c => c.Int(nullable: true));

Sql("Update dbo.Foos set NewForeignKey = ...");

AlterColumn("dbo.Foos", "NewForeignKey", c => c.Int(nullable: false));

answered Mar 5, 2015 at 17:23

Colin

22.7k17 gold badges111 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GHauan Over a year ago

Thanks. But at that point there will not be any data in the PrimaryKey-table, so the update would fail. But maybe I could insert a value to the PrimaryKey-table as well using the Sql()-method? If so, is there also a way to set the "set identity_insert" value to ON at the same time? Does the Sql() allow for batch queries?

Collectives™ on Stack Overflow

How to create and add initial data row to new table using Entity Framework 6 Code First migrations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related