Working with MySQL and ASP.NET Core the usage of EF Core

Working with MySQL and ASP.NET Core the usage of EF Core
Working with MySQL and ASP.NET Core the usage of EF Core

Entity Framework Core aka EF Core is an Object Relational Mapper (ORM) library which maps the database objects from a precise database onto their equal entity fashions in the software and interprets all the operations carried out on the software entity fashions into like minded database SQL operations. EF Core affords the a whole lot wanted flexibility for builders so that they can center of attention greater on the software good judgment alternatively than spending time on constructing the records persistence layer.

We commonly see most of the builders the use of EF Core to join their AspNetCore purposes to SQL Server, to which the EF Core affords the best possible grade compatibility and support. But it would not suggest that EF Core can not be used in working with different database vendors such as MySQL, Oracle or PostgreSQL. EF Core offers nuget bundle libraries for builders to join and work with different database carriers aside from SQL Server. In this article, let’s appear at how we can connect an AspNetCore software to a MySQL database the use of EFCore.

A Little about MySQL:

MySQL is a famous opensource database with excessive patronage and developer community. It is plenty recognised for its overall performance and replication competencies in addition to its opensource nature which makes it famous amongst builders and clients alike. It is broadly used alongside with PHP in creating serverside purposes in LAMP stack.

One can down load and deploy MySQL for free from their internet site and comes with a entire package deal referred to as as MySQL Workbench which contains of the MySQL server alongside with Client and Command Line plugin.

To preserve matters easy for our example, we go by means of the Docker route which helps us run a MySQL occasion in our computer besides having to go via all the installations. We make use of the Docker photo for MySQL on hand in Docker Hub and run it interior a Container the usage of Docker Compose in our system.

#docker-compose.yaml#
version: '3'
services: 
    database:
        image: mysql
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
            MYSQL_ROOT_PASSWORD: [email protected]
        ports: 
            - 3306:3306

Let’s take the instance of constructing our very acquainted Readers API that shops and fetches Readers from a database for a given request. We have already regarded at how we reap this with EF Core with an SQL Server background.

Configuring EF Core for MySQL:

To join our software to the MySQL database with the aid of EFCore, we start via putting in the Nuget bundle for EFCore which configures it to work with MySQL. Microsoft documentation for EFCore cites two programs – one a free model and the different supplied through Oracle which is a paid one. For our purpose, we go with the aid of the former which is a free one – Pomelo.EntityFrameworkCore.MySql

>  dotnet add package Pomelo.EntityFrameworkCore.MySql

The above dotnet core command installs the MySql plugin into our application. Remember that we ought to have already have the proper EFCore library established in our utility for this to work. For starters, we add EFCore competencies to our utility by using including the EntityFrameworkCore.Design library to our software thru CLI as:

> dotnet add package Microsoft.EntityFrameworkCore.Design

Once these applications are delivered to the application, we would register MySql database issuer provider interior the ConfigureServices() technique as below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<IDataService, DataService>();
    services.AddDbContextPool<MySqlContext>(builder =>
    {
        builder.UseMySql(
            Configuration.GetConnectionString("DefaultConnection"), 
            options =>
            {
                options.EnableRetryOnFailure();
            });
    });

    services.AddControllersWithViews();
}

the place the connectionString is configured in appsettings with the under value:

"Server=127.0.0.1;Port=3306;Uid=root;[email protected];Database=readers"

Since we’re connecting from a localhost provider through Docker, we grant the Server as 127.0.0.1 (instead of localhost) and 3306 is the default port below which the MySql server runs. Uid and Password are our username and password for the database and Database refers to the database we’re gonna work on.

the line builder.UseMySql() is comparable to the line builer.UseSqlServer() we use for an SQL Server context. This configures the EFCore to work on a MySql context. This ensures that all the kinds we use in our C# utility entity fashions are translated into their equal MySql datatypes and the constraints designated are translated accordingly.

First Migration:

To end matters off, we can run a migration on the related database simply to make certain our database works fine. This creates the database readers special in the connectionString if now not already available. If the software already includes migrations created before, they can additionally be utilized as well.

> dotnet ef migrations add InitialCreate
> dotnet ef database update

By this step, we can see that the database is now created in MySQL with the tables as configured in our DbContext class. To confirm, we can simply refresh the tables beneath Workbench or for a CLI purchaser which is our case, we can run these MySQL commands:

> show databases;
> use readers;
> show tables;

Verifying Reads and Writes:

To confirm the db reads and writes with MySql, we will setup and name a Seed() technique for the duration of our utility bootstrap which pushes a set of Users to the database. This takes place simply earlier than the utility Host is constructed and run as below:

public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();

    using (var scope = host.Services.CreateScope())
    {
        var data = scope.ServiceProvider.GetRequiredService<IDataService>();
        SeedUsers(data);
    }

    host.Run();
}

private static void SeedUsers(IDataService data)
{
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "[email protected]",
        IsActive = true
    });
}

When we name the GET Users API, EF Core need to fetch the User rows we have pushed to the database as a section of the Seed() technique at some stage in software bootstrap and return the collection.

[ApiController]
[Route("api/[controller]")]
public class UsersController 
    : ControllerBase, IController<User>
{
    private readonly IDataService data;
    public UsersController(IDataService data)
    {
        this.data = data;
    }

    [HttpGet]
    [Route("all")]
    public IEnumerable<User> Get()
    {
        return this.data
            .UsersRepository.Get(x => x.IsActive == true);
    }

    ...
}
HOST https://localhost:5001 
GET /api/users/all

Response: [{"id":1,"emailAddress":"[email protected]","isActive":true},
{"id":2,"emailAddress":"[email protected]","isActive":true},
{"id":3,"emailAddress":"[email protected]","isActive":true},
{"id":4,"emailAddress":"[email protected]","isActive":true}..]

Reverse Scaffolding:

Similar to how we can scaffold an present SQL Server database into our software comparable to a Database-First method in EF Core, it is nevertheless viable with MySQL the usage of a comparable strategy with the under command:

> dotnet ef dbcontext scaffold <connString> "Pomelo.EntityFrameworkCore.MySql"

the place connectionString can be some thing as particular before.

In this way, we can join and work with MySQL in an AspNetCore utility except any a lot distinction the usage of the abilities of EFCore.

The supply code for the instance used in this article is handy at:

https://github.com/waqasahmed0323/mysql-efcore-sample

30 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

two × 4 =

You May Also Like