Using Entity Framework Core with an existing database

I know there are a bunch of how to articles out there on how to set up Entity Framework Core (EF), but none of them took me all the way through the process of setting up the command line tools, with an existing database and with automatic migrations.

In this post I’ll demonstrate how to:

  1. Set up Entity Framework Core by installing the necessary NuGet packages
  2. Create the initial state of the database as a set of C# “scripts”
  3. Add the first migration script
  4. Write a little code to so that EF automatically upgrades the database when the application starts up
  5. Add new changes and have the command line tool generate the migration (schema changes) which are checked into source control

One other important thing I’m going to do is set up EF in a project (Assembly) which is different from the main application. Most tutorials will demonstrate EF living within the UI layer of your application and most of the time this isn’t what you want.

Step 1 – Install Packages

I’ve created a ASPNET Core Web app with a business layer, which is where EF will live.

Open up a command prompt or PowerShell session. Then, cd to the project directory where you want EF to be available from, i.e. (C:\dev\....\EfCoreDemo.Business). Then install the following packages.

dotnet add package Microsoft.EntityFrameworkCore

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet

dotnet add package Microsoft.EntityFrameworkCore.Design

Package descriptions:

An edit to the csproj file is required so that the dotnet ef command is available at the CLI. Add the following.

    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />

Lastly, at the CLI enter dotnet build. Once complete enter dotnet ef to see if EF has been correctly set up. If it isn’t available you’ll need to restart your command prompt or PowerShell session.

Step 2 – Scaffold your existing database

You’ll want to scaffold out your existing database into entities and a set of repositories so that you can perform operations against your database. To do this enter the following command.

dotnet ef dbcontext scaffold "Server=.;Database=nopCommerce;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Data

What this command will do is create a NopCommerceDbContext class and a set of models based on the existing tables, in a directory called Data (as seen in the command above).

In order to have the DbContext and Repositories available throughout your solution it’s a good idea to register it with the .NET service locator with some code similar to the following:

services.AddDbContext<NopCommerceDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Step 3 – The first migration script

The first migration script is one that’s actually ignored and represents a snapshot of the database. After all, you don’t want to create a database that already exists, you just want to track changes from this point onward.

To create the initial migration, enter the following command.

dotnet ef migrations add InitialCreate

Then in a text editor, open up the newly create migration file. It should be in .\Migrations, then delete all of the code in the Up() method and save the file. The reason for doing this is that we don’t want to try and create any objects which already exist. This is the snapshot file and our first ‘dummy’ migration if you will.

Then make the change permanent and run the command

dotnet ef database update

This will run your initial empty migration script and create a table in the database called _MigrationHistory.

4. Automatic migration

Having a database update automatically based on your version control system is a nice process. Not everyone feels the same way and it might not be a good idea in a production scenario. The project I’m working on right now will be using the following approach in production, but others I’ve worked on in the past executed database migrations as a separate build process. It depends right?

In your Startup.cs file, add the following method. You need not worry if the migration scripts have already been run on subsequent application startup as this process checks the _MigrationHistory table.

private void InitializeDatabase(IApplicationBuilder app)
  using (var scope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
    var db = scope.ServiceProvider.GetRequiredService<NopCommerceDbContext>();

5. Testing out a new migration

Open up one of the auto generated models located in  C:\dev\....\EfCoreDemo.Business\Data and add a new property. I’ve randomly chosen Customer in my project.

public class Customer
  // ..
  property string MiddleName { get; set; }
  // ..

Then at the CLI, enter

dotnet ef migrations add NewMiddleNameProperty

This command inspects your classes through reflection and generates a migration “script” for you and a Designer file. To update the database, enter

dotnet ef database update

Within a few seconds, your database is updated and you’ll have two new files to check-in to source control.

Final Words

I hope this article saves someone some time as I feel it has all the information required to get going quickly. It’s the information I wish I had when I was tasked with setting it up.

I encourage you explore the EF CLI commands and options by looking at the help offered by the tools themselves. Try the following commands:

dotnet ef --help

dotnet ef database --help

dotnet ef migrations --help