Efcore: Migrations tries to drop index before creating the index

Created on 12 Jan 2017 · 14Comments · Source: dotnet/efcore

Problem:

The generated SQL tries to drop a newly created index before adding it.

DROP INDEX [IX_Projects_CompanyId] ON [Projects];
Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

DROP INDEX [AlternateKey_ProjectHandle] ON [Projects];
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'Projects') AND [c].[name] = N'Handle');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Projects] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Projects] ALTER COLUMN [Handle] nvarchar(450) NOT NULL;
CREATE UNIQUE INDEX [AlternateKey_ProjectHandle] ON [Projects] ([CompanyId], [Handle]);``

Exception:

System.Data.SqlClient.SqlException: Cannot drop the index 'Projects.AlternateKey_ProjectHandle', because it does not exist or you do not have permission.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:49129b17-5c3a-47dd-9ca9-8f5db887d64a
Error Number:3701,State:7,Class:11
Cannot drop the index 'Projects.AlternateKey_ProjectHandle', because it does not exist or you do not have permission.

Steps to reproduce

FluentAPI:

```c#
builder.Entity()
.HasIndex(p => new { p.CompanyId, p.Handle}).IsUnique()
.HasName("AlternateKey_ProjectHandle");

Migration: 

```c#
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropIndex(
                name: "IX_Projects_CompanyId",
                table: "Projects");

            migrationBuilder.AlterColumn<string>(
                name: "Handle",
                table: "Projects",
                nullable: false,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.CreateIndex(
                name: "AlternateKey_ProjectHandle",
                table: "Projects",
                columns: new[] { "CompanyId", "Handle" },
                unique: true);
        }

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win10 x64
IDE: Visual Studio 2015

Sincerely,
Brecht

closed-duplicate

Source

ErazerBrecht

👍1

Most helpful comment

Update: Doh, didn't see the referenced issue above. Looks like someone's on to it already.

I came across this issue today. Here's the repro:

New console app with the following code:

using Microsoft.EntityFrameworkCore;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Project> Projects { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro2;Trusted_Connection=True;");
        }
    }

    public class Project
    {
        public int Id { get; set; }
        public int CompanyId { get; set; }
        public string Handle { get; set; }
    }
}

Add-Migration Initial
Update-Database
Add the following code to the context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Project>()
        .Property("Handle")
        .HasMaxLength(64);

    modelBuilder.Entity<Project>()
        .HasIndex(p => new { p.Handle, p.CompanyId }).IsUnique()
        .HasName("AlternateKey_ProjectHandle");
}

Add-Migration Second
Update-Database

Note the addition where a max length is applied to the property.

You can avoid this error by creating two separate migrations. One to add the max length and the second to change the index. Not sure if this is the cause of the problems that @ErazerBrecht is having, but hopefully this helps you squash a bug somewhere.

ajbeaven on 16 Feb 2017

👍3

All 14 comments

Note for triage: We thought there was a dupe of this issue, but I am not able to find it.

rowanmiller on 13 Jan 2017

@ErazerBrecht Can you share the complete model (entities and context) before and after the change - to help us debug.

rowanmiller on 17 Jan 2017

@rowanmiller
I'm sorry but I'm not allowed to share the complete model.

ErazerBrecht on 20 Jan 2017

Here is my attempt to reproduce it.

New console app with the following code:

```c#
using Microsoft.EntityFrameworkCore;

namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
}
}

public class MyContext : DbContext
{
    public DbSet<Project> Projects { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;");
    }
}

public class Project
{
    public int Id { get; set; }
    public int CompanyId { get; set; }
    public string Handle { get; set; }
}

}

* `Add-Migration Initial`
* `Update-Database`
* Add the following code to the context:

```c#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Project>()
        .HasIndex(p => new { p.CompanyId, p.Handle }).IsUnique()
        .HasName("AlternateKey_ProjectHandle");
}

Add-Migration Second
Update-Database

This succeeds. Can you adjust the steps above to reproduce the issue. I realize your scenario is more complex, because the drop of the IX_Projects_CompanyId index must be related to some more complex changes in the model... but I could sink a lot of time, to no avail, into trying to speculate what the change is and reproduce the issue 😄.

rowanmiller on 20 Jan 2017

👎1 👍1

Clearing the milestone and assigning as there is nothing actionable until we have a repro.

rowanmiller on 20 Jan 2017

I'll have a look tomorrow.

Thanks in advance

ErazerBrecht on 21 Jan 2017

👍1

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

rowanmiller on 27 Jan 2017

👎1 👍1

Hello,
I currently have a lot of work.
But still thanks for the time the team spent on the ticket.

If I can produce a repro once I'll create a new one.
Sincerely, Brecht

ErazerBrecht on 27 Jan 2017

Update: Doh, didn't see the referenced issue above. Looks like someone's on to it already.

I came across this issue today. Here's the repro:

New console app with the following code:

using Microsoft.EntityFrameworkCore;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Project> Projects { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro2;Trusted_Connection=True;");
        }
    }

    public class Project
    {
        public int Id { get; set; }
        public int CompanyId { get; set; }
        public string Handle { get; set; }
    }
}

Add-Migration Initial
Update-Database
Add the following code to the context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Project>()
        .Property("Handle")
        .HasMaxLength(64);

    modelBuilder.Entity<Project>()
        .HasIndex(p => new { p.Handle, p.CompanyId }).IsUnique()
        .HasName("AlternateKey_ProjectHandle");
}

Add-Migration Second
Update-Database

Note the addition where a max length is applied to the property.

ajbeaven on 16 Feb 2017

👍3

This and #7535 may be duplicates

bricelam on 17 Feb 2017

Hi guys!

I have the same problem stated before:

Just to add that when using Script-Migration, the generated sql additionally tries to Create the index 2 times, one next to the each other:

Also, in my case, the scaffolded migrations are as expected:

The problem arises when updating the Db.

I would be more than happy to share the portion of the source code, if an email is supplied to me.

Bests, and thank so much for the effort!
Always happy to help,
Robert

mdarefull on 24 Feb 2017

Although this issue was filed first, I'm closing it as a dupe of #7535 since there is a workaround and some implementation notes listed there.

bricelam on 27 Feb 2017

So I am getting the same error.

Applying migration '20200626191610_UpdateUser'. Failed executing DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DROP INDEX [IX_WatchList_User] ON [WatchListUser]; DECLARE @var0 sysname; SELECT @var0 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'[WatchListUser]') AND [c].[name] = N'UserID'); IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [WatchListUser] DROP CONSTRAINT [' + @var0 + '];'); ALTER TABLE [WatchListUser] ALTER COLUMN [UserID] nvarchar(450) NOT NULL; CREATE UNIQUE INDEX [IX_WatchList_User] ON [WatchListUser] ([UserID], [CourseID]);