0

I'm trying to use migrations to add a parent table to an existing child table. For eg. I currently have User table, now I want to add a Department table that has a 1 to many relationship: Department has many User.

My questions, in automatic update, can I somehow seed the parent table before adding the FK so I can update all the children to this default seeded Department? If automatic update cannot do this, how do I accomplish this in code?

What I currently did: Made the FK nullable, created the Parent and seeded it, then update all child User FK to the parent. But now I cant change the FK not nullable because throws this error: Automatic migration was not applied because it would result in data loss.

Shawn Mclean
  • 56,733
  • 95
  • 279
  • 406

1 Answers1

1

Switching from nullable to non-nullable is considered data loss because after the migration, there is no way to tell which rows (if any) were null. If you are ok with this, you can call Update-Database with the -Force flag.

Another option would be to add a code-based migration that would:

  1. Add the Departments table
  2. Insert a default department
  3. Add the required FK column to User with a default value of the inserted department
bricelam
  • 28,825
  • 9
  • 92
  • 117
  • Is the `DbMigration.Sql` method called in the `Up` method of the code-based migration with a manually written `INSERT INTO...` statement the right (or only possible?) approach for step 2 ("*Insert a default department*")? – Slauma Jun 13 '13 at 17:25
  • 1
    @Slauma Currently, yes. Although you could add a bit more structure around it using [custom operations](http://romiller.com/2013/02/27/ef6-writing-your-own-code-first-migration-operations/). I suspect [Work Item 16](https://entityframework.codeplex.com/workitem/16) might also include APIs to insert data. – bricelam Jun 13 '13 at 21:33
  • Cool would be also a step 4: "*Drop the default value from the required FK column*" which was surprisingly difficult for me to implement. Although here (http://stackoverflow.com/questions/11974439/changing-column-default-values-in-ef5-code-first) are answers about that it would be nice if a `DropDefaultConstraint` method like in those answers would be part of the `DbMigration` API. Does already a work item exist for that? Or would it be part of work item 16 anyway? – Slauma Jun 13 '13 at 21:55
  • @Slauma, the AlterColumn method should be able to drop/alter the default constraint. – bricelam Jun 14 '13 at 15:19
  • I tried that (with `c => c.Int(nullable: false, defaultValue: null)` as column action and also tried it without `defaultValue`) but it didn't work. The old default value was still in the DB. The linked question is about the same problem, I think. – Slauma Jun 14 '13 at 15:56
  • 1
    @Slauma looks like this should be fixed in EF6 (See [Work Item 452](https://entityframework.codeplex.com/workitem/452)). – bricelam Jun 16 '13 at 02:33