Using MySQL & MariaDB in Entity Framework Core

In this tutorial, we will show you how to install & use Mysql & MariaDB in Entity Framework Core. We will create a .NET Core console application. Install Entity Framework Core for MySQL & MariaDB. Create Simple Domain Models. Use Migrations to Create the database. Query, Insert, Update & Delete Rows.

Create a new Console Project

Open Visual Studio 2019 and Create a new Console project using the Template Console App (.NET Core). Name the project as MySQLEFCoreExample

Installing Entity Framework Core for MySQL & MariaDB

Here, we have two choice of Providers

  1. Pomelo.EntityFrameworkCore.MySql
  2. MySql.Data.EntityFrameworkCore

The Pomelo.EntityFrameworkCore.MySql is an Open source that is maintained by Pomelo Foundation. It is updated Frequently, has lesser bugs, and also supports MariaDB. It also has a driver for the latest EF Core version 5.0.

The MySql.Data.EntityFrameworkCore driver is provided by the MySQL itself. But it is slow to update and have a lot of issues

To Install Entity Framework Core, open the package manager console and run the following command

Installing Entity Framework Core for MySQL and MariaDB

Also install the Entity Framework Core Tools

Creating the Domain Model

Create a new class Models under the project folder and add the following classes

The model contains three Entities. Employee, Address & Department. Employee and Address has one to one relationship. Department & Employee has one to many relationship.

Context

The entity classes are managed by the DBContext API. The DBContext is the heart of the Entity Framework Core. This class is responsible for

  1. Connecting to the database
  2. Querying & Updating the database
  3. Hold the Information needed to configure the database etc.

Create EFContext class under the project folder

DbSet

Create DbSet Property for the domain models.

Fluent API

Use Fluent API to configure the models. We use the HasKey method to assign primary key to EmployeeAddress table. We also use it to configure the One to Many relationships between the Department & Employee.

Create Migrations

The final context class.

Now let us create EF Core migrations to generate the migration and create the database

Run add-migration v1 run to create the migration

Next, run update-database to create the database

Open the Database and check the tables, Primary keys & Foreign Keys

Database Created in MySQL Server

Inserting Data

Insert a single row to the Department Table

Insert a new Employee

Add Multiple rows using the AddRange

Querying the data

Query the data

Reference

  1. Pomelo.EntityFrameworkCore.MySql
  2. MySql.Data.EntityFrameworkCore

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top