Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Performing an IDENTITY_INSERT using EF5 Code First Migrations

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?

like image 626
bryangm Avatar asked Mar 06 '13 21:03

bryangm


3 Answers

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);
    }
like image 51
Aron Avatar answered Sep 19 '22 14:09

Aron


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
like image 30
Ross Brasseaux Avatar answered Sep 19 '22 14:09

Ross Brasseaux


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.

like image 23
Nick Spicer Avatar answered Sep 18 '22 14:09

Nick Spicer