.Net Programming - Financial independence

How to Scaffold a MySQL Database with Entity Framework Core

Sadly, there are a few limitations to using MSSQL as your database engine for your projects. One of them is the fact that your database cannot get bigger than 10 GB. On the bright side, the Entity Framework Core got us covered with MySQL Community alternative! This gives you the ability to connect your project to a MySQL database while still use the Entity Framework.

In this post I’ll show you how it’s done using the Entity Framework Core Scaffolder in Visual Studio.

What you will need:

  • Visual Studio (most recent version, right now it’s 2019) with .Net Core 2.1 or more recent;
  • MySQL server installed;
  • MySQL Workbench or any other UI to help you create tables and the like. If you feel like a pro, you can also use the command line with MySQL Shell.

1. Install the Tools!

Let’s get our environment setup and install all the necessary tools, I’ll stick with MySQL Workbench/MySQL Community combination but you can do as you like. Go to the download page and download MySQL Community full setup for Windows.

Once it’s installed, you will have to add a new connection to your MySQL Server to be able to connect from Workbench to your server. Here are some sample settings if you’ve installed MySQL to your local machine. Your root password will be asked once you connect to the server.

Add new connection to MySQL server.

2. Create a Database

We’ll simply create a test database with two tables joined with a relationship. The classic Classes -> Students example will apply, one class can have many students. Normally we would say that one student can attend many classes but this would complicate the example for no real benefit with a many to many relationship.

Database Creation

Launch MySQL Workbench and create a new Database and give it a name. Then click Apply in the bottom right corner.

Create new MySQL Database.

You might get prompted by the window below, just click Apply again to run the SQL statement.

Run database creation SQL.

The next thing you will want to do is to set the newly created schema (database) as default. That will make sure all the queries we run will apply to this database. Right-click on the schema and click on Set as detault schema.

Set as default database schema.

Create the Tables

We will now create two tables. Creating a table is very easy with MySQL Workbench! Right-click on the schema and click on Create table.

Create table in schema

Below is the Student table. You might notice that I have put the utf8mb4 charset to conform to the standard UTF8, that will enable us to put special characters in our table. Also, I want to bring your attention on the abbreviations that MySQL seems to expect you to know. Here are the most important ones.

  • PK – Primary Key
  • NN – Not null (element cannot be left null, good, I hate nulls!)
  • UQ – Must be unique. By default the primary key is always unique.
  • AI – Auto incremented column, let’s say 1, 2, 3, 4, … The database engine will automatically keep the count and increment as you add rows.

Data types are not exactly the same as in MSSQL but some are similar like VARCHAR and INT which I use in this example for storing strings and numbers respectively.

Student Table

And now here is the Class table below. For more information, the Level is the year that the student is now in, that is (1, 2, 3, …) depending on your education system.

Class table

Once you’ve clicked apply on both tables, it should give you a database just like in this image, but we are still missing the relationship.

Two tables created.

Add the Foreign Key

We will add the foreign key by going into the table that will contain it, which is Students because one Class can have MANY Students but one student can only belong to one class (again for the simplicity of the example).

Student Foreign Key

Give your foreign key a name and select the referenced table in the left panel. Then, in the right panel select the affected column and the referenced column. This will create a reference and an index. Finally, click Apply and we’re done!

3. Create a New Project in Visual Studio

Create a new .Net Core 2.1+ project in Visual Studio. Any type will work, you can also create a .Net Framework project and add the nuget packages to it.

Created .Net Core Project

4. Install Required Nuget Packages

Right-click on the target project and click on Manage Nuget Packages and head over to Browse.


Use the User Interface

You will need to install two packages :

  • MySql.Data.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Tools

Search for them in the Browse tab and click on Install. They will both install many packages required to scaffold and use the Entity Framework Core with MySQL. Simply click ok and accept on the prompts.

Or Use the Package Manager Console

You can also use the Package Manager Console with the Install-Package commands below.

Install-Package MySql.Data.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.Tools

5. Scaffolding

Open the Package Manager Console. If you can’t find it, go into View -> Other Windows -> Package Manager Console in the top menu. It should open a panel like the one below.

Package Manager Console

You have to enter the following command in the panel to scaffold your database. You can also create a folder where the created classes will reside.

Scaffold-DbContext "server=localhost;port=3306;user=root;password=yourpassword;database=test_scaffolding" MySql.Data.EntityFrameworkCore -OutputDir DataAccess\DataObjects -f

If you want to specify tables, use the following:

Scaffold-DbContext "server=localhost;port=3306;user=root;password=yourpassword;database=test_scaffolding" MySql.Data.EntityFrameworkCore -OutputDir DataAccess\DataObjects -Tables class,student -f

