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

install-package Pomelo.EntityFrameworkCore.MySql

Also install the Entity Framework Core Tools

install-package Microsoft.EntityFrameworkcore.Tools -version 3.1.10

Creating the Domain Model

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

namespace MySQLEFCoreExample
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string Name { get; set; }

        //
        //Navigation property Returns the Employee Address
        public virtual EmployeeAddress EmployeeAddress { get; set; }
        
        //Navigational Property to Department
        public Department Department { get; set; }
    }

    public class EmployeeAddress
    {
        public int EmployeeID { get; set; }
        public string Address { get; set; }

        //
        //Navigation property Returns the Employee object
        public virtual Employee Employee { get; set; }
    }

    public class Department
    {
        public int DepartmentID { get; set; }
        public string Name { get; set; }

        //Navigational Property Returns List of Employees
        public ICollection<Employee> Employees { get; set; }
    }

}

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

namespace MySQLEFCoreExample
{
    public class EFContext : DbContext
    {

        private const string connectionString = "server=localhost;port=3306;database=EFCoreMySQL;user=root;password=root";

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql(connectionString);
        }
    }
}

DbSet

Create DbSet Property for the domain models.

        public DbSet<Employee> Employees { get; set; }
        public DbSet<EmployeeAddress> EmployeeAddress { get; set; }
        public DbSet<Department> Departments { get; set; }

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.

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            //Configuring the one to many relationship

            modelBuilder.Entity<EmployeeAddress>()
                    .HasKey(e => e.EmployeeID);

            modelBuilder.Entity<Employee>()
                .HasOne<Department>(e => e.Department)
                .WithMany(d => d.Employees)
                .HasForeignKey(e => e.DepartmentID);
        }

Create Migrations

The final context class.

namespace MySQLEFCoreExample
{
    public class EFContext : DbContext
    {

        private const string connectionString = "server=localhost;port=3306;database=EFCoreMySQL;user=root;password=root";

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql(connectionString);
        }

        public DbSet<Employee> Employees { get; set; }
        public DbSet<EmployeeAddress> EmployeeAddress { get; set; }
        public DbSet<Department> Departments { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            //Configuring the one to many relationship

            modelBuilder.Entity<EmployeeAddress>()
                    .HasKey(e => e.EmployeeID);

            modelBuilder.Entity<Employee>()
                .HasOne<Department>(e => e.Department)
                .WithMany(d => d.Employees)
                .HasForeignKey(e => e.DepartmentID);
        }


    }
}

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

Run add-migration v1 run to create the migration

add-migration v1

Next, run update-database to create the database

update-database

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

Inserting Data

Insert a single row to the Department Table

using (var db = new EFContext())
{

    Department department = new Department();
    department.Name = "HR";
    db.Add(department);
    db.SaveChanges();

    Console.WriteLine(department.DepartmentID + " with name " + department.Name + " Created ");

}

Insert a new Employee

using (var db = new EFContext())
{
    Employee emp = new Employee();
    emp.DepartmentID = 1;
    emp.Name = "Joe Bidden";
    db.Add(emp);

    db.SaveChanges();

}

Add Multiple rows using the AddRange

using (var db = new EFContext())
{


    List<Department> depts = new List<Department>();

    var department = new Department();
    department.Name = "Finance";
    depts.Add(department);

    department = new Department();
    department.Name = "Accounts";
    depts.Add(department);

    department = new Department();
    department.Name = "Sales";
    db.AddRange(depts);
    db.SaveChanges();

}

Querying the data

Query the data

using (var db = new EFContext())
{
    var depts = db.Departments.ToList();

    foreach (var dept in depts)
    {
        Console.WriteLine(dept.DepartmentID + " :" + dept.Name);
    }

}

Reference

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