Change existing column to Identity

Question

Change existing column to Identity

Navigation

#1 by (2 votes)

2

Having an entity where the first key is an integer and is set to not be a Identity

public class Teste
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    public string Nome { get; set; }
}

When trying to change the primary key to a Identity , migrations is not generated correctly.

public class Teste
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string Nome { get; set; }
}

The migrations is even correct

public partial class M2 : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("dbo.Testes");
        AlterColumn("dbo.Testes", "Id", c => c.Int(nullable: false, identity: true));
        AddPrimaryKey("dbo.Testes", "Id");
    }

    public override void Down()
    {
        DropPrimaryKey("dbo.Testes");
        AlterColumn("dbo.Testes", "Id", c => c.Int(nullable: false));
        AddPrimaryKey("dbo.Testes", "Id");
    }
}

However, the script for the database is not correct.

ALTER TABLE [dbo].[Testes] DROP CONSTRAINT [PK_dbo.Testes]
ALTER TABLE [dbo].[Testes] ALTER COLUMN [Id] [int] NOT NULL
ALTER TABLE [dbo].[Testes] ADD CONSTRAINT [PK_dbo.Testes] PRIMARY KEY ([Id])
INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201708071357537_M2', N'ConsoleApp1.Migrations.Configuration',  0x1F8B0800000000000400CD57DB6E1B37107D2FD07F20F8D4008E68D92FADB19BC091EDC268641B5927EFD4EE4826C2CB96E41AD2B7E5A19FD45FE870EF17CB519C2208F4227167CE9C3973E1EADF2FFF446FB74A9247B04E181DD3F9EC9812D0A9C984DEC4B4F0EBD7BFD3B76F7EFD25BACCD4967C6AEC4E831D7A6A17D307EFF333C65CFA008ABB9912A935CEACFD2C358AF1CCB093E3E33FD87CCE0021286211127D28B4170ACA1FF87361740AB92FB85C9A0CA4ABCFF14952A2921BAEC0E53C8598A2AD3312CEF37C4EC9B9141C292420D79470AD8DE71E099E7D7490786BF426C9F180CBFB5D0E68B7E6D2414DFCAC333F3487E3939003EB1C1BA8B470DEA86F049C9FD6A2B0B1FB8BA4A5AD6828DB25CAEB7721EB52BA98DE83F398F938D2D942DA60359075561A1F91DED1515B7AEC90F0C1A785F485855843E12D9747E4AE584991FE05BB7BF31974AC0B29FB9C90153E1B1CE0D19D353958BFFB00EB9AE97546091BFAB1B163EBD6F3A9D2B8D6FEF484921B0CCE5712DA92F7524EBCB1F02768B0DC4376C7BD07AB030694A24DA28F62DD18054D34EC319C134A967CFB1EF4C63FC414BF527225B690352735838F5AE058A193B7050C8344ACABD7B48A5806CF05D2ED97321CC2D63F51516CFDBAA8AE8E354CA7024DC0F7E11C251D856A9E6675CB3C45B4A5D40D2AAB26B59968B667A4A325CF73D4AC37E2F50949EAF97E9D7C7BFFAB0A83A5EE893168D9B691B003F806464FC30C647025ACF317DCF3150F555B646A623628C01E719B50038DC7CDDF49DE9887EF95CB641EC7DE9D7257988CC2E62DF382964233F413BF72AD72C9ED1353B430B2507ADF243EE75DCD45DFBF3A9922446C447CAC0B9B0833DA0763999F6BD1B1491BBD6DD5514B46757B1C72158DFAA532A104A5791459E89564872550B360304BFE960B2930DFCE60C9B5586395AA9549F15238195D6A3FCF05C39CCBE421B7CC0F5FFA2248FAD5B53EB9130EDFF3FA91DBF481DBDF14DFBEEA237DEF2E9F6E9C0396F5FE5D5DF55F4CB39541EA15C566BFBF708F4F872162FDB7B7E8029CD87410E15D4E431ABAAC036D6CAEF5DA343263527D468DC9A80A4BF03C436DCEAD176B9E7A7C9C8273E5ADFB89CB024D2ED50AB26B7D5BF8BCF0E7CE815AC9C12D1EB1E7E39797D59073749B875FEEFF4801690A4C016EF5BB42C8ACE57D35EDC27D10A14DEAD64656F8D681709B5D8B7463F48140B57C1790830E83710F2A9708E66E75C21FE125DCF07DE33D6C78BA6B76DA7E90AF1762287B7421F8C672E56A8CCE3FFC2361E12FC99BFF008F1CAF0CC40C0000 , N'6.1.3-40302')

alter column would be missing to transform id into an identity column.

sql-server c# entity-framework-6

asked by anonymous 07.08.2017 / 16:07

1 answer

Paste previous record - SQL Server 2012 How do I trigger a method when the user presses the delete or return key?

score 2 · Accepted Answer

Can not change an existing column to Identity in SQL Server, so EF6 ends up ignoring the identity: true of migration, which for me is wrong, should give an error te warning that he can not generate a script for this and that reason.

If the table is new and contains no data, it is easier to "comment" the DbSet :

// public DbSet<Teste> Testes {get; set;}

Then when generating the migrations, he will understand that he should delete the table:

public override void Up()
{
    DropTable("dbo.Testes");
}

Then "uncomment" DbSet and generate other migrations:

public override void Up()
{
    CreateTable(
        "dbo.Testes",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                Nome = c.String(),
            })
        .PrimaryKey(t => t.Id);          
}

If it's a table that already contains data and you need to keep the ID, I suggest you create a new table (ex: Testes_Temp ) with Id already being Identity , use this command to enable the inclusion of values in the Id field:

SET IDENTITY_INSERT dbo.Testes_Temp ON

Then you have to do a load of data (Insert with Select), something like this:

INSERT INTO dbo.Testes_Temp ( Id, Nome )
SELECT  Id, Nome
FROM dbo.Testes

Then you disable IDENTITY_INSERT :

SET IDENTITY_INSERT dbo.Testes_Temp OFF

Delete the old table and rename the new one.