SQL Server Migrations: Rebuild indices on narrowed columns

[tdykstra]

Steps to reproduce

1. Create a many-to-many join entity initially with only navigation properties, no foreign key properties

   public class CourseInstructor
   {
   public int ID { get; set; }
   public Instructor Instructor { get; set; }
   public Course Course { get; set; }
   }

2. Later after adding migration for it and updating database, add foreign keys to it

   public class CourseInstructor
   {
   public int ID { get; set; }
   public int InstructorID { get; set; }
   public int CourseID { get; set; }
   public Instructor Instructor { get; set; }
   public Course Course { get; set; }
   }

   The application continues to run, doesn’t say you have to do a migration, doesn’t realize a migration is needed.

3. Run add-migration command, succeeds.

4. Run update-database and it fails:

   The Up method wants to change the foreign key columns from nullable to not-null, and it wants to change delete action from restrict to cascade.
   To do that, the Up method drops FK constraint, alters column, adds back FK constraint with cascade setting. What it doesn’t do is drop the indexes that were created on those columns, so the alter column statements fail.

5. Manually add drop index and add index statements, and update-database works.

The issue

Up method scaffolding should drop and re-add the indexes on the foreign key columns.

Further technical details

EF Core version: "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
Operating system: Windows 10
Visual Studio version: VS 2015

[rondefreitas]

I believe when directly modeling the foreign keys, you need to define them as nullable. This would be why you're experiencing an issue.

note: I apparently didn't full read the first sentence.

[tdykstra]

This is a many-to-many join table, I wouldn't want the foreign keys to be nullable.

[rondefreitas]

I think this issue would be tied into the Model snapshot file. It looks like the data type of the relationships are not saved, only the nature of them, like so...

         modelBuilder.Entity("Test.Foo", b =>
            {
                b.HasOne("Test.Bar")
                    .WithMany()
                    .HasForeignKey("BarId");
            });

As a result, the tools have no way of knowing that the data type of one of the relationship columns has changed, at least in the context of where it keeps the foreign key data. Therefor, it doesn't know that the index needs to be dropped and re-added.

[rowanmiller]

We thought we already had an issue tracking this one, but we had closed it saying we'd consider fixing it if more people hit it (we now have 3 reports of it).

[bricelam]

Original issue: #4642