Also, the -f will force the recreation of the generated classes even if you’ve made changes, so be careful! Make sure that the right project is selected before running commands in the Package Manager Console or you will affect another project (red rectangle in the image below).

Scaffolding MySQL

As you can see, the first run went well and created the classes as intended, but when I tried to re-run the command on a specific table, I needed to add the -f force argument to overwrite.

Use the Created Context

Once the context has been created with the command above, you will get one class per table plus one Context that you’ll use to access the data.

ContextCreated 1

Now it’s time to head to Program.cs in our console application and call the DataContext.

using System;
using System.Linq;

using ScaffoldingWithMySQL.DataAccess.DataObjects;

namespace ScaffoldingWithMySQL
    class Program
        static void Main(string[] args)
            test_scaffoldingContext dataContext = new test_scaffoldingContext();
            //Create a new student object to add to the class
            Student newStudent = new Student()
                CardNumber = "TEST329812",
                Level = 1,
                FirstName = "Simon",
                LastName = "Test"
            //Create a new class object
            Class newClass = new Class()
                Department = "English",
                Level = 1,
                Title = "English Class",
                Year = 2019

            //Let's add a student to the class and then add the class to the classes in the database.

            //Don't forget to save changes!

The above code is an example of how you can use your newly create DataContext to access your data. If you want more information about the process of Scaffolding with MySQL, you can go on the MySQL Website. I hope this post will be useful for a few of you!

Having Some Errors or Issues?

I found recently that using different versions of NuGet packages can lead to strange behaviours from the MySQL scaffold engine. What I would recommend is that you have a look at this link from the MySQL Website which gives you the version compatibility. Make sure the Microsoft.EntityFrameworkCore and MySQL.Data.EntityFrameworkCore versions are correct according to the previous documentation.

EF Core Scaffolding

Found an error in the code? Please send me a message so I can fix it as soon as possible!

Next article Invest With Exchange Traded Funds on Low Saving Rate
Previous article Access Data Using the Entity Framework

Related posts


  1. Hi,
    I am getting an error while doing scaffold –
    PM> Scaffold-DbContext “server=localhost;port=3306;user=root;password=123;database=apitest” MySql.Data.EntityFrameworkCore -OutputDir Contexts\Models -f
    Build started…
    Build succeeded.
    Could not scaffold the foreign key ’employeeid’. A key for ’employeeid’ was not found in the principal entity type ’employeemember’.

    could you pls assist me with what is wrong I am doing?


    • Just by curiosity, have you added employeeid as a primary key in your employeemember table? It looks like the employeeid is a foreign key but does not have a primary key in the employeemember table.

  2. Package reference –
    Microsoft.EntityFrameworkCore.Tools -version 3.1.7
    Microsoft.EntityFrameworkCore.Design -version 3.1.7
    MySql.Data.EntityFrameworkCore -version 8.0.20
    MySql.Data.EntityFrameworkCore.Design -version 8.0.17

    let me know if required any other details.

  3. Y have a error:
    Scaffold-DbContext “Server=localhost;Port=3306;database=zz1;user=root; password=soporte1″ MySql.EntityFrameworkCore -OutputDir models -f
    Build started…
    Build succeeded.
    Missing required argument ”.

    Packege reference
    Id Versions ProjectName
    — ——– ———–
    Microsoft.EntityFrameworkCore {6.0.0-preview.1.21102.2} ConsoleApp9
    MySql.Data {8.0.23} ConsoleApp9
    Microsoft.EntityFrameworkCore.De… {6.0.0-preview.1.21102.2} ConsoleApp9
    Microsoft.EntityFrameworkCore.Re… {2.0.0-preview1-final} ConsoleApp9
    Microsoft.EntityFrameworkCore.Tools {6.0.0-preview.1.21102.2} ConsoleApp9
    Microsoft.EntityFrameworkCore.Sq… {6.0.0-preview.1.21102.2} ConsoleApp9
    MySql.EntityFrameworkCore {5.0.0-m8.0.23} ConsoleApp9

  4. Dear sir,

    i cant connect to Mysql it has an error like this :

    Authentication to host ‘localhost’ for user ‘root’ using method ‘mysql_native_password’ failed with message: Access denied for user ‘root’@’localhost’ (using password: YES)

    do you have any advise ?
    Thanks in advance.

    • Have you checked if you’re on the right port? I think the default port should be 3306, if you chose another one you’ll probably have to specify it in your connection. The other thing might be that your password is wrong or that the server is off, check in the Windows Services just in case and make sure the service MySQLxx is set to Running. If you have any other questions feel free to ask!


Leave a Comment

Your data will be safe! Your e-mail address will not be published. Also other data will not be shared with third person. Required fields marked as *