swirl
Home Software Blog Wallpapers Webtools
Using EF Core with MySQL in DotNet Core 1.0
Monday 20, November 2017   |   Post link   |   Sample code  Download related material
MySQL EF Core DotNet Core 1.0

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.

Do we have everything we need?

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 running '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.

Blog database tables

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.

Blog and comments classes

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:
Modelling blog and comments relation

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:
Sample output of comments

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.

Blogs and categories

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 Blogs and Categories. Like last time this is done in the OnConfiguring method of DbContext derived class shown below:

Modelling blogs and categories

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.

Blogs and categories output

And that's all for now, hope this post was useful.

Sample code  Download related material


Comments

Posts By Year

2023 (5)
2022 (10)
2021 (5)
2020 (12)
2019 (6)
2018 (8)
2017 (11)
2016 (6)
2015 (17)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (4)
.NET Core (2)
ASP.NET MVC (4)
AWS (5)
AWS API Gateway (1)
Android (1)
Apache Camel (1)
Architecture (1)
Audio (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (2)
CloudHSM (1)
Containers (4)
Corporate culture (1)
Database (3)
Database migration (1)
Desktop (1)
Docker (1)
DotNet (3)
DotNet Core (2)
ElasticSearch (1)
Entity Framework (3)
Git (3)
IIS (1)
JDBC (1)
Java (9)
Kibana (1)
Kubernetes (1)
Lambda (1)
Learning (1)
Life (7)
Linux (1)
Lucene (1)
Multi-threading (1)
Music (1)
OData (1)
Office (1)
PHP (1)
Photography (1)
PowerShell (2)
Programming (28)
Rants (5)
SQL (2)
SQL Server (1)
Security (2)
Software Engineering (1)
Software development (2)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Unit Tests (1)
Utilities (3)
VC++ (1)
VMWare (1)
VSCode (1)
Visual Studio (2)
Wallpapers (1)
Web API (2)
Win32 (1)
Windows (9)
XML (2)

Posts By Tags

.NET(6) API Gateway(1) ASP.NET(4) AWS(3) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Asynch(1) Authentication(2) Azure(2) Backup(1) Beliefs(1) BlockingQueue(1) Book review(2) Books(1) Busy(1) C#(4) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Camel(1) Certificates(1) Checkbox(1) CloudHSM(1) Cmdlet(1) Company culture(1) Complexity(1) Consumer(1) Consumerism(1) Containers(3) Core(2) Custom(2) DPI(1) Data-time(1) Database(4) Debugging(1) Delegates(1) Developer(2) Dockers(2) DotNetCore(3) EF 1.0(1) Earphones(1) Elastic Search(1) ElasticSearch(1) Encrypted(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JDBC(1) JSON(1) JUnit(1) JWT(1) Java(3) JavaScript(1) Kubernetes(1) Life(1) LinkedIn(1) Linux(2) Localization(1) Log4J(1) Log4J2(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mirror(1) Mobile Apps(1) Modern Life(1) Money(1) Music(1) NGINX(1) NTFS(1) NUnit(2) OData(1) OPENXML(1) Objects(1) Office(1) OpenCover(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Producer(1) Programming(2) Python(2) QAAC(1) Quality(1) REDIS(2) REST(1) Runtimes(1) S3-Select(1) SD card(1) SLF4J(1) SQL(2) SQL Code-first Migration(1) SSH(2) Sattelite assemblies(1) School(1) Secrets Manager(1) Self reliance(1) Service(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Spring(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Ubuntu(1) Url rewrite(1) VMWare(1) VSCode(1) Validation(2) VeraCode(1) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(2) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1) iTunes(1) renew(1) security(1)