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.
Table of Contents
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
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
1 2 3 | install-package Pomelo.EntityFrameworkCore.MySql |
Also install the Entity Framework Core Tools
1 2 3 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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
- Connecting to the database
- Querying & Updating the database
- Hold the Information needed to configure the database etc.
Create EFContext class under the project folder
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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.
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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
1 2 3 | add-migration v1 |
Next, run update-database to create the database
1 2 3 | update-database |
Open the Database and check the tables, Primary keys & Foreign Keys
Inserting Data
Insert a single row to the Department Table
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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
1 2 3 4 5 6 7 8 9 10 11 12 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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
1 2 3 4 5 6 7 8 9 10 11 12 | using (var db = new EFContext()) { var depts = db.Departments.ToList(); foreach (var dept in depts) { Console.WriteLine(dept.DepartmentID + " :" + dept.Name); } } |
Is there a mistake? I had to add
public int DepartmentID { get; set; }
to class Employee
to get the foreign key to work?
The foreign key for the department object is the EmployeeID field of the employee object!
dose not work
optionsBuilder.UseMySql(connectionString);
this results in an error as the server version is not specified
optionsBuilder.UseMySql(ServerVersion.AutoDetect(connectionString));
this still dose not work….
change it to
ServerVersion sv = MariaDbServerVersion.AutoDetect(connString);
options.UseMySql(connString, sv);