EF migration : Error changing an indexed field to nullable

Question

The code in my initial migration was as follows

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

So that the Description field would be unique I added the following to the end of the UP method

CreateIndex("dbo.Sites", "Description", unique: true);

Later I decided to make the Description field required.

The new migration generates the following change

AlterColumn("dbo.Sites", "Description", c => c.String(nullable: false, maxLength: 450));

However when this change attempts to run I get an error

ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column.

I was able to isolate the SQL line using the profiler, as

ALTER TABLE [dbo].[Sites] ALTER COLUMN [Description] nvarchar NOT NULL

Which gives me the following error when I run it in Management Studio

Msg 5074, Level 16, State 1, Line 1 The index 'IX_Description' is dependent on column 'Description'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column.

How can I get the migration code to drop the index, then change alter the column and then re-build the index ?

I am using SQL Server 2008 R2

Answer 1

Something like this perhaps?

DropIndex("dbo.Sites", "IX_Description");
AlterColumn("dbo.Sites", "Description", c => c.String(nullable: false, maxLength: 450));
CreateIndex("dbo.Sites", "Description", unique: true);

I think you can also execute SQL direct as below.

Sql("DROP INDEX [IX_Description] ON [dbo].[Sites] WITH ( ONLINE = OFF )");

Which can be useful if you want to add a check the index exists or something.