Connecting C# to MySQL and MariaDB With Entity Framework Core

Why dotConnect for MySQL?

dotConnect for MySQL is a powerful data provider designed for MySQL and MariaDB operations. It offers numerous features that enhance functionality, improve performance, and simplify database development.

Prerequisites

Before starting, ensure you have the following prerequisites installed:

• Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
• dotConnect for MySQL: A high-performance ADO.NET data provider for MySQL with enhanced ORM support and database connectivity features.
• Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation (more on this later).
• MySQL test database: A sample database provided by MySQL for learning and testing.

Download and activate dotConnect for MySQL

30-day free trial version

Download and install dotConnect for MySQL directly on your machine, or install the Devart.Data.MySQL NuGet package.

No license key is required, and you can start exploring the product immediately.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

To activate a connection in your application, add the License Key to your connection string.

Create a .NET project

1. Open Visual Studio and select Create a new project. In the search box, enter ASP.NET Core Project, select the matching search result, and click Next.

2. Enter a project name. In our tutorial, the project is named MySQL_EF_Core.

3. Choose the framework. In our example, we use .NET 8. Click Create.

4. Once the project is created, install dotConnect for MySQL. Right-click anywhere in Solution Explorer and select Manage NuGet Packages.

5. Click Browse and search for Devart.Data.MySQL.EFCore. Select the matching result, choose your project, and click Install.

6. Run the application by pressing F5 to launch the scaffolded webpage.

Create an EF Core model

Once you have configured the database, you can move on to the next step: creating an EF Core model. You can do this in two ways: using Scaffold-DbContext or via Entity Developer.

Create an EF Core model using Scaffold-DbContext

1. Make sure the EF Core Tools are installed in your development environment. To install them globally, run this command in the .NET command-line interface (CLI):

dotnet tool install --global dotnet-ef

2. Install Scaffold-DbContext using the Package Manager Console. In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console and run this command:

Install-Package Microsoft.EntityFrameworkCore.Tools

3. When the package is installed, you can use Scaffold-DbContext to generate DbContext and entity classes for your MySQL database. Run the following command in the Package Manager Console, replacing placeholders with your actual credentials:

Scaffold-DbContext "Server=127.0.0.1;Port=3306;UserId=youruserId;Password=yourpassword;Database=sakila; LicenseKey=yourlicensekey;" Devart.Data.MySQL.Entity.EFCore -OutputDir Models

4. After executing the command, the ModelContext file and the Models folder, containing the table entity classes, will be generated and appear in Solution Explorer. This means the migration has been applied, and the Actor table has been created.

Create an EF Core model via Entity Developer

1. Before installing Entity Developer, close your Visual Studio instance.

2. Install Entity Developer following the on-screen instructions.

3. Open the MySQL_EF_Core project in Visual Studio again.

4. Right-click the MySQL_EF_Core project in Solution Explorer and select Add > New Item.

5. Go to Installed > C# > ASP.NET Core > Data, select Devart EF Core Model, and click Add.

6. In the Create Model Wizard, select the Database First option and click Next.

7. Configure the connection parameters of your MySQL database, using the database you downloaded in the Prerequisites section. Once completed, click Test Connection to ensure the connection is successful, then click Next.

8. Next, select the Generate From Database option and click Next.

9. Choose which database objects you want to scaffold. You can select all of them, but since we’ve been using the Actor table, let’s select only this table.

10. Define the naming convention for the property names in the database object and click Next. It is recommended to keep the default settings.

11. On the next page, configure the necessary settings, including file selection for saving the connection, specifying connection string versions, and defining the names of DbContext classes. Select .NET 8 (or the framework you selected for your project in the Create New Project section) and click Next.

12. Choose the model diagram content. You can use all entities, split the entities by database, or customize the selection. For this tutorial, select All Entities and click Next.

13. Choose code generation templates for your objects. You can define various parameters or apply default settings. For this tutorial, use the default settings. Click Next.

14. Your model is successfully created. Clear the Download 'Devart.Data.MySQL.EFCore' NuGet package checkbox because this package has already been added to the project. Then, click Finish.

When your model opens, you will see the generated classes such as DataModel.Actor and DataModel.sakilaModel.

Now, create a class called LicenseKey to store your dotConnect for MySQL Activation Key located in the Customer Portal.

Next, in your generated DataModel.sakilaModel, update the OnConfiguring method to use your License Key for the database connection. Your configuration should be set up as follows:

