In this lesson, we'll explain how to configure a project to use Entity Framework Core with a MySQL database. The instructions in this lesson can be used for any app you build to use EF Core with a MySQL database.
For this walkthrough, we'll use our To Do List app as an example, and we'll start where we left off with our To Do List app: using MySqlConnector
to communicate with a MySQL database. That means this walkthrough will include instructions on how to remove MySqlConnector
and how to update the custom methods we created over the weekend homework to communicate with our MySQL database. As always, you are welcome to code a long with these lessons, or just read through them. We'll provide links to an example repo as needed and at the very end of the walkthrough.
Out with the old and in with the new! We'll start by adding the additional dependencies that we will need for this EF Core project. Go to the root directory of the production project (ToDoList
) via the terminal and add the following dependencies.
$ dotnet add package Microsoft.EntityFrameworkCore -v 6.0.0
$ dotnet add package Pomelo.EntityFrameworkCore.MySql -v 6.0.0
Notice one of these does not start with Microsoft. That's because Pomelo.EntityFrameworkCore.MySql
is actually a third-party package that can be downloaded from the same source as official Microsoft packages. Check out Entity Framework Core Official GitHub Repo to learn more.
Let's also remove the MySqlConnector
package that we added over the weekend.
$ dotnet remove package MySqlConnector
Notice how MySqlConnector is now gone from our .csproj file. The dotnet remove package [PACKAGE NAME]
command allows us to conveniently and cleanly remove packages that we do not need from our project.
Next, we need to add a database connection string to our app.
When we used the MySqlConnector package to communicate with the database, we put our database connection string in appsettings.json
and loaded it during the creation of our web app host in Program.cs
. We used appsettings.json
to protect the sensitive data within our database connection string by adding it to our .gitignore
so that it is never tracked in our Git history. We also included instructions on how to recreate an appsettings.json
in our project READMEs.
Well, we'll use appsettings.json
in just the same way to load and protect our database connection string with EF Core. So if you are following along and updating your To Do List app, there are no major changes that we need to make with our processes around using appsettings.json
.
All other projects you create will need the following:
appsettings.json
listed in the .gitignore
and committed to Git history.obj
bin
appsettings.json
Setup instructions in the project's README.md
that explain how to recreate the appsettings.json
, including the location of file (within the project's production directory) and the contents of the file.
And an appsetting.json
with the following contents, replacing the following values with your own:
[YOUR-USER-HERE]
with your username [YOUR-PASSWORD-HERE]
with your password[YOUR-DB-NAME]
with the name of your database{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Port=3306;database=[YOUR-DB-NAME];uid=[YOUR-USER-HERE];pwd=[YOUR-PASSWORD-HERE];"
}
}
We recommend creating a new database for this walkthrough of using EF Core to communicate with a MySQL database. Doing so will preserve the database we set up with version of the To Do List that uses MySqlConnector. To Do this, follow these steps:
database
key in the database connection string in appsettings.json
to to_do_list_with_ef_core
.to_do_list_with_mysqlconnector
database by exporting it, and then importing it with the new name of to_do_list_with_ef_core
. For exact instructions on importing and exporting databases, visit this lesson from the weekend homework.DatabaseConfig.cs
We'll configure our project to use EF Core in Program.cs
. In the process, we'll set up our MySQL database to run as a service in Program.cs
. Because of this, we no longer need the ToDoList/Models/DatabaseConfig.cs
file that contains the DBConfiguration
class. Start by removing this file from your To Do List project.
Remove the following:
namespace ToDoList.Models
{
public static class DBConfiguration
{
public static string ConnectionString { get; set; }
}
}
Next, we need to create a model of our database and tables within our project for Entity Framework Core to use to map our C# classes and objects to MySQL database tables and entries, and vice versa. EF Core uses specific terminology for these models:
Item.cs
and Category.cs
. ProjectNameContext
and it extends the functionality of EF Core's DbContext
class. The DbContext
class represents a session with our MySQL database that can be used to query and save instances of our entities (Item
s and Category
s). Every time we want to reference a database in an app, we will do so using an instance of the DbContext
class.We don't have to change anything with Item.cs
or Category.cs
to turn those models into EF Core entities. We list the entities in our application by defining them in the database context. So, let's create that next.
Within ToDoList/Models
create a new file called ToDoListContext.cs
and add the following code to it:
using Microsoft.EntityFrameworkCore;
namespace ToDoList.Models
{
public class ToDoListContext : DbContext
{
public DbSet<Item> Items { get; set; }
public ToDoListContext(DbContextOptions options) : base(options) { }
}
}
Let's walk through what's happening here:
Our ToDoListContext
class inherits (or "extends") from EF Core's DbContext
class. This ensures it includes all default built-in DbContext
functionality.
The ToDoListContext
class contains a property of type DbSet<Item>
named Items
that represents the items
table in our database. With this Items
property, we've declared an entity called Items
in our To Do List database context.
DbSet<T>
to create an entity for a specific C# object in our app, we always need to be sure to include the class name (Item
) within the angle brackets (<>
) after DbSet
.We also include a constructor that inherits the behavior of its parent class constructor. As ToDoListContext
is an extension of the DbContext
class, we're invoking the constructor behavior from that class; base
represents the parent DbContext
class.
In the ToDoListContext
constructor, we list a parameter of the type DbContextOptions
called options
. The argument for this parameter will be passed through dependency injection from Program.cs
when our app is built and run. Note that we haven't yet set up anything in Program.cs
— neither our database as a service or our database context options. We'll do that next!
Program.cs
to Run our Database as a ServiceNext, we'll update Program.cs
to do two things:
WebApplicationBuilder
to set up a service for our database.appsettings.json
.Let's look at the new code in Program.cs
. We'll show the entire file.
using Microsoft.AspNetCore.Builder;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using ToDoList.Models;
namespace ToDoList
{
class Program
{
static void Main(string[] args)
{
WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllersWithViews();
builder.Services.AddDbContext<ToDoListContext>(
dbContextOptions => dbContextOptions
.UseMySql(
builder.Configuration["ConnectionStrings:DefaultConnection"], ServerVersion.AutoDetect(builder.Configuration["ConnectionStrings:DefaultConnection"]
)
)
);
WebApplication app = builder.Build();
// app.UseDeveloperExceptionPage();
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.MapControllerRoute(
name: "default",
pattern: "{controller=Home}/{action=Index}/{id?}");
app.Run();
}
}
}
Let's explain the updates:
First note that we have a new using
directive: using Microsoft.EntityFrameworkCore;
This using
directive imports the UseMySql()
method from the Microsoft.EntityFrameworkCore
namespace. We also maintain the using ToDoList.Models
, because we use the model ToDoListContext
within Program.cs
.
With builder.Services.AddDbContext<ToDoListContext>(...)
we add EF Core as a service to our To Do List app. We specify ToDoListContext
as the type of EF Core database context that we want to set up. Remember that ToDoListContext
is a representation of our MySQL database.
As the argument to the AddDbContext<T>()
method, we configure our To Do List database context options:
UseMySql()
method.UseMySql()
method: appsettings.json
, which we get from accessing builder.Configuration
. Remember that appsettings.json
is implicitly loaded when we begin the process of building our web app host by running WebApplication.CreateBuilder(args);
.=>
in dbContextOptions => dbContextOptions
creates a lambda expression. In short, a lambda expression is just a way to write an anonymous function in a condensed fashion. Lambda expressions are used in a variety of languages and are similar to arrow functions in JavaScript. For more information about lambdas, check out the Microsoft documentation.With EF Core now configured to run as a service, EF Core will automatically handle opening and closing a database connection each time we query our database. Pretty neat!
Note that while we name our EF Core database context ToDolistContext
, the name of the actual database is still set in appsettings.json
.
And finally, if you'd like to read more about how to configure EF Core as a service, visit the documentation Configuring DbContext with Dependency Injection and scroll down to the "Using DbContext with dependency injection" section.
For EF Core to map our C# models to a MySQL database, we need to follow specific naming conventions, two to be precise:
By default EF Core maps entity properties (the properties of our C# models) to database columns that have the same name. This mapping is case sensitive. Because of this, the column names in our database tables must match the property names and case of the properties of our models.
It's also important to note that Entity Framework Core recognizes an entity's primary key through naming convention as well: for a property to be recognized as a primary key, we need to name the property Id
or [ClassName]Id
. We're going to go with the second way, naming our primary keys as [ClassName]Id
.
To meet these naming convention, we need to go into our to_do_list_with_ef_core
database and update the column names in the following way:
items
table in the Navigator, right click and select Alter Table....
id
column to ItemId
.description
column to Description
.Next, go open Item.cs
in the ToDoList
project, and update your Item
model's Id
property to be ItemId
. The Item
model should now look like this:
namespace ToDoList.Models
{
public class Item
{
public int ItemId { get; set; }
public string Description { get; set; }
... // Item constructor and methods omitted
}
}
Note that we will follow these naming conventions when setting up databases and entities from now on.
Follow the link below to view how a sample version of the To Do List app should look like at this point. Note that this is a link to a specific branch in the repository.
Example GitHub Repo for To Do List with EF Core: 1_ef_core_setup
Both of the following files ToDoList.csproj
and appsettings.json
should live in our project's production directory. In our To Do List example project, they would live in the ToDoList/
directory.
ToDoList.csproj
--
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>net6.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.0" />
</ItemGroup>
</Project>
appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Port=3306;database=[YOUR-DB-NAME];uid=[YOUR-USER-HERE];pwd=[YOUR-PASSWORD-HERE];"
}
}
Make sure your appsettings.json
file is in your .gitignore
!
using Microsoft.AspNetCore.Builder;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using ToDoList.Models;
namespace ToDoList
{
class Program
{
static void Main(string[] args)
{
WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllersWithViews();
builder.Services.AddDbContext<ToDoListContext>(
dbContextOptions => dbContextOptions
.UseMySql(
builder.Configuration["ConnectionStrings:DefaultConnection"], ServerVersion.AutoDetect(builder.Configuration["ConnectionStrings:DefaultConnection"]
)
)
);
WebApplication app = builder.Build();
// app.UseDeveloperExceptionPage();
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.MapControllerRoute(
name: "default",
pattern: "{controller=Home}/{action=Index}/{id?}");
app.Run();
}
}
}
using Microsoft.EntityFrameworkCore;
namespace ToDoList.Models
{
public class ToDoListContext : DbContext
{
public DbSet<Item> Items { get; set; }
public ToDoListContext(DbContextOptions options) : base(options) { }
}
}
For EF Core to map our C# models to a MySQL database, we need to follow specific naming conventions, two to be precise:
By default EF Core maps entity properties (the properties of our C# models) to database columns that have the same name. This mapping is case sensitive. Because of this, the column names in our database tables must match the property names and case of the properties of our models.
It's also important to note that Entity Framework Core recognizes an entity's primary key through naming convention as well: for a property to be recognized as a primary key, we need to name the property Id
or [ClassName]Id
. We're going to go with the second way, naming our primary keys as [ClassName]Id
.