New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Seed Data #629

Closed
bricelam opened this Issue Sep 4, 2014 · 73 comments

Comments

Projects
None yet
@bricelam
Member

bricelam commented Sep 4, 2014

In EF6, this was accomplished using DbMigrationsConfiguration.Seed(), but migrations configurations don't exist in EF7.

  • Add EntityTypeBuilder.SeedData
  • Add support for shared table seeding
  • Add support for owned types seeding
  • Add seed data validation
  • Reuse existing code by delegating to IStateManager, IEntityMaterializerSource and SharedTableEntryMap
  • Documentation: aspnet/EntityFramework.Docs#509
  • Add support for navigation seeding: #10000
  • Add EntityTypeBuilder.SeedData sugar that takes an entity and a property bag for shadow properties: #9999
  • Perf: batch the operations: #9997
  • Perf: skip optimistic concurrency checks: #9998

@bricelam bricelam added the enhancement label Sep 4, 2014

@rowanmiller rowanmiller added this to the Backlog milestone Sep 5, 2014

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Oct 9, 2014

Member

Proposal is to have a Seed.cs file in the Migrations directory (alongside model snapshot etc.). We could scaffold this when the first migration is added:

public class BloggingContextSeedData : DatabaseSeedData<BloggingContext>
{
    public override void Seed(BloggingContext context)
    { 
    }
}

This is essentially the same as the Seed method on EF6.x. While this approach has it's issues it seems to work pretty well and we haven't heard a lot of complaints.

