Scaffolding DbContext (Reverse Engineering)

With scaffolding, we can import a database schema into code as entity type classes. This process is called reverse engineering.

In a migration, we create entity type classes first, then configure them in the DbContext class. After that, we run the migration command to create a migration file. Finally, with the Update-Database command, we can migrate our code into the database.

Command of Entity framework using package manager console :

Scaffold-DbContext

Dot net Cli (vscode)

dotnet ef dbcontext scaffold

The above command can be used to Scaffold database schema into entity type classes.

Let's test the above process. First, we need to create a schema in our database. Then, we need to establish a connection to the database using a connection string in our code.

Let's create an Order Table in our database.

Connect database:

We are using SSMS so we have two ways to write schema either using sql command or interface of SSMS

Create Table using SSMS interface

Add AutoIncrement To PK Id

Add Relationship (FK)

Save the table and refresh the table list to see newly added order table.

Same we can create using SQL CMD

So We are Created a Order table now we need to scaffold this table in code using the Scaffold cmd

Prerequisites:

Visual Studio with Package manager console OR .net cli.
Install this package from nuget or using cli Microsoft.EntityFrameworkCore.Design
Install Database provide package We are using SQL SERVER so we will install Microsoft.EntityFrameworkCore.SqlServer
Database connection string

To scaffold entity type from db we have to run our command with connection string, our cmd will be:

For PCM

Scaffold-DbContext 'Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook' Microsoft.EntityFrameworkCore.SqlServer

For .net CLI

dotnet ef dbcontext scaffold "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook" Microsoft.EntityFrameworkCore.SqlServer

We are going to do this in visual studio PCM

Install packages

Scaffold-DbContext 'Data Source=MSI;Initial Catalog=ESTORE' Microsoft.EntityFrameworkCore.SqlServer

ERROR while executing above cmd

FIX:

Scaffold-DbContext "Server=MSI;Database=ESTORE;Trusted_Connection=True;MultipleActiveResultSets=true;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

So we have to update our command to fix the issue. Above is the fixed cmd

Output

Created Dbcontext

You can see the code for the order table in the above image.

In our project we already using migration (code first approach) so this is just demonstration to scaffold database (reverse engineering)

Now we can copy Order Table code and paste it our Dbcontext file we will delete other code from here

Our DbContext file usages the identity code so we will keep that code .

Code generated by Scaffold Has issue, It has HasNoKey but in our db we have PrimaryKey and Auto generated identity to Id, let's fix this issue by adding key and Auto generated Id.

After copying and pasting necessary files we can delete other files.

Code first or db first approach which one is good

If you find creating entities and defining relationship between them is time consuming or complex to you and you have good understanding writing schema using sql cmd or ui interface then you can use DB first approach , can be used when you have new project

If you are able find easy to write first entities and then creating table using migration the you can use db first approach, can we used when you have existing database.

Useful code syntax to create entities and configure them in dbContext file

To create index