CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6ef-code-firstentity-framework-migrations
Starina
Starina

Reputation: 103

Set identity to the previous created column during migration

I have a project with the CodeFirst database (Entity Framework 6) and two migration steps. Database is updated automatically by using this code in Application_Start in Global.asax:

Database.SetInitializer(
          new MigrateDatabaseToLatestVersion<MyDBEntities, MyNamespace.Configuration>());

First migration step is creating the tables:

CreateTable(
     "dbo.GalleryAlbum",
      c => new
      {
           Id = c.Int(nullable: false),
           //other columns.....
       })
       .PrimaryKey(t => t.Id);

CreateTable(
       "dbo.GalleryPics",
       c => new
       {
           Id = c.Int(nullable: false),
           //other columns.....
       })
       .PrimaryKey(t => t.Id)
       .ForeignKey("dbo.GalleryAlbum", t => t.AlbumId)
       .Index(t => t.AlbumId);

Second migration step is adding identities to the created tables:

AlterColumn("dbo.GalleryAlbum", "Id", c => c.Int(nullable: false, identity: true));
AlterColumn("dbo.GalleryPics", "Id", c => c.Int(nullable: false, identity: true));

When I run the application, I can see that second migration code is running, information about two migrations is added to the _MigrationHistory table, but columns in both tables are not changed (without Identity). Here is the schema:

[Id]        INT             NOT NULL,
//other columns

Code First classes for the first migration are the following:

 public partial class GalleryAlbum
 {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
 }
 //GalleryPics is the same

and this one for the second migration step:

public partial class GalleryAlbum
 {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
 }
 //GalleryPics is the same

Could you please tell me, why Identity is not added to these columns and how I can fix it?

Thanks.

Update: Generated update requests to the database, which I got from the IDbCommandInterceptor: 

ALTER TABLE [dbo].[GalleryAlbum] ALTER COLUMN [Id] [int] NOT NULL
ALTER TABLE [dbo].[GalleryPics] ALTER COLUMN [Id] [int] NOT NULL

Upvotes: 3

Views: 10402

Answers (4)

Esset
Esset

Reputation: 1056

eoghank is right, you can't alter a column to Identity. You need set it when creating the table in the migration. In EF Core 7 .Annotation("SqlServer:Identity", "1, 1") or .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)

More on migration is here

  migrationBuilder.CreateTable(
      name: "MyTable",
      columns: table => new
      {
          Id = table.Column<int>(type: "int", nullable: false)
              .Annotation("SqlServer:Identity", "1, 1"),
              //or
              .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
              //Multiple providers is supported:
              .Annotation("Sqlite:Autoincrement", true),
          OtherColumn = table.Column<string>(type: "nvarchar(20)", nullable: false),
      },
      constraints: table =>
      {
          table.PrimaryKey("PK_MyTable", x => x.Id);
      });

Upvotes: 1

tomRedox
tomRedox

Reputation: 30543

As per the accepted answer, if you can just include the identity when creating the table originally then that will solve the issue. If the table already exists however, and you have access to SSMS, then I find the easiest way is to:

  1. Back up the database...
  2. Change your class so has an identity in your code
  3. Generate the migration with the Package Manager Console (add-migration [your migration name] )
  4. Comment out the code in Up method in the newly generated migration
  5. Add a new line of code ready to receive the SQL you'll generate below: Sql (@" ");
  6. Go into SSMS and make sure it's set to generate scripts when you make a table change
  7. Add the identity in the table designer in SMSS
  8. Save the table change in SSMS and copy the SQL generated. (That SQL makes a copy of the table with the data, drops the original table, then recreates the original table with the identity set on the column, then copies all the data back and adds all the foreign keys and constraints back on again)
  9. Paste the SQL you just copied between the speech marks in the code you added above.
  10. Run the migration

That should then give you a migration that adds the identity and can be run on other copies of the database successfully.

NB:The Down method that was generated won't work either because it will be removing the identity, which EF also can't do. If you need the Down method to work create a copy of the SQL you added and adjust it to create the table without the identity again.

Upvotes: 4

Tony Trembath-Drake
Tony Trembath-Drake

Reputation: 1678

ALTER TABLE [Mytable] add constraint [PK_dbo.Mytable] primary key (Id) works for me if Id was there and had data in place

Upvotes: 0

eoghank
eoghank

Reputation: 1043

You can't ALTER a column to Identity in SQL Server, see Adding an identity to an existing column

Instead try add identity and column in one step:

CreateTable(
         "dbo.GalleryAlbum",
          c => new
          {
              Id = c.Int(nullable: false, identity:true),
              //other columns.....
          }).PrimaryKey(t => t.Id);

Upvotes: 3

Related Questions