Entity Framework Core and MySQL

From all the articles written on ASP.NET Core, Entity Framework Core and newer versions of MySQL, connecting the three should be a piece of cake in theory. And, as we all know, theory and practice are the same thing… in theory. However, on a recent project I actually needed to connect the three myself and was surprised how well it all worked and how easy it was to set up.

My task was to get data from the MySQL database, do some processing on it and then store the results to a different database. However, the project itself will not be discussed into details here, just the setup and the usage of EF Core with the MySQL database.

Disclaimer: It is possible to argue that using ASP.NET Core for this is not the right tool, that it could be done faster with SQL language itself etc. I will not go into that here, for my use case this was not important so I used ASP.NET Core, as I feel most comfortable in this environment.

Project setup

At the time of this blog post, .NET Core 3.0 was already released. However, Entity Framework Core did not provide support for connecting to MySQL databases in .NET Core 3.0. There were other suggestions out there for ORMs like Pomelo. This did not work for me, so I ended up using ASP.NET Core 2.2 for the time being 🙁

For this particular project we will build a new .NET Core 2.2 Console Application that will just connect to the MySQL database, get some data, manipulate it in some way and export it to some format. You can use the code from my GitHub repo to follow along.

Start with creating a new .NET Core 2.2 application:

1. File –> New Project
2. From the project templates choose Console app (.NET Core)
3. Set up the name and the folder of the application and choose Create
4. In project properties make sure that the Targeted Framework is .NET Core 2.2

Next, we need to add Entity Framework to our application. Entity Framework has a special version to be used with .NET Core applications called (very creatively) Entity Framework Core. However, as we are connecting to a MySQL database, we do not want to add “regular” Entity Framework Core (which is set to connect to MSSQL Server like databases) but we need to add packages for MySQL Entity Framework Core.

The two packages you need to add are:

• MySql.Data.EntityFrameworkCore
• MySql.Data.EntityFrameworkCore.Design

If you are doing this from the Package Manager Console command line, use:

Install-Package MySql.Data.EntityFrameworkCore -Version 8.0.18
Install-Package MySql.Data.EntityFrameworkCore.Design -Version 8.0.18

If you are doing this from the NuGet Package Manager, these are the packages you want to add:

Make sure you have the Connector/NET in the same version as your NuGet packages, so they can work together. The Connector/NET can be downloaded from here.

Scaffolding the database

In my project I had been given a database to work with. This means that I want to create my models from my database – this is called “scaffolding the database”. In order to do this, you need to use the Package Manager Console and use the following command:

Scaffold-DbContext "server=localhost;port=3306;user=myuser;password=mypass;database=mydatabase" MySql.Data.EntityFrameworkCore -o DataModels -f

It is possible that this will fail with the message saying that Scaffold-DbContext is not a recognized commands. This happened to me and what solved it was installing the package Microsoft.EntityFrameworkCore.Tools:

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 2.2.6

However, other possible issues and solutions are discussed on StackOverflow.

If everything went OK, you should have the DataModels folder created in your project, and in it you should be able to see classes created from your database tables.

That’s it?? OK, so let’s use it!

We have a pretty basic example. We have a list of students, a list of courses, and we are tracking when did each student enroll into any given course. You can find the sample database in my GitHub repo, and the ER model is given here:

Our goal is to get a list of sentences saying “John Doe enrolled into Basic Math on September 1st 2019” for all students enrolling all classes ordered by date of enrollment descending.

In MySQL a query could look something like this:

SELECT students.name, students.lastname, students.email, courses.name, studentcourses.date_enrolled
FROM
	students INNER JOIN studentcourses ON students.id = studentcourses.student_id
    INNER JOIN courses ON studentcourses.course_id = courses.id
ORDER BY studentcourses.date_enrolled DESC;

In C# using Entity Framework Core that would amount to something like:

using (var ctx = new demoprojectdatabaseContext())
{
    var studentCourses = ctx.Studentcourses
        .Include(sc => sc.Student)
        .Include(sc => sc.Course)
        .OrderByDescending(sc => sc.DateEnrolled);

    foreach (var sc in studentCourses)
    {
        Console.WriteLine("{0} {1} enrolled into {2} on {3}", sc.Student.Name, sc.Student.Lastname, sc.Course.Name, sc.DateEnrolled.Date.ToShortDateString());
    }
}

For the database provided it should output the sentences like in the screenshot below. If you have your database with your test data, it should output something similar.

Everything works out of the box and beautifully. So, like I said in the introduction – this was unexpectedly easy and straightforward.

Conclusion

I was working on an ASP.NET Core project recently using MSSQL Server and Entity Framework Core. We built a clean architecture with a data layer that communicates to the database behind interfaces so it could be easily switched at point in time. A colleague saw the code and said “Ah, this is nice. Yeah, in our last project we did not do that. We just made the project depend on Entity Framework Core. We discussed it and we figured that, even if we end up changing the database (which very rarely happens), Entity Framework Core has an adapter for it anyway!”

While I still think you should always architect software so that you can switch layers easily, he did have a point. With Entity Framework Core working with MS SQL Server databases, MySQL databases and even Postgress databases (check the Npgsql project), it is indeed a question if you can take the liberty of being tightly coupled to it and still not have a problem.

Either way, I am really glad that connecting to any database is seamless nowadays and that it works in the same manor so that the knowledge I already have from connecting to MS SQL Server databases using Entity Framework Core can be reused.