Entity Framework - Migrations - Code First - Seeding per Migration

Question

I am looking into Migrations in an effort to clean up our deployment processes. The less manual intervention required when pushing a change to production the better.

I have run into 3 major snags with the migrations system. They are show stoppers if I can not figure out a clean way around them.

1. How do I add Seed data per migration:

I execute the command "add-migration" which scaffolds a new migration file with Up and Down functions. Now, I want to automatically make changes to the data with both Up and Down changes. I don't want to add the Seed data to the Configuration.Seed method as this runs for all migrations which ends in all sorts of duplication problems.

2. If the above is not possible, how do I avoid duplications?

I have an enum that I loop through to add the values to the database.

foreach(var enumValue in Enum.GetValues(typeof(Access.Level)))
{
    context.Access.AddOrUpdate(
        new Access { AccessId = ((int)enumValue), Name = enumValue.ToString() }
    );
}
context.SaveChanges();

Even though I am using AddOrUpdate, I still get duplicates in the database. The above code brings me to my 3rd and final problem:

3. How can I seed Primary Keys?

My enumerable with the above code is:

public class Access
{
    public enum Level
    {
        None = 10,
        Read = 20,
        ReadWrite = 30
    }
    public int AccessId { get; set; }
    public string Name { get; set; }
}

I am specifying the values that I want as my primary key, but Entity Framework seems to ignore it. They still end up being 1,2,3. How do I get it to be 10,20,30?

Are these limitations of EF at the moment or are they intentional constraints to prevent some other kind of catastrophe I am not seeing?

Weatherford

Answer 1

1. When I have fixed data that I want to insert with a migration, I put the inserts directly in the Up() migration using calls to Sql("Insert ..."). See the note halfway down this page: how to insert fixed data
2. You prevent duplicates in the Seed method by calling the AddOrUpdate overload that takes an identifier expression specifying the natural key - see this answer and this blog entry.
3. Primary keys that are integers are created as identity fields by default. To specify otherwise use the [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute

I think this is a good explanation of Initializer and Seed methods

Here is an example of how to use the AddOrUpdate method:

foreach(var enumValue in Enum.GetValues(typeof(Access.Level)))
{
    context.Access.AddOrUpdate(
        x => x.Name, //the natural key is "Name"
        new Access { AccessId = ((int)enumValue), Name = enumValue.ToString() }
    );
}

Szymanowski

If you want to go the SQL route but have some trouble writing all queries with string escaping or verbatim strings then you can also use the SqlResource() method. See jasoncavett.com/blog/… Maybe that's interesting to mention in your answer? – Push

Answer 2

As a possible solution to item 1, I made an implementation of the IDatabaseInitializer strategy which will run the Seed method of each pending migration only, you will need to implement a custom IMigrationSeed interface in each of your DbMigration classes, the Seed method will then be implemented right after Up and Down methods of every migration class.

This helps to solve two problems for me:

1. Group Database Model Migration with Database Data Migration (or Seeding)
2. Check what part of the Seed migration code should really be running, not checking data in the database but using already known data which is the database model that was just created.

The interface looks like this

public interface IMigrationSeed<TContext>
{
    void Seed(TContext context);
}

Below is the new implementation that will call this Seed method

public class CheckAndMigrateDatabaseToLatestVersion<TContext, TMigrationsConfiguration>
    : IDatabaseInitializer<TContext>
    where TContext : DbContext
    where TMigrationsConfiguration : DbMigrationsConfiguration<TContext>, new()
{
    public virtual void InitializeDatabase(TContext context)
    {
        var migratorBase = ((MigratorBase)new DbMigrator(Activator.CreateInstance<TMigrationsConfiguration>()));

        var pendingMigrations = migratorBase.GetPendingMigrations().ToArray();
        if (pendingMigrations.Any()) // Is there anything to migrate?
        {
            // Applying all migrations
            migratorBase.Update();
            // Here all migrations are applied

            foreach (var pendingMigration in pendingMigrations)
            {
                var migrationName = pendingMigration.Substring(pendingMigration.IndexOf('_') + 1);
                var t = typeof(TMigrationsConfiguration).Assembly.GetType(
                    typeof(TMigrationsConfiguration).Namespace + "." + migrationName);

                if (t != null 
                   && t.GetInterfaces().Any(x => x.IsGenericType 
                      && x.GetGenericTypeDefinition() == typeof(IMigrationSeed<>)))
                {
                    // Apply migration seed
                    var seedMigration = (IMigrationSeed<TContext>)Activator.CreateInstance(t);
                    seedMigration.Seed(context);
                    context.SaveChanges();
                }
            }
        }
    }
}

The good thing here is you have a real EF context to manipulate Seed Data, just like standard EF Seed implementation. However this can get strange if for example you decide to delete a table that was Seeded in a previous migration, you will have to refactor your existing Seed code accordingly.

EDIT: As an alternative to implement the seed method after the Up and Down, you can create a partial class of the same Migration class, I found this useful as it allows me to safely delete the migration class when I want to re-seed the same migration.

Fadil

This is genius!!! You need WAY more points for this. The only changes I made was a try/finally around the Update, so that the Seeds would continue if one migration failed. Also after Update, called GetDatabaseTransaction() and compared to pending, so that only the successful migrations would Seed. Also wrapped Seed call in it's own transaction (again, just in case one failed.) – Skippy

Wow, man! I was looking whole day for database seeding done right when migration is enabled and eventually found this. – Dynast

I used to be very enthusiastic about this answer but it has serious drawbacks: 1) There are no transactions for each migration's seeding method and also no coupling between the Up and the Seed methods (which gets run later). Once the Up method succeeds you have just one shot for the Seed method to work. 2) The Seed method is hard to test when it can only get called once, most of the times you are working on an updated database. Trying to run Update-Database again will not have your migration in the pendingMigrations list anymore and hence the seed method will never be called again.... – Push

