Seeding data with Entity Framework Core using migrations

Using EF Core migrations to seed the data

As much as it is important for testing the functionality of the application, data seeding can also be used to pre-load some values like lookups not only to development and test environment but also to populate these values in production as well.

Seeding of data can be done in several ways and in this article I will go through two ways of seeding data. Both of them have their pros and cons and it is up to you to pick the way you want to have your data pre-seeded in your application database.

Before we dig into the details of both approaches, we need to do some basic EF Core setup in the project.

I created a project named Seeding.Sample.Data which is .NET Core 3.1 class library project. This project will as I mention contain entities, configurations for entities, migrations and dbContext for the database.

First thing to do is to install packages for EF Core

<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<None Remove="appsettings.json" />
</ItemGroup>
<ItemGroup>
<Content Include="appsettings.json">
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
			<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
			<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
		</Content>
	</ItemGroup>

	<ItemGroup>
		<Folder Include="Migrations\" />
	</ItemGroup>

	<ItemGroup>
		<PackageReference Include="Microsoft.Extensions.Configuration" Version="3.1.6" />
		<PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables" Version="3.1.6" />
		<PackageReference Include="Microsoft.Extensions.Configuration.FileExtensions" Version="3.1.6" />
		<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="3.1.6" />
		<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.6" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.6" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.6">
			<PrivateAssets>all</PrivateAssets>
			<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
		</PackageReference>
	</ItemGroup>

</Project>

    

Apart from packages, you can see from the .csproj file that there is appconfig.json file included with this library. The reason for this is that when you run migrations from the CLI, the library needs to know the connection string to apply create and apply migrations. Since we do not necessary depend of the main application project (ASp.NET Core Web API in this case) we have the config with database connection string

{
  "ConnectionStrings": {
    "SampleDatabase": "data source=.\SQLEXPRESS;initial catalog=Sample;user id=SampleUser;password=$uPeRdUpEr$tRoNgPa$$WoRd;MultipleActiveResultSets=True;PersistSecurityInfo=true;"
  }
}

    

In order to execute migrations from the CLI we need to write implementation class for IDesignTimeDbContextFactory interface, but before we do that we need to have dbConext with entities and configurations wired up.

For this sample I picked simple Lookup implementation that is perfect candidate to demonstrate pre-seeding of values that are equally important on all environments including the production one.

Our simple entities would look like the following

namespace Seeding.Sample.Data.Models
{
    public class LookupType
    {
        public LookupType()
        {
            LookupValues = new List<LookupValue>();
        }

        public int Id { get; set; }
        public String Name { get; set; }

        public virtual ICollection<LookupValue> LookupValues { get; set; }
    }
}
    

namespace Seeding.Sample.Data.Models
{
    public class LookupValue
    {
        public int Id { get; set; }
        public int LookupTypeId { get; set; }
        public virtual LookupType LookupType { get; set; }
        public String Value { get; set; }
    }
}
    

Now when we have the entity structure, before we even create the dbContext we need the configuration which will basically tell our infrastructure (Microsoft SQL Server in this case) how to setup relationships and keys for the tables where these entities will be stored.

namespace Seeding.Sample.Data.Configurations
{
    public class LookupTypeConfiguration : IEntityTypeConfiguration<LookupType>
    {
        public void Configure(EntityTypeBuilder<LookupType> builder)
        {
            builder.ToTable("LookupTypes");

            builder.Property(e => e.Id)
               .ValueGeneratedOnAdd()
               .UseIdentityColumn(1, 1);

            builder.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(250);

            builder.HasMany(t => t.LookupValues)
                .WithOne(v => v.LookupType)
                .HasForeignKey(v => v.LookupTypeId);
        }
    }
}
    

namespace Seeding.Sample.Data.Configurations
{
    public class LookupValueConfiguration : IEntityTypeConfiguration<LookupValue>
    {
        public void Configure(EntityTypeBuilder<LookupValue> builder)
        {
            builder.ToTable("LookupValues");

            builder.Property(e => e.Id)
               .ValueGeneratedOnAdd()
               .UseIdentityColumn(1, 1);

            builder.Property(e => e.Value)
                .IsRequired()
                .HasMaxLength(250);
        }
    }
}
    

We have our entities and translation classes that will tell the underlying infrastructure how to setup the tables for our entities. This is enough for constructing our dbContext. 

namespace Seeding.Sample.Data
{
    public class SampleDatabaseContext : DbContext
    {
        public SampleDatabaseContext() : base() { }

        public SampleDatabaseContext(DbContextOptions<SampleDatabaseContext> options) : base(options) { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new LookupTypeConfiguration());
            modelBuilder.ApplyConfiguration(new LookupValueConfiguration());
        }

        public virtual DbSet<LookupType> LookupTypes { get; set; }
        public virtual DbSet<LookupValue> LookupValues { get; set; }

