Efcore: To change the IDENTITY property of a column, the column needs to be dropped and recreated

[joacar]

Changing an identity pk to composite key

dotnet : System.InvalidOperationException: To change the IDENTITY property of a column, the column needs to be dropped and recreated.
At line:1 char:1
+ dotnet ef migrations script
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (System.InvalidO... and recreated.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(AlterColumnOperation operation, IModel mod
el, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, 
MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateScript(String fromMigration, String toMigration, Boolean idempotent)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.ScriptMigration(String fromMigration, String toMigration, Boolean 
idempotent, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigration.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

To change the IDENTITY property of a column, the column needs to be dropped and recreated.

Steps to reproduce

```c#
public class A {
[Key]
public int Key {get; set;}
public int Key2 {get; set; }
}
// 1. Create DB
public class A {
public int Key {get; set; }
public int Key2 {get; set; }
}
modelBuilder.Entity().HasKey(e => new { e.Key, e.Key2 });
// 2. Try migrate (error message will be shown). dotnet ef migrations script fails as well

// Incorrect Migration.cs
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropPrimaryKey(
name: "PK_A",
table: "A");

        migrationBuilder.AlterColumn<int>(
            name: "Key",
            table: "A",
            nullable: false,
            oldClrType: typeof(int))
            .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

}

// * Correct* Migration.cs
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(
name: "InfluencerId",
table: "OfferingInfluencer",);

        migrationBuilder.AddColumn<int>(
            name: "InfluencerId",
            table: "OfferingInfluencer",
            nullable: false);

}
```

Can't recall if doing schema compare in VS created the temporary table and did the data moving or not. For now I just deleted the previous data so no data migration needed to take place, but perhaps EF generates the same as Schema Compare does (or don't :)

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio Community 2017 RC

[rowanmiller]

Duplicate of https://github.com/aspnet/EntityFramework/issues/329, planned for the next release of EF Core.

[joacar]

Searched prior to submission but didn't find that :)

Thanks will follow that issue

[rowanmiller]

No problem, it's not obvious from the title of #329 😄

[joacar]

I'll post a small question here to you @rowanmiller :)

I have a model (most properties excluded)

````c#
// .HasKey(entity => new { entity.InfluencerId, entity.OfferingId });
public class OfferingInfluencer
{
public int InfluencerId { get; set; }

    [ForeignKey("InfuencerId")]
    public Profile Influencer { get; set; }

    public long OfferingId { get; set; }

    [ForeignKey("OfferingId")]
    public Offering Offering { get; set; }

}

public class Offering
{
[Key]
public long Id { get; set; }

    //[InverseProperty("Offering")]
    public virtual ICollection<OfferingInfluencer> Influencers { get; set; }    
}

````

The table for Offering is

Name | Key(s) | Nullable
---- | --- | --------
OfferingId | PK, FK | not null
InfluencerId | PK | not null
InfluencerId | FK | null

If I add the InverseProperty-attribute to Offerings.Influencers and remove the ForeignKey-attribute from OfferingInfluencer.Influencer the migration will remove the last FK and produce the correct (at least imo :) SQL.

Migration.cs

````c#
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropForeignKey(
name: "FK_OfferingInfluencer_Profiles_InfuencerId",
table: "OfferingInfluencer");

        migrationBuilder.DropIndex(
            name: "IX_OfferingInfluencer_InfuencerId",
            table: "OfferingInfluencer");

        migrationBuilder.DropColumn(
            name: "InfuencerId",
            table: "OfferingInfluencer");

        migrationBuilder.AddForeignKey(
            name: "FK_OfferingInfluencer_Profiles_InfluencerId",
            table: "OfferingInfluencer",
            column: "InfluencerId",
            principalTable: "Profiles",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    }

````

SQL
Name | Key | Nullable
---- | --- | --------
OfferingId | PK,FK | not null
InfluencerId | PK, FK | not null

Is this a bug or just me that doesn't grasp how to model relationships correctly? :) Glad if you can enlighten me or point me to some good resource when to use [InverseProperty] vs [ForeignKey]

[rowanmiller]

@joacar you shouldn't really need to use any of the attributes in this model, since there is a single pair of navigations between each class, and the foreign keys should be picked up by convention. That said, what you described sounds like a bug, so please open a new issue and include the full code listing to reproduce the behavior.

[joacar]

Just adding it here so I won't forget it when I have (take..) time to create a repro solution.

The domain model

````c#
// Dependent
public class PublishedOffering
{
[Key]
public long OfferingId { get; set; }

public virtual Offering Offering {get ;set }

}

// Principal
public class Offering
{
public long Id { get; set; }

public virtual PublishedOffering Published { get; set; }
}
````

Creating a migration produces the FK-constraint correctly but the index name FK_OfferingPublished_Offerings_OfferingId1 gets a 1 at the end.

Adding [ForeignKey("OfferingId")] to dependenty entity generates FK_OfferingPublished_Offerings_OfferingId.

[MikeInSwitzerland]

I get this problem whilst following this tutorial, with the latest version of EF Core.

https://www.codeproject.com/Articles/1210559/Asp-net-core-Angular-Build-from-scratch-a-web-appl

All the tutorial is attempting to do is add one table to a blank existing SQL Server database (on localhost), and I get this error about "To change the IDENTITY property of a column, the column needs to be dropped and recreated."

Which damn table is it referring to ?!

Why don't the error messages quote the SQL it's fallen over on ?

Yesterday, I couldn't even get most of that tutorial to build, until i upgraded VS2017 to the latest version. VS Core didn't even recognise that I'd added AutoMapper to my project. Today, following the upgrade, the code _does_ build okay (but this IDENTITY error continues).

Last week, I was trying to follow an ASP.Net Core tutorial on Microsoft's own website. It was 5 months old, and already out of date, and hopeless. I was Googling for unexpected error messages within 13 minutes of starting the tutorial.

This whole ASP.Net Core and EF Core stuff... it's just not stable.

Even now, in November 2017, it's just not ready yet. Far from it.

I've never wasted so many man-hours Googling for error message after error message.

[gtproys]

MikeInSwitzerland, I am trying to run it too.

did you were able to solve the issue?

[gtproys]

I am trying to follow https://www.codeproject.com/Articles/1210559/Asp-net-core-Angular-Build-from-scratch-a-web
with code but i am not able to get over that step

[Janidbest]

we have hit this today for a change in Identity user Id datatype..
Considering the production scenario, not really sure going ahead to drop and recreate which means potentially we may loose the data which are foreign key referenced. or perhaps drop the constraints and update the data using some helper scripts on the dependent tables.. !!

not so easy change I guess. I expect at least ef migration handle script change and leave data change to user.

[JohnCOsborne]

@joacar Thanks for posting, I'm dealing with the same issue

[Janidbest]

I have managed it by hiding the change (All existing migrations are updated manually). Injected an additional script to handle the database changes.

[chetankhargone]

@rowanmiller I am using EF Core 2.1 (Latest Stable Version). But still facing similar issue.

Can you please check & suggest
https://stackoverflow.com/questions/53408175/ef-core-error-to-change-the-identity-property-of-a-column-the-column-needs-to?noredirect=1#comment93695587_53408175

Thanks

[Angelinsky7]

Still the same issue...