Using MariaDB in an ASP.NET Core API with Entity Framework Core

MariaDB Server is one of the most popular open source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions.

In this post, we will see how to use MariaDB as storage for an ASP.NET API and do simple CRUD operations with Entity Framework Core.

Installing MariaDB

Visit the https://downloads.mariadb.org/ and download the latest stable version. At the moment of writing this article the last stable version is 10.5.4, so I would suggest to download the MSI packaged named “mariadb-10.5.4-winx64.msi” for easier installation.

You can also download the installation package here: mariadb-10.5.4-winx64.msi.

Installing an MSI package is rather easy, so I am not going to get into details about it. When you’ re done, a shortcut of the management tool that comes with MariaDB will be placed on your desktop; it is called HeidiSQL. It doesn’t feature the best UI around, but you can do all sort of operations with it!

If you wish to go into the MySQL world, I would suggest DBeaver Community Edition as management tool, which you can download here: https://dbeaver.io/download/

Creating the schema

We can use HeidiSQL for this! Double click on the HeidiSQL shortcut, fill in the correct username & password (the ones you gave during installation) and click “Open“. Once done, right click on the server’s entry in the tree on the left (= the topmost one). In its context menu, you have “Create new > Database” that you can use to -obviously- create a new database! Follows an image that makes things a bit clearer!

MariaDB - Create New Database

MySQL case sensitivity depends on the OS, so I would suggest to always use lower case in database and table names.

t is convenient that [LiteDB](https://blog.georgekosmidis.net/2019/11/02/using-litedb-in-an-asp-net-core-api/) (and [MongoDB](https://blog.georgekosmidis.net/2019/11/02/using-litedb-in-an-asp-net-core-api/)) is creating collection on demand, but here we need to create the table ourselves. We are going to use Microsoft’s example of a weather forecast API as a base for this post, so the only table needed is a table to store the WeatherForcast object. Just had back to HeidiSQL, and run the following script:

CREATE TABLE `weatherforecasts` (
		`Id` INT(11) NOT NULL AUTO_INCREMENT,
		`Date` DATETIME NULL DEFAULT NULL,
		`TemperatureC` TINYINT(4) NULL DEFAULT NULL,
		`Summary` TEXT(65535) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
		PRIMARY KEY (`Id`)
	)
	COLLATE='latin1_general_ci'
	ENGINE=InnoDB
	;

#asp.net core #aspnet #dotnet #dotnetcore #mariadb #api