We could also look at some simple Insert/Update/Delete APIs in Migrations. This would give folks a more advanced option that overcomes some of the limitations of Seed():

  • Removes the need to write AddOrUpdate code since the data is inserted once during the migration (and presumably deleted during the down).
  • Removes the need to update the Seed code whenever the model changes (since it is applied at a well known point in time of the schema.

Of course, data in migrations would be loosely typed since it wouldn't use the current model.

Member

rowanmiller commented Oct 9, 2014

Proposal is to have a Seed.cs file in the Migrations directory (alongside model snapshot etc.). We could scaffold this when the first migration is added:

public class BloggingContextSeedData : DatabaseSeedData<BloggingContext>
{
    public override void Seed(BloggingContext context)
    { 
    }
}

This is essentially the same as the Seed method on EF6.x. While this approach has it's issues it seems to work pretty well and we haven't heard a lot of complaints.

We could also look at some simple Insert/Update/Delete APIs in Migrations. This would give folks a more advanced option that overcomes some of the limitations of Seed():

  • Removes the need to write AddOrUpdate code since the data is inserted once during the migration (and presumably deleted during the down).
  • Removes the need to update the Seed code whenever the model changes (since it is applied at a well known point in time of the schema.

Of course, data in migrations would be loosely typed since it wouldn't use the current model.

@rowanmiller rowanmiller removed this from the Backlog milestone Oct 9, 2014

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Oct 9, 2014

Member

BTW if we do Seed.cs we'll probably want something like the AddOrUpdate method. If so, we should consider the ability to whitelist properties to be updated, as discussed in #453.

Member

rowanmiller commented Oct 9, 2014

BTW if we do Seed.cs we'll probably want something like the AddOrUpdate method. If so, we should consider the ability to whitelist properties to be updated, as discussed in #453.

@rowanmiller rowanmiller added this to the Backlog milestone Oct 10, 2014

@giggio

This comment has been minimized.

Show comment
Hide comment
@giggio

giggio Dec 12, 2014

We should be able to add data along with a specific migration. For example, I add a new table and then add some data to it right after the table is created.
This is really important for domain data, that mainly changes when some app structure changes. An example is menu records, where I mostly only add a new menu record when I add a new feature.
So, it would be great to have some methods to add/change data on the MigrationBuilder class.

giggio commented Dec 12, 2014

We should be able to add data along with a specific migration. For example, I add a new table and then add some data to it right after the table is created.
This is really important for domain data, that mainly changes when some app structure changes. An example is menu records, where I mostly only add a new menu record when I add a new feature.
So, it would be great to have some methods to add/change data on the MigrationBuilder class.

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Dec 12, 2014

Member

@giggio Totally agree, having some sort of first class data manipulation API would be great. This issue is specifically about expressing some seed data in terms of the current model, but your scenario would also be very good to enable. The data migration would need to be weakly typed rather than using the model (since the model will change over time).

Member

rowanmiller commented Dec 12, 2014

@giggio Totally agree, having some sort of first class data manipulation API would be great. This issue is specifically about expressing some seed data in terms of the current model, but your scenario would also be very good to enable. The data migration would need to be weakly typed rather than using the model (since the model will change over time).

@hbopuri

This comment has been minimized.

Show comment
Hide comment
@hbopuri

hbopuri Jan 25, 2015

How is Seed method handled in EF7?

protected override void Seed(MyContext context){}

hbopuri commented Jan 25, 2015

How is Seed method handled in EF7?

protected override void Seed(MyContext context){}

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Jan 26, 2015

Member

@hbopuri - There isn't a first class API for this at the moment (though this work item is tracking adding one in the future). Currently you would just write code in app startup to handle seed data.

Member

rowanmiller commented Jan 26, 2015

@hbopuri - There isn't a first class API for this at the moment (though this work item is tracking adding one in the future). Currently you would just write code in app startup to handle seed data.

@hbopuri

This comment has been minimized.

Show comment
Hide comment
@hbopuri

hbopuri Jan 26, 2015

@rowanmiller wasn't override seed part of earlier EF versions? is EF7 a upgraded version of earlier versions or a completely newly designed from scratch?

HaBo Rocks
Harsha Bopuri

hbopuri commented Jan 26, 2015

@rowanmiller wasn't override seed part of earlier EF versions? is EF7 a upgraded version of earlier versions or a completely newly designed from scratch?

HaBo Rocks
Harsha Bopuri

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Jan 26, 2015

Member

@hbopuri - It's a major version and has breaking changes in it. These breaking changes are larger than we would typically do in a release, but you can read more about the reasoning behind what we are doing here http://blogs.msdn.com/b/adonet/archive/2014/10/27/ef7-v1-or-v7.aspx.

Member

rowanmiller commented Jan 26, 2015

@hbopuri - It's a major version and has breaking changes in it. These breaking changes are larger than we would typically do in a release, but you can read more about the reasoning behind what we are doing here http://blogs.msdn.com/b/adonet/archive/2014/10/27/ef7-v1-or-v7.aspx.

@heathyates

This comment has been minimized.

Show comment
Hide comment
@heathyates

heathyates Feb 24, 2015

@rowanmiller Will this feature ship with the RTM? And totally agree with @giggio suggestion.

@rowanmiller Will this feature ship with the RTM? And totally agree with @giggio suggestion.

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Feb 24, 2015

Member

@heathyates - Not sure if we'll get a seed method equivalent into initial RTM. It is super easy to write the logic in your app start code in the meantime.

Member

rowanmiller commented Feb 24, 2015

@heathyates - Not sure if we'll get a seed method equivalent into initial RTM. It is super easy to write the logic in your app start code in the meantime.

@jwdavidson

This comment has been minimized.

Show comment
Hide comment
@jwdavidson

jwdavidson Jun 23, 2015

@rowanmiller - It is easy to write a seed method using the app startup code. However, all the samples that I could find incorrectly use EnsureCreatedAsync and as a result fail to load the seed data. What is missing is a way to determine if the DbContext is correctly configured and has the model applied that matches the seed data requirement. This could be solved by having a way to detect if a particular Migration has been applied to the datastore.

@rowanmiller - It is easy to write a seed method using the app startup code. However, all the samples that I could find incorrectly use EnsureCreatedAsync and as a result fail to load the seed data. What is missing is a way to determine if the DbContext is correctly configured and has the model applied that matches the seed data requirement. This could be solved by having a way to detect if a particular Migration has been applied to the datastore.

@heathyates

This comment has been minimized.

Show comment
Hide comment
@heathyates

heathyates Jun 23, 2015

@jwdavidson I have not experienced a failure to load the seed data and I use EnsureCreatedAsync. Has the mvc music store failed for you? I'm not implying I don't believe you or your claim, but confused as to what samples are not working for you? Would be interested in duplicating what you have observed myself. :-)

@jwdavidson I have not experienced a failure to load the seed data and I use EnsureCreatedAsync. Has the mvc music store failed for you? I'm not implying I don't believe you or your claim, but confused as to what samples are not working for you? Would be interested in duplicating what you have observed myself. :-)

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Jun 23, 2015

Member

@jwdavidson do you want to apply migrations during startup to ensure the database is up to date? If so, context.Database.AsRelational().ApplyMigraions() is the API to do this.

Member

rowanmiller commented Jun 23, 2015

