EF Core Database First. Reverse Engineering the Database (scaffolding)

In this article, we will learn how to use Entity Framework Core with the existing database or Database First approach. The EF core only supports Code First & Database First approach. In Database First, We use the Scaffold-dbcontext to create the Model from an existing database. This is basically Reverse engineering the existing database. Once we create the entity classes databases first does not work. You will continue to work in the code first approach.

Source Code:
The source code of this project available in GitHub.

Create the .NET Console App

You can refer to the tutorial on how to create a simple Entity framework core console application. Also, learn how to use ef core migrations to create & update the database.

You will work with the new database only if you are working with a new project. In most of the scenarios, you may have to start with an existing database. You can use the Scaffold-dbcontext quickly create the models from the existing database

Let us create the .NET Console App. You can also create the ASP.NET Core Web application

• Open Visual Studio 2017.
• Click on File -> New -> Project to open the New Project form
• Select Visual C# -> .NET Core -> Console App (.NET Core)
• Name the App as EFDBFirst
• Click OK to create the Application

Adding Required Libraries

Preparing for the Reverse engineering

There are two ways by which you can create the models from the existing database

• From the Package Manager Console of Visual Studio
• Using the Command line tool dotnet.exe

Using Package Manager Console

To Use the Package Manager Console, you need to install the Entity Framework Core Tools. Open the Package Manager and run the following command.

We use these tools inside the Visual Studio NuGet Package manager console.

Installing the above package also installs the Microsoft.EntityFrameworkCore.Design package. This package actually contains the command to scaffold an existing database by reverse-engineering the schema of a database.

In ASP.NET Core 2.1 and above, these packages are automatically included by the Visual Studio 2017, when creating the project.

Using Command Line Tools

The Command line tools run from the command line does not require the Visual Studio. If you are not using windows OS, then using the command line tools is the only option available to you.

NET Core SDK version 2.1.300 and newer

If you are using the .NET Core SDK version 2.1.300 or newer you do not have to do anything. The tools have become part of the SDK. You can download the SDK from the link 

Previous Versions

To Tools to work correctly you need to install the Microsoft.EntityFrameworkCore.Design package. Goto to the solution directory and run the following command to install

Existing Database

You can use any database you like. We will use the EFCoreMigration database, which we created in the previous tutorial.

You can also download the adventureworks database from and use it.

scaffold-dbcontext

The Scaffold-DbContext is the command is used to generate the model from the database. We need to pass the connection string & database provider to this command.

Parameters of the Scaffold-DbContext

Here the -Connection & -Provider are required.

Reverse engineering the model

Run the following command to generate the model. We are using -OutputDir to specify the directory for the model classes.

Or if you prefer DOT Net CLI use the following command

• The Context and models are created in the default project selected in the PMC.
• The Microsoft.EntityFrameworkCore.Tools must be installed in the startup project.
• The solution must compile without any errors

As you can see the scaffold-dbcontext

Models

• The models are created in the root folder. You can override it by specifying the -OutputDir as shown in our example
• Creates one model class per table.
• No data annotations are applied to the models unless you specify the -DataAnnotations argument.
• You can limit the tables to use using the -Tables argument and specifying the name of the tables separated by the comma.
• Similarly, you can restrict it to a certain schema using the -Schema

Context

• The context class EFCoreMigrationContext is created deriving from the DBContext. The name of the context is <DatabaseName>Context. You can change the name of the context class from the -Context argument
• The Context class is created in the models folder. You can override it by specifying the -ContextDir
• The context class uses the namespace of the default project.
• The OnConfiguring method is created with connection hardcoded in it.
• Fluent API used in the OnModelCreating() method of the DbContext class

What if the database changes

The EF Core does not support updating the Model if the database changes. You have to delete the model and recreate it again

What if Model changes

You can run add-migration to create the migrations, but you won’t be able to run update-database as the tables already exist.

To enable our new model to work under migrations you need to follow these steps

1. Create Models by reverse-engineering the existing database as mentioned above
2. Run add-migration to create the first migration
3. Use the script-migration to create the script
4. In the generated script look for the CREATE TABLE [__EFMigrationsHistory] query which is at the top of the script and run it against your DB to create the table in the database
5. Next, find out the INSERT INTO [__EFMigrationsHistory] which are at the bottom of the script and run it against your DB

That’s it.

Now, you can update the model and create migrations as mentioned in the tutorial Migrations in EF Core

Reference

Summary

In this tutorial, we learned how to work with the existing database in EF Core, We use the scaffold-dbcontext to create the model classes from the existing database. Once we create the model classes, we can then use the normal ef core migration techniques to keep the database in sync with the model.