Performing IDENTITY_INSERT Using the First Steps of EF5 Code First Migrations

Question

I have a POCO that I am trying to create using First First Migrations, and then the seed data. The problem is that I want to insert certain values ​​into the identifier column when sowing.

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 call to AddOrUpdate in the Seed method for 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 ​​101 and 201, but instead of 1 and 2. Are there any DataAttributes that I can apply to the model to help with this?

Answer 1

This is how to disable Identity through 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 disable Identity through EntityTypeConfiguration

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

Or you can use OnModelCreating overload

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

Answer 2

If someone is still confused.,.

See below for more information needed to get the IDENTITY_INSERT to work with the first code's Seid () Segment () method.

I used the Aron implementation of the System.ComponentModel.DataAnnotations.Schema.DatabaseGenerated attribute to set the property generated by the model identifier to No, but I still could not get past the identifier insertion error. I decided that I would post my results here if anyone else had a problem.

To make it work, I wrapped the seed method logic in an SQL transaction and used context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTable ON") to enable insertion before the .AddOrUpdate() method is .AddOrUpdate() . Here is my Configuration.vb file (using the 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 

Answer 3

After exploring this question, it looks like if the key was previously created, and then you added [DatabaseGenerated (DatabaseGeneratedOption.None)] to the migration, it usually does not do what you intend, you can verify this by going to the database explorer table → Keys → PK → Change and you will see that the Identity Specification parameter is set to Yes instead of No.

If so, try moving to the point where this table does not exist, and then redesign your backup.

Answer 4

If you use AutoMapper and use for / foreach mode, you must reassign the for loop.

Example:

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