@jwdavidson do you want to apply migrations during startup to ensure the database is up to date? If so, context.Database.AsRelational().ApplyMigraions() is the API to do this.

@jwdavidson

This comment has been minimized.

Show comment
Hide comment
@jwdavidson

jwdavidson Jun 24, 2015

@heathyates - The case where EnsureCreatedAsync is when it returns true, which it does when the database was not previously created and/or the initial migration has not be loaded to the database. After that if you want to load seed data which uses a second migration then it will fail as EnsureCreatedAsync will return false.

@rowanmiller - the suggested ApplyMigrations() process may enable the seed function to be reliably processed from the startup code. I will try that tomorrow and let you know.

@heathyates - The case where EnsureCreatedAsync is when it returns true, which it does when the database was not previously created and/or the initial migration has not be loaded to the database. After that if you want to load seed data which uses a second migration then it will fail as EnsureCreatedAsync will return false.

@rowanmiller - the suggested ApplyMigrations() process may enable the seed function to be reliably processed from the startup code. I will try that tomorrow and let you know.

@jwdavidson

This comment has been minimized.

Show comment
Hide comment
@jwdavidson

jwdavidson Jun 24, 2015

@rowanmiller - the following code block seems to do exactly what I want: ensure the database exists and the relevant migrations have been applied

using (DbContext db = (DbContext)serviceProvider.GetService<ApplicationDbContext>()) {
    if (!db.Database.AsRelational().Exists()) {
        db.Database.AsRelational().Create();
    }
    db.Database.AsRelational().ApplyMigrations();
}

After this any data insertion routines may be safely added. Thanks.

@rowanmiller - the following code block seems to do exactly what I want: ensure the database exists and the relevant migrations have been applied

using (DbContext db = (DbContext)serviceProvider.GetService<ApplicationDbContext>()) {
    if (!db.Database.AsRelational().Exists()) {
        db.Database.AsRelational().Create();
    }
    db.Database.AsRelational().ApplyMigrations();
}

After this any data insertion routines may be safely added. Thanks.

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Jun 24, 2015

Member

@jwdavidson you should be able to remove the existence check and creation. ApplyMigrations() will take care of creating the database if it doesn't exist.

using (DbContext db = (DbContext)serviceProvider.GetService<ApplicationDbContext>()) {
    Database.AsRelational().ApplyMigrations();
}
Member

rowanmiller commented Jun 24, 2015

@jwdavidson you should be able to remove the existence check and creation. ApplyMigrations() will take care of creating the database if it doesn't exist.

using (DbContext db = (DbContext)serviceProvider.GetService<ApplicationDbContext>()) {
    Database.AsRelational().ApplyMigrations();
}
@jwdavidson

This comment has been minimized.

Show comment
Hide comment
@jwdavidson

jwdavidson Jun 24, 2015

@rowanmiller - Yes, that works as advertised, just not used to doing something like this without an "if" validation. Thanks again

@rowanmiller - Yes, that works as advertised, just not used to doing something like this without an "if" validation. Thanks again

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Sep 17, 2015

Member

@weitzhandler good discussion on the best pattern for the moment going on at #3070

Member

rowanmiller commented Sep 17, 2015

@weitzhandler good discussion on the best pattern for the moment going on at #3070

@divega divega removed this from the Backlog milestone Nov 14, 2015

@divega

This comment has been minimized.

Show comment
Hide comment
@divega

divega Nov 14, 2015

Member

Clearing the milestone after talking about this with @DamianEdwards and @rowanmiller. It is really hard to find the right place to do seeding, in particular in an ASP.NET application. We should at least consider trying to have a solution for RTM.

Member

divega commented Nov 14, 2015

Clearing the milestone after talking about this with @DamianEdwards and @rowanmiller. It is really hard to find the right place to do seeding, in particular in an ASP.NET application. We should at least consider trying to have a solution for RTM.

@summer600

This comment has been minimized.

Show comment
Hide comment
@summer600

summer600 Feb 19, 2016

I Always use seed data for system records that have identity values outside the range of the identity. Mostly I use negative values and have the identity simply start at 1. I then know which values are for what "system" data, which is also the same across all instances of the databases. As part of seeding, but also in general how could you insert a record with an identity PK with a given PK value. In effect will inserting records with -- adhoc IDENTITY INSERT ON -- be supported or what workaround would you suggest?.

