📓 Configuration for Entity Framework Core
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.
Project Setup
Adding and Removing Dependencies
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.
Database Connection String
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 theappsettings.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];"
}
}
Update the Database Name and Create a New Database
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:
- Update the
database
key in the database connection string inappsettings.json
toto_do_list_with_ef_core
. - Copying the
to_do_list_with_mysqlconnector
database by exporting it, and then importing it with the new name ofto_do_list_with_ef_core
. For exact instructions on importing and exporting databases, visit this lesson from the weekend homework.
Remove 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; }
}
}
Creating the Database Context and Entities
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:
- An entity represents a single database table (with columns and types) as a C# model in our app. The entities in our To Do List app are described in
Item.cs
andCategory.cs
. - The database context represents the entire database (with all the tables) as a model in our app. The model is always called
ProjectNameContext
and it extends the functionality of EF Core'sDbContext
class. TheDbContext
class represents a session with our MySQL database that can be used to query and save instances of our entities (Item
s andCategory
s). Every time we want to reference a database in an app, we will do so using an instance of theDbContext
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'sDbContext
class. This ensures it includes all default built-inDbContext
functionality.The
ToDoListContext
class contains a property of typeDbSet<Item>
namedItems
that represents theitems
table in our database. With thisItems
property, we've declared an entity calledItems
in our To Do List database context.- Note that in order for
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 (<>
) afterDbSet
.
- Note that in order for
We also include a constructor that inherits the behavior of its parent class constructor. As
ToDoListContext
is an extension of theDbContext
class, we're invoking the constructor behavior from that class;base
represents the parentDbContext
class.In the
ToDoListContext
constructor, we list a parameter of the typeDbContextOptions
calledoptions
. The argument for this parameter will be passed through dependency injection fromProgram.cs
when our app is built and run. Note that we haven't yet set up anything inProgram.cs
— neither our database as a service or our database context options. We'll do that next!
Configuring Program.cs
to Run our Database as a Service
Next, we'll update Program.cs
to do two things:
- Configure our
WebApplicationBuilder
to set up a service for our database. - Configure our MySQL database to use the database connection string in
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;
Thisusing
directive imports theUseMySql()
method from theMicrosoft.EntityFrameworkCore
namespace. We also maintain theusing ToDoList.Models
, because we use the modelToDoListContext
withinProgram.cs
.With
builder.Services.AddDbContext<ToDoListContext>(...)
we add EF Core as a service to our To Do List app. We specifyToDoListContext
as the type of EF Core database context that we want to set up. Remember thatToDoListContext
is a representation of our MySQL database.As the argument to the
AddDbContext<T>()
method, we configure our To Do List database context options:- We state that we want to use a MySQL database with the
UseMySql()
method. - We pass in two arguments to the
UseMySql()
method:- The database connection string in
appsettings.json
, which we get from accessingbuilder.Configuration
. Remember thatappsettings.json
is implicitly loaded when we begin the process of building our web app host by runningWebApplication.CreateBuilder(args);
. - The version of the MySQL server, which we set to autodetect.
- The database connection string in
- Note that the syntax
=>
indbContextOptions => 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.
- We state that we want to use a MySQL database with the
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.
Updating to EF Core Naming Conventions
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:
- Using MySQL Workbench, find the
items
table in the Navigator, right click and select Alter Table....- Change the name of the
id
column toItemId
. - Change the name of the
description
column toDescription
. - Click Apply.
- Change the name of the
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.
Repository Reference
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