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.
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.
You might get prompted by the window below, just click Apply again to run the SQL statement.
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.
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.
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.
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.
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.
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).
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.
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.
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).
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.
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.
newClass.Student.Add(newStudent);
dataContext.Class.Add(newClass);
//Don't forget to save changes!
dataContext.SaveChanges();
}
}
}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.
Conclusion
Done! You have learned how to scaffold your MySQL database with Entity Framework. If you found this article helpful, please feel free to share it. Thank you
