Connecting C# to MySQL and MariaDB With Entity Framework Core
This tutorial shows how to connect a C# application to MySQL and MariaDB using Entity Framework Core with dotConnect for MySQL. You’ll create EF Core models using Entity Developer - visual ORM builder and Scaffold-DbContext, build a console app, and perform CRUD operations through a structured database access layer.
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 an EF Core model via Entity Developer
1. Right-click anywhere inside Solution Explorer and select Add > New Item.
2. Go to Installed > C# Items > Data, select Devart EF Core Model, and click Add.
3. In the Create Model Wizard, select Database First and click Next.
4. Fill in the details of your MySQL database connection and click Next.
5. Select Generate From Database and click Next.
6. Choose the database objects you want to scaffold. You can select all, but since we've been using the Actor table, let's select only it.
7. Define the naming convention for the property names in the database object and click Next. We suggest keeping the default settings this time.
8. On the next page, configure the necessary settings, including file selection for saving the connection, and defining the names of DbContext classes. Select your EF Core version and .NET target framework, then click Next.
9. Choose the model diagram contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, select All Entities and click Next.
10. Choose code generation templates for your objects. You can define parameters according to your preferences or apply default settings. For this tutorial, use the default settings. Click Next.
11. Your model is ready now. Click Finish.
The model you created opens.
This guideline was designed with Visual Studio in mind. However, if you prefer VS Code, JetBrains Rider, or another similar tool, you can use ED as a standalone application to work with the models efficiently.
Connect to MySQL and retrieve data
This section demonstrates how to connect to a MySQL database and retrieve the first 10 rows from the Actor table using EF Core. The SakilaModel DbContext is configured with a MySQL connection string in its OnConfiguring method. We will query the data and display it in the console for verification.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new sakilaModel())
{
var actors = context.Actors
.Take(10)
.ToList();
Console.WriteLine("First 10 Actors:");
Console.WriteLine("---------------");
foreach (var actor in actors)
{
Console.WriteLine($"ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}, Last Update: {actor.LastUpdate:yyyy-MM-dd HH:mm:ss}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
}
Run the application. If everything is configured correctly, the console will display the first 10 records from the Actor table.
Insert new records using EF Core
In this section, we insert a new test actor into the Actor table. EF Core's Add method stages the record, while SaveChanges persists it to the database.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new sakilaModel())
{
var newActor = new Actor
{
FirstName = "Test",
LastName = "Actor",
LastUpdate = DateTime.Now
};
context.Actors.Add(newActor);
int rowsAffected = context.SaveChanges();
Console.WriteLine("Insert Result:");
Console.WriteLine("-------------");
Console.WriteLine($"Rows affected: {rowsAffected}");
Console.WriteLine($"Inserted Actor - ID: {newActor.ActorId}, Name: {newActor.FirstName} {newActor.LastName}, Last Update: {newActor.LastUpdate:yyyy-MM-dd HH:mm:ss}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
}
Run the application. The console will display the inserted record for verification purposes.
Update MySQL data using EF Core
This section demonstrates how to update the test actor's first and last names. We retrieve the record by first name and last name, modify the properties, and call SaveChanges to persist the changes.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new sakilaModel())
{
var actor = context.Actors
.FirstOrDefault(a => a.FirstName == "Test" && a.LastName == "Actor");
if (actor != null)
{
actor.FirstName = "Updated";
actor.LastName = "Star";
actor.LastUpdate = DateTime.Now;
int rowsAffected = context.SaveChanges();
Console.WriteLine("Update Result:");
Console.WriteLine("-------------");
Console.WriteLine($"Rows affected: {rowsAffected}");
Console.WriteLine($"Updated Actor - ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}, Last Update: {actor.LastUpdate:yyyy-MM-dd HH:mm:ss}");
}
else
{
Console.WriteLine("Test Actor not found.");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
}
After running the application, the console displays the updated record for verification.
Delete MySQL data using EF Core
Finally, we delete the test actor from the Actor table. The record is retrieved by first name and last name, removed using the Remove method, and SaveChanges commits the deletion.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new sakilaModel())
{
var actor = context.Actors
.FirstOrDefault(a => a.FirstName == "Updated" && a.LastName == "Star");
if (actor != null)
{
context.Actors.Remove(actor);
int rowsAffected = context.SaveChanges();
Console.WriteLine("Delete Result:");
Console.WriteLine("-------------");
Console.WriteLine($"Rows affected: {rowsAffected}");
Console.WriteLine($"Deleted Actor - ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
}
else
{
Console.WriteLine("Test Actor not found.");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
}
After running the application, the console confirms the successful deletion.
Video tutorial: How to connect EF Core to MySQL and MariaDB