I Always use seed data for system records that have identity values outside the range of the identity. Mostly I use negative values and have the identity simply start at 1. I then know which values are for what "system" data, which is also the same across all instances of the databases. As part of seeding, but also in general how could you insert a record with an identity PK with a given PK value. In effect will inserting records with -- adhoc IDENTITY INSERT ON -- be supported or what workaround would you suggest?.

@rowanmiller

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Feb 19, 2016

Member

@summer600 it requires a little bit of a workaround, but you can do this already - http://docs.efproject.net/en/latest/saving/explicit-values-generated-properties.html#explicit-values-into-sql-server-identity-columns. If you set a value on a PK property (i.e. it's not just the CLR default for the property type) then EF Core will attempt to write that to the database for you.

Member

rowanmiller commented Feb 19, 2016

@summer600 it requires a little bit of a workaround, but you can do this already - http://docs.efproject.net/en/latest/saving/explicit-values-generated-properties.html#explicit-values-into-sql-server-identity-columns. If you set a value on a PK property (i.e. it's not just the CLR default for the property type) then EF Core will attempt to write that to the database for you.

@gdoron

This comment has been minimized.

Show comment
Hide comment
@gdoron

gdoron Feb 21, 2016

@divega

Clearing the milestone after talking about this with @DamianEdwards and @rowanmiller. It is really hard to find the right place to do seeding

Were you referring to seeding before\after each migration or even for a single seed? I'm curious to know why it is so complicated, is it not as easy as just running the DML SQL scripts after running the DDL migration scripts?

We should at least consider trying to have a solution for RTM.

(If you referred to seed per migration) I think so too, 90~% of the times I had to change the structure of my tables I also had to run DML migration scripts.

As a temporary solution, I would write the raw SQL inside the migration with

migrationBuilder.Sql(@"{{DoMagicHere}}");

gdoron commented Feb 21, 2016

@divega

Clearing the milestone after talking about this with @DamianEdwards and @rowanmiller. It is really hard to find the right place to do seeding

Were you referring to seeding before\after each migration or even for a single seed? I'm curious to know why it is so complicated, is it not as easy as just running the DML SQL scripts after running the DDL migration scripts?

We should at least consider trying to have a solution for RTM.

(If you referred to seed per migration) I think so too, 90~% of the times I had to change the structure of my tables I also had to run DML migration scripts.

As a temporary solution, I would write the raw SQL inside the migration with

migrationBuilder.Sql(@"{{DoMagicHere}}");
@divega

This comment has been minimized.

Show comment
Hide comment
@divega

divega Feb 21, 2016

Member

That comment was referring to the fact that in EF Core we don't have a pattern or API to run seeding (single method or not) after applying migrations. That sends users looking for ways to execute the seeding logic when the application runs, but that itself has lots of issues and no generally correct solution.

Member

divega commented Feb 21, 2016

That comment was referring to the fact that in EF Core we don't have a pattern or API to run seeding (single method or not) after applying migrations. That sends users looking for ways to execute the seeding logic when the application runs, but that itself has lots of issues and no generally correct solution.

@rowanmiller rowanmiller modified the milestones: 1.0.1, 1.0.0 May 4, 2016

@rowanmiller rowanmiller removed the pri0 label Jul 6, 2016

@rowanmiller rowanmiller added this to the Backlog milestone Jul 29, 2016

@brunoalreis

This comment has been minimized.

Show comment
Hide comment
@brunoalreis

brunoalreis Jul 24, 2017

@jceddy, I think you can. I have two different scenarios, in two different applications. In one i am using Sqlite and in the other i am using PostgreSQL. In the Sqlite migrationBuilder.ActiveProvider has "Microsoft.EntityFrameworkCore.Sqlite" and in the PostgreSQL migrationBuilder.ActiveProvider has "Npgsql.EntityFrameworkCore.PostgreSQL". Don't know if is the best way to check database type, but it works.

@jceddy, I think you can. I have two different scenarios, in two different applications. In one i am using Sqlite and in the other i am using PostgreSQL. In the Sqlite migrationBuilder.ActiveProvider has "Microsoft.EntityFrameworkCore.Sqlite" and in the PostgreSQL migrationBuilder.ActiveProvider has "Npgsql.EntityFrameworkCore.PostgreSQL". Don't know if is the best way to check database type, but it works.

@DalSoft

This comment has been minimized.

Show comment
Hide comment
@DalSoft

DalSoft Jul 27, 2017