optionsBuilder.UseMySql($"User
Id=testuserId;Password=testpassword;Host=localhost;Database=sakila;LicenseKey={LicenseKey.Key}; 
Persist Security Info=True");  

Now, move on to creating a connection class.

Create a connection class

Using the generated context, let's create the services we'll use with our database.

1. Right-click the MySQL_EF_Core project, select Add > Class, and add a class named DatabaseConnectionClass.

2. Paste this code into the created file:

public class DatabaseConnectionClass
{
    private readonly sakilaModel _context;
                            
    public DatabaseConnectionClass()
    {
        _context = new sakilaModel();
    }
    public decimal AddActor(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Actors.Add(actor);
        _context.SaveChanges();
        return actor.ActorId;
    }
    public bool DeleteActor(int Id)
    {
        var actor = _context.Actors.FirstOrDefault(a => a.ActorId == Id);
        if (actor != null)
        {
            _context.Actors.Remove(actor);
            _context.SaveChanges();
            return true;
        }
        return false;
    }
                            
    public IEnumerable GetActorById(int Id)
    {
        return _context.Actors.Where(a => a.ActorId == Id).ToList();
    }
                            
    public List GetActors()
    {
        return _context.Actors.ToList();
    }
                            
    public Actor UpdateActor(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Actors.Update(actor);
        _context.SaveChanges();
        return actor;
    }
}

Read data from MySQL

Let's try to read data from the database table using the implementation we've just created.

1. In the Controllers folder, add a new class named ActorsController.

2. In the created file, inside namespace MySQL_EF_Core.Controllers, add the following code:

[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
    private readonly DatabaseConnectionClass _context;

    public ActorsController()
    {
        _context = new DatabaseConnectionClass();
    }

    [HttpGet]
    public IEnumerable GetActors()
    {
        return _context.GetActors();
    }
}
                                    

3. Run your application by pressing F5.

4. Go to the GetActors endpoint, click Try it out, and then click Execute.

The query result appears in the response body.

Insert new data into MySQL

Next, let's try to insert a new record into the Actors table.

1. In the ActorsController class, add this piece of code:

[HttpPost]
public decimal AddActor(Actor actor)
{
    return _context.AddActor(actor);
}

This uses the helper method in DatabaseConnectionClass to insert a new record.

2. Run the application again. We can see that the new AddActor endpoint has appeared.

3. Click the expander arrow and add this JSON object to the request body:

{
    "actorId": 201,
    "firstName": "John",
    "lastName": "Lewis"
}
    

4. Click Execute.

The response should be the ID you assigned to the added data.

Update MySQL data

Imagine that John Lewis has decided to change his surname. Let's update the data.

1. In the ActorController class, add the following method:

[HttpPut]
public Actor UpdateActor(Actor actor)
{
return _context.UpdateActor(actor);
}

This uses the helper method in DatabaseConnectionClass to update a particular record.

2. Run the application, select UpdateActor, and add this JSON object to the request body:

{
"actorId":201,
"firstName": "John",
"lastName": "Mcginnis"
}

3. Click Execute.

In the response body, you'll see the record with the updated last name.

Delete data from MySQL

Assume that John has left the acting business. Let's delete his record from the table.

1. Add these two methods to the ActorController class:

[HttpDelete("{Id}")]
public bool DeleteActor(int Id)
{
return _context.DeleteActor(Id);
}
[HttpGet("{Id}")]
public IEnumerable<Actor> GetActorsById(int Id)
{
return _context.GetActorById(Id);
}

The DeleteActor method will delete a row from the table and return true if the request is successful. If no record for that particular ID exists or an error occurs, it'll return false.

The GetActorsById method will verify the actor no longer exists.

2. Run the application, select the DeleteActor endpoint, and enter the ID of the previously created record.

3. Click Execute.

This should return true, and the record gets removed.

4. To verify this, try to get the record with that ID using the GetActorsById endpoint.

The request returns an empty array, which means the record no longer exists.

Conclusion

In this tutorial, we've demonstrated how the integration of Entity Framework Core with MySQL using dotConnect for MySQL streamlines data operations in .NET applications. The advanced features of dotConnect for MySQL, combined with the simplicity of Entity Framework Core, offer a powerful and efficient way to handle database interactions in MySQL-powered applications. With these tools and techniques, developers can effortlessly build scalable and maintainable .NET solutions that leverage MySQL databases.