Performing an IDENTITY_INSERT using EF5 Code First Migrations

Question

I have a POCO that I am trying to create via Code First Migrations and then seed data. The problem is that I want to insert specific values into the identity column when seeding.

Here is my POCO

public class Result
{
    public long ResultId { get; set; }
    public long? TeamId { get; set; }

    public Team Team { get; set; }
}

And here is my AddOrUpdate call in the Seed method of the Configuration.cs

context.Results.AddOrUpdate
    (
         r => r.ResultId,
         new Result { ResultId = 101, TeamId = null },
         new Result { ResultId = 201, TeamId = null }
    );

As expected, it does not insert the values of 101 and 201, but instead 1 and 2. Are there any DataAttributes I can apply to the model to help with this?

Do you want to disable identity inserts completely? Or just for your seed method? — Steven V, Mar 07 '13 at 04:26
Initially I was thinking for the seeding only...but if there was a way for Code First migrations to identify the ID column as a primary key but with Identity off, that would be acceptable as well — bryangm, Mar 07 '13 at 05:22
Are you using EntityTypeConfiguration? If so you can use Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None). You could after the seeding turn this option off in your OnModelCreating and this.Database.Initialize(force:true); in the contructor. — Aron, Mar 07 '13 at 07:00

score 19 · Accepted Answer · answered Mar 07 '13 at 07:06

This how to turn off Identity via attribute/conventions

public class Result
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public long ResultId { get; set; }
    public long? TeamId { get; set; }

    public Team Team { get; set; }
}

This is how you turn off Identity via EntityTypeConfiguration

public class ResultMapper : EntityTypeConfiguration<Result>
{
    public ResultMapper()
    {
        HasKey(x => x.ResultId);
        Property(x => x.ResultId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

Or you can use the OnModelCreating overload

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Result>().Property(x => x.ResultId)
               .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }

Thanks @Aron, I successfully inserted into the identity field using the attributes method you specified. — bryangm, Mar 07 '13 at 13:58
If the OP needs IDENTIY_INSERT, it's because they have an identity column. You can't just do away with that identity column to perform an IDENTITY_INSERT. Then you'd need yet another migration to turn Identity on again for that column. Much better to use raw SQL and not change the DB schema just for some inserts. — ProfK, Feb 01 '17 at 18:45

Ross Brasseaux · Answer 2 · 2017-01-25T16:18:03.863

In case anyone is still confused . . .

See below for additional info required to get IDENTITY_INSERT to work with Code-First Migration Seed() method

I did use Aron's implementation of the System.ComponentModel.DataAnnotations.Schema.DatabaseGenerated attribute to set the model ID's DB-generated property to 'None', but I still could not get past the identity insert error. I figured I would post my findings here in case anyone else is still having trouble.

To get it to work, I wrapped the seed method's logic in a SQL transaction and used context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTable ON") to allow the insert prior to running the .AddOrUpdate() method. Here is my Configuration.vb file (using a table for Google API types as our example data):

Imports System
Imports System.Data.Entity
Imports System.Data.Entity.Migrations
Imports System.Linq

Namespace Migrations

    Friend NotInheritable Class Configuration 
        Inherits DbMigrationsConfiguration(Of DAL.MyDbContext)

        Public Sub New()
            AutomaticMigrationsEnabled = False
            AutomaticMigrationDataLossAllowed = False
        End Sub

        Protected Overrides Sub Seed(context As DAL.MyDbContext)
            '  This method will be called after migrating to the latest version.

            Dim newContext As New MyDbContext(context.Database.Connection.ConnectionString)
            Using ts = newContext.Database.BeginTransaction()

                Try

                    ' Turn on identity insert before updating
                    newContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT GoogleApiTypeGroups ON")
                    ' Make sure the expected GoogleApiTypeGroups exist with the correct names and IDs.
                    newContext.GoogleApiTypeGroups.AddOrUpdate(
                        Function(x) x.Id,
                        New GoogleApiTypeGroup() With {.Id = 1, .name = "Google Cloud APIs"},
                        New GoogleApiTypeGroup() With {.Id = 2, .name = "YouTube APIs"},
                        New GoogleApiTypeGroup() With {.Id = 3, .name = "Google Maps APIs"},
                        New GoogleApiTypeGroup() With {.Id = 4, .name = "Advertising APIs"},
                        New GoogleApiTypeGroup() With {.Id = 5, .name = "Google Apps APIs"},
                        New GoogleApiTypeGroup() With {.Id = 6, .name = "Other popular APIs"},
                        New GoogleApiTypeGroup() With {.Id = 7, .name = "Mobile APIs"},
                        New GoogleApiTypeGroup() With {.Id = 8, .name = "Social APIs"})
                    ' Attempt to save the changes.
                    newContext.SaveChanges()
                    ' Turn off the identity insert setting when done.
                    newContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT GoogleApiTypeGroups OFF")

                    ' Turn on identity insert before updating
                    newContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT GoogleApiTypes ON")
                    ' Make sure the expected GoogleApiTypes exist with the correct names, IDs, and references to their corresponding GoogleApiTypeGroup.
                    newContext.GoogleApiTypes.AddOrUpdate(
                        Function(x) x.Id,
                        New GoogleApiType() With {.Id = 1, .name = "Google Maps JavaScript API", .GoogleApiTypeGroupId = 3})
                    ' Save the changes
                    newContext.SaveChanges()
                    ' Turn off the identity insert setting when done.
                    newContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT GoogleApiTypes ON")

                    ts.Commit()
                Catch ex As Exception
                    ts.Rollback()
                    Throw
                End Try
            End Using

        End Sub

    End Class

End Namespace

score 3 · Answer 3 · answered May 11 '16 at 22:26

After researching into this it looks like if the key was previously created and then you add [DatabaseGenerated(DatabaseGeneratedOption.None)] in a migration it wont actually do what you intend, you can check this by going to the database explorer Table -> Keys -> PK -> Modify and see the Identity Specification is set to Yes instead of No.

If this is the case try migrating down to a point where that table did not exist and then remigrate back up.

You really made my day! Thanks! – PSkalka Apr 10 '19 at 15:36 — PSkalka, Apr 10 '19 at 15:36

score 0 · Answer 4 · edited Jul 19 '17 at 14:07

0

If you are using AutoMapper and using for/foreach mode, you must remap in for loop.

Example:

foreach (var item in Ids) {
    var page = Mapper.Map<Pages>(model);
    .
    .
    .
    .
    db.Pages.Add(page);
}
db.SaveChanges();

edited Jul 19 '17 at 14:07

Hussein El Feky

6,627
5
44
57

answered Jul 19 '17 at 13:45

macit

1
3

Performing an IDENTITY_INSERT using EF5 Code First Migrations

4 Answers4

In case anyone is still confused . . .

Linked