Seeding data will not work when schema changes wit

问题:

Okay so I am using Entity Framework 6.1 and attempting code first with seeding. I have a drop always intializer that ALWAYS WORKS. However I want to use database migration which I have set up and it works. UNTIL I normalize out a table and then try to seed it I get a primary key error.

Basically in my context when I uncomment out the changes in the 'TODO' section and the schema changes I get a primary key violation when attempting population of the newly normalized out table. It will work for the initializer that does the drop always, but I want my migration data table and not to drop the database everytime I make changes in case I want to rollback ever. I have tried changing the attribute of the 'PersonId' to Identity and to None and back to Identity. So the caveat is if it is set to 'Identity' it will work but the values will keep incrementing to higher values each time 1,2,3,4 then 5,6,7,8;etc. If I set it to none it works the first time and then when it is split in the mapping and normalized it blows up. I have tried custom dbcc commands and it does not like that either, as even with setting dbcc to reseed with the two new tables it does not like it. It is as if it has no idea about seeding the new table when being done explicitly.

Does anyone know how to do a seeding process that the model can handle if you normalize out the mapping of an object to multiple tables? I am trying a bunch of different patterns and getting nowhere fast.

So POCO Object

public class Person
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int PersonId { get; set; }
        [Column(TypeName = "varchar")]
        [Required]
        [MaxLength(32)]
        public string FirstName { get; set; }
        [Column(TypeName = "varchar")]
        [Required]
        [MaxLength(32)]
        public string LastName { get; set; }

        [Column(TypeName = "varchar")]
        public string OverlyLongDescriptionField { get; set; }
}

Context for code First:

public class EasyContext : DbContext
    {
        public EasyContext() : base("name=EasyEntity")
        {
            //Database.SetInitializer<EasyContext>(new EasyInitializer());

            Database.SetInitializer(new MigrateDatabaseToLatestVersion<EasyContext, Migrations.Configuration>("EasyEntity"));
        }

        public DbSet<ProductOrder> ProductOrder { get; set; }
        public DbSet<Person> Person { get; set; }
        public DbSet<Product> Product { get; set; }
        public DbSet<Audit>  Backup { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("dbo");
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

            //TODO Let's normalize out a long descriptive field
            //modelBuilder.Entity<Person>()
            //.Map(m =>
            //{
            //    m.Properties(p => new { p.FirstName, p.LastName });
            //    m.ToTable("Person");
            //})
            //.Map(m =>
            //{
            //    m.Properties(p => new { p.OverlyLongDescriptionField });
            //    m.ToTable("PersonDescription");
            //});
        }
    }

Initializer for DropCreateAlways:

public class EasyInitializer : DropCreateDatabaseAlways<EasyContext>
    {
        protected override void Seed(EasyContext context)
        {
            SeedingValues.SeedingForDatabaseDrop(context);

            base.Seed(context);
        }
    }

Configuration for migrations:

internal sealed class Configuration : DbMigrationsConfiguration<EasyEntity.EasyContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
            ContextKey = "EasyEntity.EasyContext";
        }

        protected override void Seed(EasyContext context)
        {
            SeedingValues.SeedingWithoutDatabaseDrop(context);

            base.Seed(context);
        }
    }

Base Seeding class:

internal static class SeedingValues
{
    public static void SeedingForDatabaseDrop(EasyContext context)
    {
        BaseSeed(context);
    }

    public static void SeedingWithoutDatabaseDrop(EasyContext context)
    {
        context.Person.ClearRange();

        BaseSeed(context);
    }

    private static void BaseSeed(EasyContext context)
    {
        IList<Person> persons = new List<Person>
        {
            new Person { PersonId = 1, FirstName = "Brett", LastName = "Guy", OverlyLongDescriptionField = "OMG Look I have a bunch of text denormalizing a table by putting a bunch of stuff only side related to the primary table." },
            new Person { PersonId = 2, FirstName = "Neil", LastName = "Person"},
            new Person { PersonId = 3, FirstName = "Ryan", LastName = "Other"},
            new Person { PersonId = 4, FirstName = "Aaron", LastName = "Dude"},
        };

        foreach (var person in persons)
            context.Person.AddOrUpdate(person);
    }
}

ClearingHelper

public static void ClearRange<T>(this DbSet<T> dbSet) where T : class
{
    using (var context = new EasyContext())
    {
       dbSet.RemoveRange(dbSet);
    }
 }

回答1:

Okay so the issue is with a newly created table being not populated and the old table being populated. So if I have following off of my example a POCO class 'Person' and have plurilization removed. Without any explicit mapping and just a DbSet will create a table Person. If I then do my mapping to split tables.

modelBuilder.Entity<Person>()
.Map(m =>
{
    m.Properties(p => new { p.PersonId, p.FirstName, p.LastName });
    m.ToTable("Person");
})
.Map(m =>
{
    m.Properties(p => new { p.PersonId, p.OverlyLongDescriptionField });
    m.ToTable("PersonDescription");
});

I get a Primary Key violation with my seeding process. This is due to if I look at the database newly updated it still is retaining the old table and created a new one. However it does not know how to remove the data with this method:

public static void ClearRange<T>(this DbSet<T> dbSet) where T : class
{
    using (var context = new EasyContext())
    {
        dbSet.RemoveRange(dbSet);
    }
}

Becase the set is partial. So I am thinking: "Well if my data for seeding is contained at this point and I need to alter it moving forward I can in theory just clean up the tables directly with SQL commands." This is not the approach I wanted per say but it does work.

So I add more data to my clearing helper:

public static void ResetIdentity(string tableName)
{
    using (var context = new EasyContext())
    {
        context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('{tableName}', RESEED, 0)");
    }
}

public static void DeleteTable(string tableName)
{
    using (var context = new EasyContext())
    {
        context.Database.ExecuteSqlCommand($"DELETE {tableName}");
    }
}

public static void DeleteTableAndResetIdentity(string tableName)
{
    using (var context = new EasyContext())
    {
        context.Database.ExecuteSqlCommand($"DELETE {tableName}");
        context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('{tableName}', RESEED, 0)");
    }
}

Then I add this to my Seeding routine's cleanup portion:

ClearingHelper.DeleteTable("dbo.PersonDescription");
ClearingHelper.DeleteTableAndResetIdentity("dbo.Person");

This is unfortunate in two ways to do it this way in that:

1. It is slower doing a delete as it goes row by row.
2. If I migrate backwards I will have to change this.

But it works! I can now have changes to the schema with normalizing out POCO's and still run a seeding routine.