How To Configure A .Net Core 3.1 With MariaDB Using EntityFrameworkCore

Introduction

Most of the time for our projects, we need to use a database to manage data and to make the application more dynamic. Therefore, in this tutorial, we will see how to configure a .NET Core 3.1 project to work with MariaDB using Entity Framework Core. In this tutorial we will create a small .NET Core API to test our configuration with MariaDB. But before we do it, let me quickly explain these technologies.

.NETcore: is a free and open-source cross-platform framework for development. It enables us to develop web or API in C#.
MariaDB: is a free and open-source Relational Database Management System (RDMS).
Entity Framework: an open-source Object-Relational Mapping (ORM) framework for .Net applications. It enables us to work with a database using .NET objects so we can do requests on a database using .Net objects. It is very useful.
Entity Framework Core: is the new version of Entity Framework after version 6.x. It is a cross-platform framework, unlike Entity Framework. For more information, see EntityFrameworkCore.

Creating database and web API projects

Create a database (Don't forget, we are using MariaDB) with one table named "student" and insert the data.

-- Create table
CREATE TABLE `student` (
`Id` int(11) NOT NULL,
`FirstName` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`LastName` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`BirthDate` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Insert data
INSERT INTO `student` (`Id`, `FirstName`, `LastName`, `BirthDate`) VALUES
(1, 'Vanessa', 'Scott', '2000-11-06'),
(2, 'Emma', 'Reid', '1998-04-20'),
(3, 'Ava', 'May', '2000-04-05'),
(4, 'Joshua', 'Cameron', '2001-02-06'),
(5, 'Jan', 'Powell', '2001-05-02'),
(6, 'Justin', 'Metcalfe', '2002-05-10'),
(7, 'Ava', 'Glover', '1999-10-02'),
(8, 'Jason', 'Rampling', '1998-10-20'),
(9, 'Dorothy', 'Ferguson', '1997-05-10'),
(10, 'Sue', 'Reid', '1998-01-20');

Create a .NET core project (I used Visual Studio).

Installing NuGet packages

To install the necessary NuGet packages, I choose to use CLI .NET core because it is a cross-platform tool. Open your console and change the current directory to match with your web API project directory (verify if you can see .csproj file in this directory). Now, we will install "dotnet ef" to enables access to Entity Framework tools using CLI .NET.

Note

We don't need to install EntityFrameworkCore because it is integrated by default to the .NET core project.

dotnet tool install --global dotnet-ef

If the installation has gone fine, you can determine this by typing "dotnet ef," and you will get this output,

With EntityFrameworkCore, we can work on many types of databases like SQL Server, Postegre SQL, MariaDB, MySql, etc... To do this, it needs a plug-in library named "database provider". Now, we will install a database provider "Pomelo.EntityFrameworkCore.MySql," that enables us to communicate with MariaDB or MySql.

dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet add package Pomelo.EntityFrameworkCore.MySql

Scaffolding model and create database context

To scaffold a Model and to create a context (for more information, see DbContext EFCore), we will execute this command,

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=<username>;password=<password>;database=<database>" Pomelo.EntityFrameworkCore.MySql -c AMCDbContext -o Models

Note

Replace username, password, and database by your own information.

Update Startup.cs file

We will use Dependency Injection (DI) by adding our dbcontext "AMCDbContext" to the "ConfigureServices" method in Startup.cs class. In a simple way, the DI mechanism enables us to create dependencies between different objects dynamically based on the description. For more information, see Dependency Injection .NET Core

services.AddDbContext<AMCDbContext>(options => options
.UseMySql("Server=localhost; Database=asp_mariadb_cfg;User=<username>;Password=<password>;",
mysqlOptions =>
mysqlOptions.ServerVersion(new ServerVersion(new Version(10, 4, 6), ServerType.MariaDb))));
/**
* Version(10, 4, 6) is my MariaDB version, to see your version run following
* request in MariaDB's console
* SELECT VERSION();
*/

Test

To test our project, we will scaffold the controller based on a model Student. Follow these instructions to do it: Right-click on controllers -> Add-> Controller... -> API Controller with actions, using Entity Framework and click "Add"

After scaffolding the controller class, run your project and go to https://localhost: /api/Students.

Your .NET core web API is working with the MariaDB database correctly. If you have any suggestions or issues, please tell me in the comments.

Resources

https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet
https://docs.microsoft.com/en-us/ef/core/get-started/install/
https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql
https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext#using-dbcontext-with-dependency-injection