Documentation Home
Connectors and APIs Manual
Download this Manual

4.7.2.2 Scaffolding an Existing Database in EF Core

Scaffolding a database produces an Entity Framework model from an existing database. The resulting entities are created and mapped to the tables in the specified database. For an overview of the requirements to use EF Core with MySQL, see Table 4.4, “Connector/NET Versions and Entity Framework Core Support”).

NuGet packages have the ability to select the best target for a project, which means that NuGet installs the libraries related to that specific framework version.

There are two different ways to scaffold an existing database:

This section shows how to scaffold the sakila database using both approaches. Additional scaffolding techniques are:

Requirements

For the components needed to reproduce each scaffolding approach, see General Requirements for EF Core Support. With the Package Manager Console approach, determine which version of Visual Studio is recommended for the version of .NET or .NET Core in use (see Table 4.1, “Connector/NET Requirements for Related Products”).

To download sakila database, see https://dev.mysql.com/doc/sakila/en/.

Note

When upgrading ASP.NET Core applications to a newer framework, be sure to use the appropriate EF Core version (see https://docs.microsoft.com/en-us/aspnet/core/migration/30-to-31?view=aspnetcore-3.1).

Scaffolding a Database Using .NET Core CLI
  1. Initialize a valid .NET Core project and console application using the .NET Core command-line interface (CLI) and then change to the newly created folder (sakilaConsole).

    dotnet new console –o sakilaConsole
    cd sakilaConsole
  2. Add the MySQL NuGet package for EF Core using the CLI. For example, use one of the following commands to add either the MySQL EF Core 5.0 or EF Core 3.1 package for use with Connector/NET 8.0.23 and higher:

    dotnet add package MySql.EntityFrameworkCore --version 5.0.0-m8.0.23
    dotnet add package MySql.EntityFrameworkCore --version 3.1.10-m8.0.23

    For previous versions Connector/NET, use the following command to specify the version of the package:

    dotnet add package MySql.Data.EntityFrameworkCore --version 8.0.22
  3. Add the following Microsoft.EntityFrameworkCore.Design Nuget package:

    dotnet add package Microsoft.EntityFrameworkCore.Tools
  4. Restore dependencies and project-specific tools that are specified in the project file as follows:

    dotnet restore
  5. Create the Entity Framework Core model by executing the following command. The connection string for this example must include database=sakila. For information about using connection strings, see Section 4.4.1, “Creating a Connector/NET Connection String”.

    Note

    If you are using a connector version earlier than Connector/NET 8.0.23, replace MySql.EntityFrameworkCore with MySql.Data.EntityFrameworkCore.

    dotnet ef dbcontext scaffold "connection-string" MySql.EntityFrameworkCore -o sakila -f

    To validate that the model has been created, open the new sakila folder. You should see files corresponding to all tables mapped to entities. In addition, look for the sakilaContext.cs file, which contains the DbContext for this database.

Scaffolding a Database Using Package Manager Console in Visual Studio
  1. Open Visual Studio and create a new Console App (.NET Core) for C#.

  2. Add the MySQL NuGet package for EF Core using the Package Manager Console. For example, use one of the following commands to add either the MySQL EF Core 5.0 or EF Core 3.1 package for use with Connector/NET 8.0.23 and higher:

    Install-Package MySql.EntityFrameworkCore -Version 5.0.0-m8.0.23
    Install-Package MySql.EntityFrameworkCore -Version 3.1.10-m8.0.23

    For previous versions Connector/NET, use the following command to specify the version of the package:

    Install-Package MySql.Data.EntityFrameworkCore -Version 8.0.22
  3. Install the following NuGet package by selecting either Package Manager Console (or Manage NuGet Packages for Solution and then NuGet Package Manager) from the Tools menu: Microsoft.EntityFrameworkCore.Tools.

  4. Open Package Manager Console and enter the following command at the prompt to create the entities and DbContext for the sakila database. The connection string for this example must include database=sakila. For information about using connection strings, see Section 4.4.1, “Creating a Connector/NET Connection String”.

    Note

    If you are using a connector version earlier than Connector/NET 8.0.23, replace MySql.EntityFrameworkCore with MySql.Data.EntityFrameworkCore.

    Scaffold-DbContext "connection-string" MySql.EntityFrameworkCore -OutputDir sakila -f

    Visual Studio creates a new sakila folder inside the project, which contains all the tables mapped to entities and the sakilaContext.cs file.

Scaffolding a Database by Filtering Tables

It is possible to specify the exact tables in a schema to use when scaffolding database and to omit the rest. The command-line examples that follow show the parameters needed for filtering tables. The connection string for this example must include database=sakila.

If you are using a connector version earlier than Connector/NET 8.0.23, replace MySql.EntityFrameworkCore with MySql.Data.EntityFrameworkCore.

.NET Core CLI:

dotnet ef dbcontext scaffold "connection-string" MySql.EntityFrameworkCore -o sakila -t actor -t film -t film_actor -t language -f

Package Manager Console in Visual Studio:

Scaffold-DbContext "connection-string" MySql.EntityFrameworkCore -OutputDir Sakila -Tables actor,film,language -f
Scaffolding with Multiple Schemas

When scaffolding a database, you can use more than one schema or database. Note that the account used to connect to the MySQL server must have access to each schema to be included within the context.

The following command-line examples show how to incorporate the sakila and world schemas within a single context. If you are using a connector version earlier than Connector/NET 8.0.23, replace MySql.EntityFrameworkCore with MySql.Data.EntityFrameworkCore.

.NET Core CLI:

dotnet ef dbcontext scaffold "connection-string" MySql.EntityFrameworkCore -o sakila --schema sakila --schema world -f

Package Manager Console in Visual Studio:

Scaffold-DbContext "connection-string" MySql.EntityFrameworkCore -OutputDir Sakila -Schemas sakila,world -f