        internal static SampleDatabaseContext CreateContext()
        {
            return new SampleDatabaseContext(new DbContextOptionsBuilder<SampleDatabaseContext>().UseSqlServer(
                 new ConfigurationBuilder()
                     .AddJsonFile(Path.Combine(Directory.GetCurrentDirectory(), $"appsettings.json"))
                     .AddEnvironmentVariables()
                     .Build()
                     .GetConnectionString("SampleDatabase")
                 ).Options);
        }
    }
}
    

You are probably wondering what's the deal with static internal CreateContext method? Well, remember that we want to execute these migrations without our main application. This means that we need instance of our dbContext in the migration in order to seed the data

Since we do not have scope of dependency injection set in the application project (ASP.NET Core Web API in this case) we need to construct the dbConext using the connection string we have in the library project appconfig.js mentioned above.

This method will only be used when running migrations from the CLI so we do not need id to be tied to an instance nor we need to expose it outside of this library and therefore internal access is all it is required.

As a final step before we create our initial migration is to implement IDesignTimeDbContextFactory interface where we'll execute migrations.

namespace Seeding.Sample.Data
{
    public class SampleDatabaseContextFactory : IDesignTimeDbContextFactory<SampleDatabaseContext>
    {
        public SampleDatabaseContext CreateDbContext(string[] args)
        {
            var dbContext = SampleDatabaseContext.CreateContext();
            dbContext.Database.Migrate();
            return dbContext;
        }
    }
}
    

Finally we are ready to create our initial migration that will create the necessary tables and wire them up with keys. EF Core 3.1 allows you to run migrations from the CLI independently from the rest of your application and since it's CLI is platform independent like .NET Core, the same following commands apply to any operating system.

Make sure you navigate in you favorite command line tool (cmd, ps, bash, git bash (MINGW) or even bash inside WSL ) to folder where your Seeding.Sample.Data project sits and run the following command

dotnet ef migrations add Initial_Migration -o Migrations -c SampleDatabaseContext
    

The EF Core CLI will first build the project and try to generate the migrations to folder Migrations as it is instructed in the command.

Finally, just update the database to apply the migrations just created

dotnet ef database update -c SampleDatabaseContext
    

Now when we have our database table structure in place we can proceed with creating migration to seed the data to these tables

Creating seeding migrations

The concept behind using migrations to seed the data is pretty simple and relies on EF Core mechanism for executing migration only once by tracking executed migration in the migrations table. This ensures that your data will be seeded only once.

With this approach you can easily rollback your seeding like any other migration and since it is part of the migrations, you can actually execute seeding from the CLI instead of having to run the whole application in order to have data seeded.

Let's see how do we use migrations to setup some basic seeding in the project. The process starts as with any new change needed to be applied to the database and that is create a migration from the EF Core CLI using the following command

dotnet ef migrations add Initial_Lookup_Seeding -o Migrations -c SampleDatabaseContext
    

If you check your migrations folder you will see one new migration class with an empty Up and Down methods

namespace Seeding.Sample.Data.Migrations
{
    public partial class Initial_Lookup_Seeding : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {

        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {

        }
    }
}
    

Before we apply this migration to the database we need to write the logic to generate the data and to persist it to the database.

This is one more time where dbContext internal static method CreateContext comes into play. Since we do not have the dbConext injected because we are executing all these things from the CLI apart from the main application where DI is set, we need to create the instance of dbContext ourself.

namespace Seeding.Sample.Data.Migrations
{
    public partial class Initial_Lookup_Seeding : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            using (var databaseContext = SampleDatabaseContext.CreateContext())
            {
                databaseContext.LookupTypes.AddRange(
                    new List<LookupType>() {
                        new LookupType()
                            {
                                Name = "ProductTypes",
                                LookupValues = new List<LookupValue>()
                                {
                                    new LookupValue(){ Value="Laptop" },
                                    new LookupValue(){ Value="Monitor" },
                                    new LookupValue(){ Value="Mouse" },
                                    new LookupValue(){ Value="Keyboard" }
                                }
                            }
                    });

                databaseContext.SaveChanges();
            }
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            using (var databaseContext = SampleDatabaseContext.CreateContext())
            {
                databaseContext.LookupTypes.Remove(databaseContext.LookupTypes.SingleOrDefault(t => t.Name == "ProductTypes"));
                databaseContext.SaveChanges();
            }
        }
    }
}
    

Now in order to seed this data, we just need to update the database from the CLI

dotnet ef database update -c SampleDatabaseContext
    

Once the command execute successfully, all the values will be inserted into both LookypTypes and LookupValues tables. 

The nice part is that you can easily revert this migration and remove inserted values from the CLI but just updating the database with the previous migration

dotnet ef database update -c SampleDatabaseContext Initial_Migration
    

This whole sample project is awailable on GitHub https://github.com/dejanstojanovic/ef-core-migration-seeding

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.

About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN