Lesson Sunday

In the last lesson, we introduced Entity Framework Core. Let's integrate Entity into our To Do List which is currently a C#/.NET application that utilizes MySqlConnector to communicate with our database.

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 5.0.0
$ dotnet add package Pomelo.EntityFrameworkCore.MySql -v 5.0.0-alpha.2

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.

Connection String

Next, we need to add the database connection to our app. We'll do this using the connection string we currently have as a property of the DBConfiguration class in our Startup.cs file.

Before we move forward with this, it's important to note that the file we are about to create will store sensitive information that we don't want to share with the world. As such, before we make any commits with it in our project, we should add it to our .gitignore:

.gitignore
...
*/appsettings.json

Now we'll store the connection string in a new file called appsettings.json in the directory of our project's production folder, ToDoList/:

ToDoList/appsettings.json
{
    "ConnectionStrings": {
        "DefaultConnection": "Server=localhost;Port=3306;database=to_do_list;uid=root;pwd=[YOUR-PASSWORD-HERE];"
    }
}
  • Note that the database will change based on the database we are connecting to and that uid and pwd may vary as well depending on MySql configurations.

Since our appsettings.json has been put in our .gitignore, users will not have access to it when cloning our project. So, you will need to add specific instructions in your README, telling the user where to create the file, and what code to include in it -- obviously, it would be against the point of ignoring it, if you included the username and password in these instructions, so make sure that's formatted in the same way as we've shown it above, such as ...database=to_do_list;uid=[YOUR-USERNAME-HERE];pwd=[YOUR-PASSWORD-HERE]...

Let's also remove the DBConfiguration class declaration from our Startup.cs file since it is redundant We'll prefer storing our connection string in our appsettings.json due to the way Entity Framework Core is configured.

Remove the following:

ToDoList/Startup.cs
  public static class DBConfiguration
  {
    public static string ConnectionString = "server=localhost;user id=root;password=epicodus;port=3306;database=to_do_list;";
  }

Configuring Startup

Now that we've added the connection string and dependencies, we must tell our app what to do with it in the Startup class. First, we need to alter our constructor:

Startup.cs
...

public Startup(IWebHostEnvironment env)
{
    var builder = new ConfigurationBuilder()
        .SetBasePath(env.ContentRootPath)
        .AddJsonFile("appsettings.json"); //this line replaces .AddEnvironmentVariables();
    Configuration = builder.Build();
}
...

Next we need to add the Entity Framework middleware. To do this we need to update the IConfigurationRoot auto-generated property of our Startup class to include set;. This will allow us to set our app's connection string. Next we'll update the ConfigureServices() method by adding some Entity configuration code:

Startup.cs
public IConfigurationRoot Configuration { get; set; }
...
public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();

    //New code
    services.AddEntityFrameworkMySql()
        .AddDbContext<ToDoListContext>(options => options
        .UseMySql(Configuration["ConnectionStrings:DefaultConnection"], ServerVersion.AutoDetect(Configuration["ConnectionStrings:DefaultConnection"])));
}
...
  • Here we've added a form of Entity that understands MySQL as a service.

  • We've also configured that service to use a particular database context with the AddDbContext() method, which will be a representation of our database.

  • We further configure Entity Framework to use our default connection by passing it to the UseMySQL() method.

Note that in order to call the UseMySql method, we need to add the using directive for Entity to Startup.cs:

using Microsoft.EntityFrameworkCore;

Creating a Model

Next, we need to create a model that will allow us to interact with the database, which we've called ToDoListContext in the code above. This differs from our previous method of using defining a Database class in the Database.cs file.

Entity Framework contains a class called DbContext. Every time we want to reference a database in an app, we will do so using an instance of this DbContext class.

Similarly, tables in the database are represented by DbSet objects defined inside of a DbContext.

In the Model-View-Controller pattern, our DbContext will be a model. Let's add it now.

  • In the Models folder let's remove Database.cs and add a file called ToDoListContext.cs.
Models/ToDoListContext.cs
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 Entity Framework's DbContext. This ensures it includes all default built-in DbContext functionality.

  • ToDoListContext also contains a property of type DbSet named Items that represents the Items table in our ToDoList database and lets us interact with it. DbSet needs to know what C# object it’s going to represent, so we must include Item in 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 some constructor behavior from that class. Further, we are passing a variable of DbContextOptions called options to our constructor through its argument, instantiating our ToDoListContext with the options we defined in Startup.cs using is called a dependency injection.

  • If you'd like to read more about configuration, check out the documentation (Configuring DbContext with Dependency Injection) and scroll down to the "Using DbContext with dependency injection" section.

Finally, we need a using directive for this model in our Startup.cs file. If we don't, our application won't understand what we mean by <ToDoListContext>. At the top of the Startup.cs, add using ToDoList.Models;. See the cheat sheet of this lesson for the full Startup.cs file.

Repository Reference

Follow the link below to view how a sample version of the project should look at this point. Note that this is a link to a specific commit in the repository.

Example GitHub Repo for To Do List

File Location


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>net5.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.0" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="5.0.0-alpha.2" />
  </ItemGroup>
</Project>

appsettings.json

{
    "ConnectionStrings": {
        "DefaultConnection": "Server=localhost;Port=3306;database=to_do_list;uid=root;pwd=[YOUR-PASSWORD-HERE];"
    }
}

Make sure your appsettings.json file is in your .gitignore!

Startup.cs


using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using ToDoList.Models;

namespace ToDoList
{
  public class Startup
  {
    public Startup(IWebHostEnvironment env)
    {
      var builder = new ConfigurationBuilder()
          .SetBasePath(env.ContentRootPath)
              .AddJsonFile("appsettings.json");
      Configuration = builder.Build();
    }

    public IConfigurationRoot Configuration { get; set; }

    public void ConfigureServices(IServiceCollection services)
    {
      services.AddMvc();

      services.AddEntityFrameworkMySql()
        .AddDbContext<ToDoListContext>(options => options
        .UseMySql(Configuration["ConnectionStrings:DefaultConnection"], ServerVersion.AutoDetect(Configuration["ConnectionStrings:DefaultConnection"])));
    }

    public void Configure(IApplicationBuilder app)
    {
      app.UseDeveloperExceptionPage();
      app.UseRouting();

      app.UseEndpoints(routes =>
      {
        routes.MapControllerRoute("default", "{controller=Home}/{action=Index}/{id?}");
      });

      app.UseStaticFiles();

      app.Run(async (context) =>
      {
        await context.Response.WriteAsync("Hello World!");
      });
    }
  }
}

Program.cs

using System.IO;
using Microsoft.AspNetCore.Hosting;

namespace ToDoList
{
  public class Program
  {
    public static void Main(string[] args)
    {
      var host = new WebHostBuilder()
        .UseKestrel()
        .UseContentRoot(Directory.GetCurrentDirectory())
        .UseIISIntegration()
        .UseStartup<Startup>()
        .Build();

      host.Run();
    }
  }
}

Models/ToDoListContext.cs

using Microsoft.EntityFrameworkCore;

namespace ToDoList.Models
{
  public class ToDoListContext : DbContext
  {
    public DbSet<Item> Items { get; set; }

    public ToDoListContext(DbContextOptions options) : base(options) { }
  }
}

Lesson 22 of 36
Last updated February 24, 2022