EF migration : Error changing an indexed field to nullable

entity-framework-5entity-framework-migrations

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

Best Answer

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.

Related Solutions

Tsql – Removing a default constraint after adding a new column in code first

Personally I can see nothing wrong with the first approach. Yes, you have to create a default constraint to add a non-nullable column to a non-empty dataset. And yes, you have to delete it afterwards if you need to make sure the new column is always added explicitly in the future, as per your requirement.

And even if you still have issues with this approach, the problem is, there's most likely no other alternative apart from your second approach. And the cost of updating a possibly large table with a default value would seem to me greater than the cost of creating and immediate dropping a default constraint.

I might consider a slight modification, though: I might create the constraint in SQL to avoid the fuss of looking up the default name assigned by the engine, something like this:

Sql("ALTER TABLE tablename
  ADD columnname type NOT NULL
  CONSTRAINT DF_tablename_columnname DEFAULT defaultvalue");

(Could be rewritten to use a helper function.)

Dropping a constraint would then be as trivial as executing a single ALTER TABLE statement:

Sql("ALTER TABLE tablename
  DROP CONSTRAINT DF_tablename_columnname");

(Again, a helper function could easily be used here.)

But that all might be a T-SQL developer in me speaking louder than a C# one. So you might very well go with the code like the example you've posted.

Entity-framework – EF Migrations: Rollback last applied migration

I want to add some clarification to this thread:

Update-Database -TargetMigration:"name_of_migration"

What you are doing above is saying that you want to rollback all migrations UNTIL you're left with the migration specified. Thus, if you use GET-MIGRATIONS and you find that you have A, B, C, D, and E, then using this command will rollback E and D to get you to C:

Update-Database -TargetMigration:"C"

Also, unless anyone can comment to the contrary, I noticed that you can use an ordinal value and the short -Target switch (thus, -Target is the same as -TargetMigration). If you want to rollback all migrations and start over, you can use:

Update-Database -Target:0

0, above, would rollback even the FIRST migration (this is a destructive command--be sure you know what you're doing before you use it!)--something you cannot do if you use the syntax above that requires the name of the target migration (the name of the 0th migration doesn't exist before a migration is applied!). So in that case, you have to use the 0 (ordinal) value. Likewise, if you have applied migrations A, B, C, D, and E (in that order), then the ordinal 1 should refer to A, ordinal 2 should refer to B, and so on. So to rollback to B you could use either:

Update-Database -TargetMigration:"B"

Update-Database -TargetMigration:2

Edit October 2019:

According to this related answer on a similar question, correct command is -Target for EF Core 1.1 while it is -Migration for EF Core 2.0.

Best Answer

Related Solutions

Tsql – Removing a default constraint after adding a new column in code first

Entity-framework – EF Migrations: Rollback last applied migration

Edit October 2019:

Related Topic