... 3) Because the Seed method is never called again you might forget to update them when your database changes. I had one example where one of these Seed methods would insert a default user. At some point the database was changed to require that all user details are filled in (i.e. entity properties are not nullable) but the Seed method did not initialize those properties. End result: existing installations would work because the Seed method was called in the past when it was correct, new installations try to insert an entity which cannot be stored in the current database model. – Push

@Push regarding 1, did you see Joshua changes in his comment? He improved the exception/transaction handling and regarding 2 I really don't understand what you expect to do. This strategy objective is tu run the Seed ONLY IF the Migration is run, of course you only migrate database once, so Seed will only execute once per migration which is the desired behavior (thus avoiding duplicated data insertion, etc.). In case you want to test the Seed I would suggest undoing the migrations and then migrating again (use --TargetMigration option) Thanks for your comments. – Fadil

The "run once" behavior is OK, but only for the "happy flow". I just want to put a warning out there that there is no built-in retry when this solution fails, and deployment to production will not likely allow you to use --TargetMigration. The Up() method is different: it can fail many times. Because it runs in a transaction it rolls back any failed changes and stops the next migration from running so that running Update-Database (or the Initializer in your software) and fixing problems works out. Joshua's fix won't solve that, therefore I now prefer to do seeding using SQL in the Up() method. – Push

Just used this code. One migration adds new DB Columns and Seed method populates them from values in old/related columns. Second migration then drops old columns. One small tweak, I added a constraint to the IMigrationSeed interface "where TContext: DbContext" – Chauffeur

Doesn't work with PMC:> Update-Database. Not sure why. It runs but GetPendingMigrations() is empty. Works amazingly great with normal bootup though (dbcontext first access) – Lustrum

Answer 3

Hi I have found a very useful information for your problem in this link: Safari Books Online

"1. How do I add Seed data per migration:" As you see in the example you need to create a new confiugration for seeding. This seed Configuration must be called after migration.

public sealed class Configuration : DbMigrationsConfiguration
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(SafariCodeFirst.SeminarContext context)
    {
        //  This method will be called after migrating to the latest version.

        //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
        //  to avoid creating duplicate seed data. E.g.
        //
        //    context.People.AddOrUpdate(
        //      p => p.FullName,
        //      new Person { FullName = "Andrew Peters" },
        //      new Person { FullName = "Brice Lambson" },
        //      new Person { FullName = "Rowan Miller" }
        //    );
        //
    }
}

"2. If the above is not possible, how do I avoid duplications?"

AddOrUpdate Must help you exactly to avoding the duplicates if you get an error here you might have a configuration error post the call stack please. See the example!

"3. How can I seed Primary Keys?"

Here it is also on your key definition. If your key DatabaseGenerated(DatabaseGeneratedOption.Identity) than you do not have to provide it. In some other senarios you need to create a new one it is depending on the key type.

"Are these limitations of EF at the moment or are they intentional constraints to prevent some other kind of catastrophe I am not seeing?"
Not that I know!

Grefer

I have got my data in that Seed method. But it keeps adding duplicates even though I use AddOrUpdate. The problem is when I use "add-migration", it does not create it's own configuration.seed. So no matter which migration you execute, it still executes the common Seed method which is not what I want. I want to have a separate Seed method per Migration file. – Weatherford

look I have a similar problem. What I did in the DbMigrationsConfiguration construcator; You have to set the MigrationsNamespace for example this.MigrationsNamespace = "DataAccessLayer.Repository.Migrations"; and in the wished migration file you have to modfiy the namespace according the DbMigrationsConfiguration. This trick I have founded by my self after long fight and now Entity Framework will go only in the wished migration file. I hope this will solve your problem 2. – Grefer

I think in the end of the day, migrations are still in an infant stage and need some time to evolve. I have added what I ended up doing, Sounds like you created a whole new migrations folder with a single migration file in each. I will try it one day but right now I have already wasted too much time and need to hurry on. Thanks for the help! – Weatherford

The first parameter of the AddOrUpdate method is for preventing duplicates. In the above example, if there is an existing "FullName" that matches, then it does not update. So if you are getting duplicates, check that parameter. – Hemocyte

Answer 4

OK, so with a bit of bashing I have managed to bash EF into submission. Here is what I did:

1. There is no way that I found to see data for a specific migration. It all must go into the common Configuration.Seed method.

2. To avoid duplicates I had to do 2 things. For my enums, I wrote the following seed code:

foreach (var enumValue in Enum.GetValues(typeof(Access.Level)))
{
    var id = (int)enumValue;
    var val = enumValue.ToString();

    if(!context.Access.Any(e => e.AccessId == id))
        context.Access.Add(
            new Access { AccessId = id, Name = val }
        );
}
context.SaveChanges();

So basically, just checking if it exists and adding if not

3. In order for the above to work, you need to be able to insert Primary Key Values. Luckily for me this table will always have the same static data so I could deactivate the auto increment. To do that, the code looks like:

public class Access
{
    public enum Level
    {
        None = 10,
        Read = 20,
        ReadWrite = 30
    }

    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int AccessId { get; set; }
    public string Name { get; set; }
}

Weatherford