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?

Solution 1

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);
    }

Solution 2

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

Solution 3

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.

Comments

[bryangm]

Thanks @Aron, I successfully inserted into the identity field using the attributes method you specified.

[ProfK]

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.

[PSkalka]

You really made my day! Thanks!