Documentation Home
MySQL Connector/NET Developer Guide
Related Documentation Download this Manual

MySQL Connector/NET Developer Guide  /  ...  /  Scaffolding an Existing Database in EF Core

8.2.2 Scaffolding an Existing Database in EF Core

Scaffolding a database is when an Entity Framework model is created from an existing database. The resulting entities are created and mapped to the tables in the specified database. This feature was introduced in MySQL Connector/NET 6.10.2-beta and 8.0.8-dmr.

Note

The Design package for scaffolding a database is part of the main package in EF Core 2.x; however, the MySql.Data.EntityFrameworkCore.Design package remains available for EF Core 1.x compatible projects.

NuGet packages have the ability to select the best target for a project, which means that NuGet will install 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.

Prerequisites

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 switch to the newly created folder (sakilaConsole).

    dotnet new console –o sakilaConsole
    cd sakilaConsole
  2. For EF Core 1.x only: Add a reference to the Microsoft.EntityFrameworkCore.Design Nuget package:

    dotnet add package Microsoft.EntityFrameworkCore.Design
  3. For EF Core 1.x only: Add the reference to Connector/NET Entity Framework Core Design Nuget package:

    dotnet add package MySql.Data.EntityFrameworkCore.Design -v 6.10.2-beta
    Note

    The version (for example, -v 6.10.2-beta) must match the actual Connector/NET version you are using. To obtain the most recent package, visit https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/.

  4. Add a reference to Microsoft.EntityFrameworkCore.Tools.DotNet as a DotNetCliToolReference entry in the sakilaConsole.csproj file as follows:

    EF Core 1.x

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

    EF Core 2.x

    <ItemGroup>
       <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
    </ItemGroup>
  5. Restore dependencies and project-specific tools that are specified in the project file as follows:

    dotnet restore
  6. Create the Entity Framework Core model by executing the following command (adjust the connection-string values to match your settings for the user= and password= options):

    dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.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. Install the following NuGet packages by selecting either Package Manager Console or Manage NuGet Packages for Solution from the Tools and then NuGet Package Manager menu:

    • MySql.Data.EntityFrameworkCore.Design

    • Microsoft.EntityFrameworkCore.Tools version 1.1.4 (for EF Core 1.x) and Microsoft.EntityFrameworkCore.Tools version 2.0.1 (for EF Core 2.0)

  3. Open Package Manager Console and enter the following command at the prompt to create the entities and DbContext for the sakila database (adjust the connection-string values to match your settings for the user= and password= options):

    Scaffold-DbContext "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.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.

.NET Core CLI:

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -o sakila -t actor -t film -t film_actor -t language -f

Package Manager Console in Visual Studio:

Scaffold-DbContext "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -OutputDir Sakila -Tables actor,film,film_actor,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. Multiple-schema functionality was introduced in Connector/NET 6.10.3-rc and 8.0.9-dmr releases.

The following command-line examples show how to incorporate the sakila and world schemas within a single context.

.NET Core CLI:

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -o sakila --schema sakila --schema world -f

Package Manager Console in Visual Studio:

Scaffold-DbContext "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -OutputDir Sakila -Schemas sakila,world -f

User Comments
  Posted by gavin Luo on July 11, 2018
i have test in
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.1.1" />
<PackageReference Include="MySql.Data.EntityFrameworkCore" Version="8.0.11" />
<PackageReference Include="MySql.Data.EntityFrameworkCore.Design" Version="8.0.11" />
, is not work. get this error :
Scaffold-DbContext "server=192.168.100.201;port=3306;user=quantum;password=admin;database=scm_oms" MySql.Data.EntityFrameworkCore -OutputDir Models -f

System.NotImplementedException: The method or operation is not implemented.
at MySql.Data.EntityFrameworkCore.Scaffolding.Internal.MySQLDatabaseModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas)
at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String namespace, String language, String contextDir, String contextName, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Sign Up Login You must be logged in to post a comment.