@jceddy take a look at my post on EF Core seeding.

If you use my technique you can pass environment switches, and more importantly just use the normal EF context. This means you don't have to do custom SQL for each provider.

DalSoft commented Jul 27, 2017

@jceddy take a look at my post on EF Core seeding.

If you use my technique you can pass environment switches, and more importantly just use the normal EF context. This means you don't have to do custom SQL for each provider.

@jceddy

This comment has been minimized.

Show comment
Hide comment
@jceddy

jceddy Jul 27, 2017

@brunoalreis @DalSoft I ended up creating an empty migration after the initial schema creation one and added code to seed data in the empty migration.

jceddy commented Jul 27, 2017

@brunoalreis @DalSoft I ended up creating an empty migration after the initial schema creation one and added code to seed data in the empty migration.

@divega divega assigned AndriySvyryd and unassigned bricelam Jul 27, 2017

@divega divega modified the milestones: 2.1.0, Backlog Jul 27, 2017

@divega divega modified the milestones: Backlog, 2.1.0 Aug 11, 2017

@AndriySvyryd AndriySvyryd reopened this Sep 13, 2017

AndriySvyryd added a commit that referenced this issue Sep 22, 2017

Add table splitting and inheritance support to data seeding.
Use two new instances of StateManager for the source and target model to be able to reference the correct columns.

Part of #629

AndriySvyryd added a commit that referenced this issue Sep 22, 2017

Add table splitting and inheritance support to data seeding.
Use two new instances of StateManager for the source and target model to be able to reference the correct columns.

Part of #629

AndriySvyryd added a commit that referenced this issue Sep 26, 2017

Add table splitting and inheritance support to data seeding.
Use two new instances of StateManager for the source and target model to be able to reference the correct columns.

Part of #629

AndriySvyryd added a commit that referenced this issue Sep 29, 2017

AndriySvyryd added a commit that referenced this issue Oct 5, 2017

AndriySvyryd added a commit that referenced this issue Oct 5, 2017

Validate seed data
Fix issues with discriminators and table splitting

Part of #629

AndriySvyryd added a commit that referenced this issue Oct 5, 2017

Validate seed data
Fix issues with discriminators and table splitting

Part of #629

AndriySvyryd added a commit that referenced this issue Oct 6, 2017

Validate seed data
Fix issues with discriminators and table splitting

Part of #629

AndriySvyryd added a commit that referenced this issue Oct 6, 2017

Validate seed data
Fix issues with discriminators and table splitting

Part of #629

AndriySvyryd added a commit that referenced this issue Oct 6, 2017

AndriySvyryd added a commit that referenced this issue Oct 6, 2017

AndriySvyryd added a commit that referenced this issue Oct 9, 2017

@AndriySvyryd AndriySvyryd closed this in #9996 Oct 9, 2017

@AndriySvyryd AndriySvyryd removed their assignment Oct 9, 2017

@ToddThomson

This comment has been minimized.

Show comment
Hide comment
@ToddThomson

ToddThomson Oct 22, 2017

I'm a bit late to the party, but I wanted to give the EF Core team input on my requirements for seeding or DbContext data initialization. Perhaps entity SeedData could be made to work for me, but I can't wait for release 2.1.
I feel that the APIs for DbContext data initialization should be added to DbContext. A simple

DbContext.MigrateToLatestVersion( IDbContextInitializer initializerClass, IServiceProvider serviceProvider ) 

which utilizes DbContext.Database.Migrate() and after which calls the seed( dbContext, serviceProvider ) method in the initializer class.

The DbContextInitializer class then has full reign to manipulate the context and persist changes to the repository.

Reading through this issue, this looks like the initial solution proposed by @rowanmiller . It's simple and easy to implement.

I'm a bit late to the party, but I wanted to give the EF Core team input on my requirements for seeding or DbContext data initialization. Perhaps entity SeedData could be made to work for me, but I can't wait for release 2.1.
I feel that the APIs for DbContext data initialization should be added to DbContext. A simple

DbContext.MigrateToLatestVersion( IDbContextInitializer initializerClass, IServiceProvider serviceProvider ) 

which utilizes DbContext.Database.Migrate() and after which calls the seed( dbContext, serviceProvider ) method in the initializer class.

The DbContextInitializer class then has full reign to manipulate the context and persist changes to the repository.

Reading through this issue, this looks like the initial solution proposed by @rowanmiller . It's simple and easy to implement.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment