Using EF Core with MySQL in DotNet Core 1.0

• Download source files - 4.2 KB

Introduction

As of today, AWS (Amazon Web Services) supports Lambdas written in a number of languages, one of them being DotNet Core which is a great thing. However, the downside is that the version supported is 1.0 when DotNet Core 2.0 has been out since Aug 2017 and v1.1 has been out since Oct 2016. This post targets DotNet Core 1.0 interacting with MySQL database using EF Core. In the next article, I'll cover how to write serverless lambda functions based on what is discussed in this post.

Getting Things Ready

I am using Windows 10 and Visual Studio 2017 Community Edition. If you are still using Visual Studio 2015 or an earlier version, you really should upgrade, the 2017 community edition is free.

Firstly, we need DotNet Core 1.0 SDK to be installed. You can check this by running the Developer Command prompt and typing:

dotnet --version

You should see 1.0 being printed. If you don't have DotNet Core installed, you can 'modify' the Visual Studio installation from 'Add / Remove' programs in the control panel or just run 'appwiz.cpl'.

Next, you need MySql running somewhere on the network or on the local development machine. MySQL can be downloaded freely from https://dev.mysql.com/downloads/mysql/.

Next, we need to create the sample database. The downloadable ZIP file contains the database file, sampledb.sql. Open this in MySQL Workbench and run it to have the sample database ready.

Code First or DB First?

Many lives and hours have been lost to this debate. Should you create the database first or should you create the domain classes (in C# perhaps) first?

In a code-first approach, you define the entities using C# or Java or whatever language you use along with the relationships after which you can use Entity Framework's database generation capability to create the database.

In a database-first approach, you define the database first, you can then use a tool like Visual Studio EDMX designer to drag & drop tables from the database into the IDE which then generates the required entity classes and models the relationships. The gotcha here is that there is no EDMX designer out of the box for Entity Framework core.

The approach I am going to take in this post is to create the database first and model my entity classes manually.

Preparing the Project

If you are creating a new DotNet Core project in Visual Studio, once the project is created, the first thing is to add Nuget package reference to the correct MySql data provilder & EF providers.
In the Nuget package manager console, type in:

Install-Package MySql.Data.Core -version 7.0.4-IR-191
Install-Package MySql.Data.EntityFrameworkCore -version 7.0.4-IR-191

This should pull in everything required to work with MySQL using EF core.

Starting Out Small

I'll first model the Blog to Comments relationship which is a one-to-many relation; one blog can have many comments and one comment belongs to one and only one blog.

The important things to note are:

• The Blog class contains a collection of Comment objects.
• The Comment class contains the BlogId which provides the foreign-key path to the blog.
• The Comment class also contains a reference to the associated Blog object.

The next important thing is to model this relationship so EF knows how Blogs and Comments are related. To do this, we hook into the OnConfiguring method of DbContext as shown below:

Let's try and add a new blog with 2 comments. Build the sample application & ensure you have modified the method GetConnectionString in the BlogDbContext class with correct information to connect to your MySQL instance. To populate the sample blogs, run the program by typing:

dotnet blogsample -populate

Hopefully, there aren't any exceptions. Now you can retrieve what was populated using:

dotnet blogsample

Output:

Something Bigger

The last thing we are going to model is the many-to-many relation that exists between Blogs and Categories. Like the last time, we'll first define the classes and modify some properties of the existing classes.

Important points to notice:

• We have a class to represent the 3^rd table named BlogCategory which contains the foreign keys of Blog and Category.
• It also contains the reference to the Blog and Category objects.
• The Blog class now holds a collection of BlogCategory objects.
• The Category class holds a collection of BlogCategory objects.

The next thing is to tell EF about the relations that exist between Blogs and Categories. Like last time, this is done in the OnConfiguring method of DbContext derived class shown below:

We use the following fluent syntax to retrieve categories and comments from a blog:

foreach (Blog blog in db.Blogs.Include(b => b.Comments).Include
        (b => b.BlogsCategories).ThenInclude(bg => bg.Category))	
{
	Console.WriteLine(blog);
	Console.WriteLine();
}

To populate the categories and one sample blog, run the program with the -populatecategories option. If you now run the program without any arguments, you see the previous blogs plus the new blog with the sample SQL category.

Points of Interest

In this post, relations have been defined using only Entity Framework's fluent API. I ran into a lot of problems while working with this particular version of MySQL EF provider like incompatible versions of the MySQL nuget packages for Core 1.0, unsupported composite primary keys using attribute style definitions. I hope I have been able to provide a working solution to getting started with MySQL, EF Core on the DotNet Core 1.0 platform.

History

• Version 1 - things seem to be working :)