Building APIs with C# & ASP.NET Core

Watch on Frontend Masters

The time has come: let's set up our DbContext and DbSets

First thing we're going to do is install the Microsoft.EntityFrameworkCore.Sqlite package. This will also install Microsoft.EntityFrameworkCore as dependencies.

Setting up your DbContext

Setting up our initial DbContext is pretty simple - we really just need to create a new class called DbContext.

Many .NETters like to put this in a folder called Data or Infrastructure - we're just gonna keep it in the root of the project for now. I don't have a strong opinion because organization is a personal (and team thing)!

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Employee> Employees { get; set; }
}

Let's break down the AppDbContext class:

  • It inherits from DbContext, which gives it all of its goodness
  • It has a constructor that takes a DbContextOptions<AppDbContext> - these options are our configuration for the DbContext, usually the dataset
  • It has a DbSet<Employee> Employees { get; set; } property - this is the collection of Employee entities that are mapped to the Employees table in the database

Setting up your DbContext with dependency injection

We need to set up our DbContext with dependency injection. This is pretty simple - we just need to add it to the IServiceCollection in the ConfigureServices method in Program.cs:

using Microsoft.EntityFrameworkCore;

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));

Wait, what's the builder.Configuration.GetConnectionString("DefaultConnection")? That's a connection string to our database. We need to add that to our appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=employees.db"
  }
}

Setting up your DbContext with Migrations

Migrations are a feature of Entity Framework Core that allows us to make changes to our database schema over time. This is useful for a few reasons:

  • We can make changes to our database schema without dropping and recreating the database
  • We can make changes to our database schema without manually writing SQL (for the most part - more on that later)

We'll use the dotnet ef CLI to manage our migrations.

First, we need to add the Microsoft.EntityFrameworkCore.Tools package to our project. We'll install the tools globally.

dotnet tool install --global dotnet-ef

Then we can start running commands to manage our migrations. We'll start first by creating our database by running:

dotnet ef database update

This will create a new database called employees.db. It does a few other things as well:

❯ dotnet ef database update
Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA journal_mode = 'wal';
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "__EFMigrationsHistory" (
          "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
          "ProductVersion" TEXT NOT NULL
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "MigrationId", "ProductVersion"
      FROM "__EFMigrationsHistory"
      ORDER BY "MigrationId";
info: Microsoft.EntityFrameworkCore.Migrations[20405]
      No migrations were applied. The database is already up to date.
No migrations were applied. The database is already up to date.
Done.

Mainly, creates a database table for us to store our migrations history.

Let's add an extension for VS Code that helps us look at our database: SQLTools. It's popular and straightforward. We'll also install the SQLite SQLTools extension (and the driver, and Node tools, so that this can all run together.)

Once we get connected to the database, we can see that our __EFMigrationsHistory table is empty. Let's change that!

In terminal, run:

dotnet ef migrations add Init

This will create a new migration file in the Migrations folder. Let's take a look at it:

public partial class Init : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Employees",
            columns: table => new
            {
                Id = table.Column<int>(type: "INTEGER", nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                FirstName = table.Column<string>(type: "TEXT", nullable: false),
                LastName = table.Column<string>(type: "TEXT", nullable: false),
                SocialSecurityNumber = table.Column<string>(type: "TEXT", nullable: true),
                Address1 = table.Column<string>(type: "TEXT", nullable: true),
                Address2 = table.Column<string>(type: "TEXT", nullable: true),
                City = table.Column<string>(type: "TEXT", nullable: true),
                State = table.Column<string>(type: "TEXT", nullable: true),
                ZipCode = table.Column<string>(type: "TEXT", nullable: true),
                PhoneNumber = table.Column<string>(type: "TEXT", nullable: true),
                Email = table.Column<string>(type: "TEXT", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Employees", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "EmployeeBenefits",
            columns: table => new
            {
                Id = table.Column<int>(type: "INTEGER", nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                EmployeeId = table.Column<int>(type: "INTEGER", nullable: false),
                BenefitType = table.Column<int>(type: "INTEGER", nullable: false),
                Cost = table.Column<decimal>(type: "TEXT", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_EmployeeBenefits", x => x.Id);
                table.ForeignKey(
                    name: "FK_EmployeeBenefits_Employees_EmployeeId",
                    column: x => x.EmployeeId,
                    principalTable: "Employees",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateIndex(
            name: "IX_EmployeeBenefits_EmployeeId",
            table: "EmployeeBenefits",
            column: "EmployeeId");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "EmployeeBenefits");

        migrationBuilder.DropTable(
            name: "Employees");
    }
}

Let's break down what's happening here:

  • The Up method is where we define the changes we want to make to our database schema
  • The Down method is where we define the changes we want to make to our database schema if we want to revert the changes
  • The migrationBuilder parameter is where we define our table and column changes

The data types that we have migrations for are all defined by the properties in our Employee class as well as the properties of the database we're using.

Note that it even created a EmployeeBenefits table for us, as our property in the Employee class has the List<EmployeeBenefit> defined.

Let's use this opportunity to discuss navigation properties.

Navigation properties

Navigation properties are a feature of Entity Framework Core that allows us to navigate between related entities. In our case, we have a one-to-many relationship between Employee and EmployeeBenefit. This means that an employee can have many benefits, but a benefit can only belong to one employee.

In Employee, we have a List<EmployeeBenefit> Benefits { get; set; } property. This is a navigation property that allows us to navigate from an Employee to its EmployeeBenefits.

We don't need a corresponding Employee property in EmployeeBenefit per se, but we're going to add it anyways because it's good practice.

🌶️🌶️🌶️ We have nullability turned on for our project, which has some interesting implications for EF Core. Note the weird = null! syntax below - you can call this a bit of a compromise to make the nullability feature work nicely with EF Core. See more here.

public class EmployeeBenefits
{
    public int Id { get; set; }
    public int EmployeeId { get; set; }
    public BenefitType BenefitType { get; set; }
    public decimal Cost { get; set; }

    public Employee Employee { get; set; } = null!;
}

We didn't make any changes that should have an impact with EF Core, but sometimes I like to run a new migration to make sure that everything is working as expected.

public partial class meeowwwww : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {

    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

Good, nothing changed - you can run this to remove the migration:

dotnet ef migrations remove

This will remove the last migration from the migrations history.

Run dotnet ef database update again to apply the migrations. Look in SQLTools to see the changes!

We haven't connected this to our Employees endpoints yet - that will happen in the next couple of sections.

Connecting to the DB with SQLTools

Open the SQLTools Extension for VSCode and create a connection to the employees.db database. When you do this, click to Test the Connection. VSCode may ask you to enable some features and restart VSCode. Follow the prompts and recreate the connection if need be after it restarts.