.net core tutorials

How to seed your EF Core database

A typical requirement when developing new applications is to have the ability to populate your database with an initial set of data. This may be to enable a common set of lookup data, or even to enable populating it with an inital set of data.

We may even want to populate your database with some Mock testing data, in order not too expose actual customer data to your developers or even to leak potentially sensitive data.

Using Entity Framework Core as an Object Relation Mapper (ORM) for your ASP.net Core Web or API projects, is really easy and you can be up and running with it really quickly. In this post I will discuss how to seed your EF Core Model first database with seed data by using migrations.

Code available on Github

I have previously discussed how to use EF Core in a seprate class library project and how to configure migrations to enable the update of your database schema.

Often you will also need to Seed your database with an initial set of data.

Database seeding is the initial seeding of a database with data. Seeding a database is a process in which an initial set of data is provided to a database when it is being installed . It’s a process which is typically only run once on the initial deployment of a database.

I generally always follow principles from Code Complete: A Practical Handbook of Software Construction when designing my data tables and schema’s – Chapter 18 is definitely worth a read – the most common mistake I have found when working on legacy applications, is that the developers have often attempted to design their databases for all possible use cases, i.e. Application, Reporting, Data Warehouse, transactional.

This often results in bloated databases and applications which eventually become increasingly difficult to manage, maintain and develop against. This is one of the driving reasons why micro-service architectures have become popular, whereby the application and database are just enough to solve the problem.

Featured Book Review


Code Complete: A Practical Handbook of Software Construction

Code Complete: A Practical Handbook of Software Construction

Steve McConnell and Microsoft Press,U.S.

Price: £26.35

You’ll notice that I have tried to keep the database behind this service really small, and only implemented the Minimum Viable Product (MVP) requirements. We only need the ability to Add, Edit and View a list of Web Crawlers and their referer URL’s.

Can’t wait to see it in action ?

Check out the code on GitHub

Code

Seeding with OnModelCreated

It is possible to initially seed your database directly after it has been initially been created. This can easily be accomplished by utilising the HasData method.

The HasData is defined with the EntityTypeBuilder which is returned from the Entity method of the ModelBuilder. It has been defined with a params keyword which enables any number of entity types to be passed in.

Lets look at an example, where we could use the HasData method. One example could be where we are defining a really simple Entity. A point to note is that it has Fluent API, which you use to apply in the OnModelCreating method.

Our Skill Entity is a simple Lookup type entity, which will be foreign key to a CandidateSkill table

We can now create our Candidate context and create the table

You’ll notice using this method we explicitly set the key property, Id, with a value.

With this in place we can generate a Migration, in this instance it could be the Initial Migration dotnet ef migrations add initial

Inspecting the migration file, that is generated for this, you see it generates the following logic

HasData is specific to a single entity. You can’t combine inserts to multiple tables with HasData.

If you use the dotnet ef migrations script command to check the generated SQL script EF core will use insert the data, you’ll notice that depending on the Provider EF core will generate the appropriate SQL.

In this case because we generated the script to user MS SQL, and we elected to hard code id EF core will wrap our Insert statements with IDENTITY_INSERT

Suggested Reading


ASP.NET Core in Action_p1

ASP.NET Core in Action_p1

Andrew Lock

Price: £32.22

From this point on is the legacy implementation.

The Database

The database we are going to seed in this example is a relatively simple database with just enough complexity in it to illustrate how you can cope with most database seeding tasks.

We have 3 tables Threat, ThreatType and Status. Each table has a Primary Key defined and the Threat table has foreign key relationships with both the ThreatType and Status tables.

We are going to seed all 3 of these tables with data, including the relationships.

Context

Although we have created a separate class library project for our EF Core database context class, ApiContext, we are going to develop some extension methods to this class but we are going to add this extension class to our main Api project.

The primary reason being that our Api.Database project is generic mini database project that is going to used for several disconnected micro service applications internally. Each of these micro service applications may have different database seeding requirements and therefore we want to be able to control the seeding on a per project basis.

Add Extension class

Add a new class to the root of the Api project and name it DbContextExtension.cs and add the following code.

View Full Code

We have added a simple boolean method we will call to determine whether all migrations have been applied.



Add Folder for data seeding scripts

Add a folder that will contain your data seeding scripts. This fodler will contain some JSON files which will contain the data required by the applicaiton.

I have just added a folder and called it seed. I have added three JSON files, threats.json , status.json & types.json

Add Seeding Extension Methods

We need to import a few additional namespaces to the DbContextExtension.cs

Add and addtional extension method to the DbContextExtension.csclass, that will be responsible for reading the data from the JSON files and the inserting to the database.

View Full Code

Modify StartUp.cs

We now need to add some code to the StartUp.cs to the Configure method

Summary

The above techniques illustrate how it easy it is to make use of Entity Framework core to create a database and seed it with the default data that it requires.

We used standard JSON data as a source for our data. We also made use of EF Core functions to insert the data.



Gary Woodfine

Freelance Full Stack Developer at threenine.co.uk
Helps businesses by improving their technical proficiencies and eliminating waste from the software development pipelines.

A unique background as business owner, marketing, software development and business development ensures that he can offer the optimum business consultancy services across a wide spectrum of business challenges.

Latest posts by Gary Woodfine (see all)

Affiliate Disclaimer

Disclosure: Please note that some of the links included in the blog posts are affiliate links, which means I will earn a commission if you decide to make a purchase.

I only include affiliate links to products, services and companies that I have personal experience and have actually used. I only recommend them because they are helpful and useful, not because of the small commissions I make if you decide to buy something.

Please do not spend any money on these products unless you feel they are relevant, suitable and will help you achieve your goals.

Buying anyone of these products and the commisions earned will not make me an overnite multi millionaire, but they will help to cover the hosting costs and compensate for the countless hours and effort I put in to create meaningful and valuable free content I provide to help others succeed.

You've also undoubtedly noticed that I also use various advertising networks - unless of of course you're using an Ad blocker, this is also an attempt to reduce my monthly deficit I experience in providing free relevant, valuable and on occassion insightful content for the benefit of others.

I only really make some money if people click on the ads, unless of course you consider 1c per 1000 impressions real money!

Bear in mind just how many impressions I need to make before I can cover my £20 hosting costs a month!

If you are using an adblocker and guilt stricken you can of course donate using any of the buttons below, but I won't be holding my breath.


Buy me a coffeeBuy me a coffee
π