Generating a model from an existing database

In previous versions of Entity Framework, you had the option to take a Database-First approach to modelling from an existing database, resulting in the generation of an EDMX file. Support for the EDMX file was dropped in Entity Framework Core in favour of using commands to reverse-engineering files for the model from an existing database schema. This approach is known as Code First to an existing database.

Command Line Interface link

The following example illustrates how to do use code first to to generate a model from a SQL Server database in a new console application using the CLI tools.

First, create a folder for the project:

> mkdir EFCoreScaffoldexample

Then navigate to it:

> cd EFCoreScaffoldExample

Then create a new project:

> dotnet new console

Add the Entity Framework Core and Tools packages to the project:

> dotnet add package Microsoft.EntityFrameworkCore.SqlServer> dotnet add package Microsoft.EntityFrameworkCore.Design

The first package is the EF Core provider for SQL Server. The second package contains the Entity Framework Core commands. Both of these packages are required for any Entity Framework Core application that targets SQL Server.

Test to see if ef commands are available to you:

dotnet ef -h

This should result in the initial help for the EF tools being displayed:

EF commands Help

You use the DbContext Scaffold command to generate the model. The command has two required arguments – a connection string and a provider. The connection string will depend on your environment and database provider. The provider argument is the Entity Framework provider for your chosen database. This example uses the AdventureWorks sample database for SQL server provided by Microsoft.

> dotnet ef dbcontext scaffold "Server=.\;Database=AdventureWorksLT2012;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model

Once you have executed the command, you will see that a folder named Model has been created in the project folder, containing a collection of class files representing the entities in addition to a file for the DbContext class:

Scaffolded Model in Visual Studio Code

The -o option (or alternatively --output-dir) specifies the directory where the class files will be generated. If it is omitted, the class files will be generated in the project directory (where the .csproj file is located).

The DbContext class will take the name of the database plus “Context”, You can override this using the -c or --context option e.g.

> dotnet ef dbcontext scaffold "Server=.\;Database=AdventureWorksLT2012;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model -c "AdventureContext"

Model Configuration link

The resulting model is configured using the Fluent API by default, which is the recommended approach. The configurations are placed in the generated context’s OnModelCreating method. However, if you prefer to use Data Annotations for configuration, you can use the -d or --data-annotations switch:

dotnet ef dbcontext scaffold "Server=.\;Database=AdventureWorksLT2012;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -d 

Since Data Annotations only cover a subset of configuration options, you are likely to find that the Fluent API has also been used to configure the model.

Updating the Model link

The recommended approach to keeping changes to the database in sync with the generated model is to use migrations. However, if you want to re-scaffold the model after making schema changes, you can do so by specifying the -force option e.g.:

dotnet ef dbcontext scaffold "Server=.\;Database=AdventureWorksLT2012;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -force

All of the class files will be overwritten, which means that any amendments that you might have made to them e.g. adding attributes or additional members, will be lost.

Visual Studio link

If you are working with Visual Studio, you can use the Package Manager Console commands to generate the the code files for the model. The equivalent command to the last CLI command just above is:

PM> Scaffold-DbContext "Server=.\;Database=AdventureWorksLT2012;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Model -Context "AdventureContext" -DataAnnotations

Migrations link

Once you have your model, the recommendation is that you use Migrations to keep the database synchronised with any changes to the model. In previous versions of Entity Framework, it was possible, once the model has been created, to add a migration that did not affect the schema of the existing database using the -ignorechangesoption. This option does not exist in Entity Framework Core, so the workaround is to create a first migration and then to delete or comment out the contents of the Up method prior to applying the migration to the database. This will result in a model and a database schema that match.

Leave a Comment