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 Blog
s and Comment
s 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 Blog
s 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 3rd 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 Blog
s 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 :)