005 Add EF and Create DB - CarrieKroutil/Reactivities GitHub Wiki

Setup Entity Framework

Add Entity Framework NuGet Package

  1. Install extension NuGet Gallery | Publisher: pcislo
  2. [shift + command + p] to open command pallet and type nuget to “Open NuGet Gallery”
  3. Search for package like “Microsoft.EntityFrameworkCore.Sqlite” -
    • Check which project(s) to install. E.g. Persistence.csproj
    • Select version that matches projects target framework
    • Click “Install”

Implement Entity Framework in Code

The following steps will add EF using code first database approach.

  1. In Domain project, add new Activity class -
namespace Domain
{
    public class Activity
    {
        public Guid Id { get; set; }
        public string Title { get; set; }
        public DateTime Date { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }
        public string City { get; set; }
        public string Venue { get; set; }
    }
}
  1. In Persistence project, add new class - DataContext that inherits DbContext with default constructor:
using Domain;
using Microsoft.EntityFrameworkCore;

namespace Persistence
{
    public class DataContext : DbContext
    {
        public DataContext(DbContextOptions options) : base(options)
        {
        }

        public DbSet<Activity> Activities { get; set; }
    }
}

  1. In terminal, run dotnet restore - this will give the API project access to the newly added package.

  2. In API project → Program.cs → Add:

builder.Services.AddDbContext<DataContext>(opt => 
{
    opt.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection"));
});
  1. In API project → appsettings.Development.json → Add:
{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=reactivities.db"
  }
}

Install Entity Framework Core Tools

Above steps are considered code first, now let’s create a migration script to create database from code.

  1. In terminal, run dotnet tool list -g to see if you have dotnet-ef installed globally (-g) and if it is on the current target framework.
    • If not installed, go to dotnet-ef and grab the .NET CLI script to run: dotnet tool install --global dotnet-ef --version 9.0.0.
      • After it runs, if using zsh, you can add it to your profile by running the following command:
cat << \EOF >> ~/.zprofile
# Add .NET Core SDK tools
export PATH="$PATH:/Users/carrie/.dotnet/tools"
EOF
  • Run zsh -l to make it available for current session.
  • If it was already installed but an older version, just change the script above to use “update” in place of “install” and for the desired version.
  1. Run dotnet ef command to check it is working.
  2. Add NuGet package: Microsoft.EntityFrameworkCore.Design into the API project.
  3. This is required for the Entity Framework Core Tools work.

Create Database with Data

Create Migration Script

Above code steps are considered code first, next create a migration script to create database from code.

  1. Run dotnet ef migrations add InitialCreate -s API -p Persistence
    • Terminal should spit out: “Done. To undo this action, use ef migrations remove".
    • New “Migrations” folder should have been created in the Persistence project.
  2. Run dotnet ef database to see available commands.
    • Note: The update command will create db if one does not exist.
  3. Update API project’s Program.cs with below code to run before app.Run();:
using var scope = app.Services.CreateScope();
var services = scope.ServiceProvider;

try
{
    var context = services.GetRequiredService<DataContext>();
    await context.Database.MigrateAsync();
    await Seed.SeedData(context);
}
catch (Exception ex)
{
    var logger = services.GetRequiredService<ILogger<Program>>();
    logger.LogError(ex, "An error occured during migration");
}

Create Database

  1. In terminal, cd API then run dotnet watch to start with hot reload. Database should now exist.
  2. Database created and contains “__EFMigrationsHistory” table to show which scripts have been run.
  3. Press “Ctrl + R” to restart. Press “Ctrl+C” to shut down.

SQLite: View Database

Now that database has been created, lets view it in SqLite.

  1. [shift + command + p] to open command pallet and type sqlite to “SQLite: Open Database”.
    • Select the newly created “reactivities.db” file.
  2. Though it appears nothing has happened, there is a new “SQLITE EXPLORER” menu now available from the File Explorer.

Add Seed Data

  1. In API project, create seed data class for database:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Domain;

namespace Persistence
{
    public class Seed
    {
        public static async Task SeedData(DataContext context)
        {
            // Check if activities have already been created and stop.
            if (context.Activities.Any()) return;
            
            // Otherwise, create a list of activities to be added to the database.
            var activities = new List<Activity>
            {
                new Activity
                {
                    Title = "Past Activity 1",
                    Date = DateTime.UtcNow.AddMonths(-2),
                    Description = "Activity 2 months ago",
                    Category = "drinks",
                    City = "London",
                    Venue = "Pub",
                },
                new Activity
                {
                    Title = "Past Activity 2",
                    Date = DateTime.UtcNow.AddMonths(-1),
                    Description = "Activity 1 month ago",
                    Category = "culture",
                    City = "Paris",
                    Venue = "Louvre",
                },
                new Activity
                {
                    Title = "Future Activity 1",
                    Date = DateTime.UtcNow.AddMonths(1),
                    Description = "Activity 1 month in future",
                    Category = "culture",
                    City = "London",
                    Venue = "Natural History Museum",
                },
                new Activity
                {
                    Title = "Future Activity 2",
                    Date = DateTime.UtcNow.AddMonths(2),
                    Description = "Activity 2 months in future",
                    Category = "music",
                    City = "London",
                    Venue = "O2 Arena",
                },
                new Activity
                {
                    Title = "Future Activity 3",
                    Date = DateTime.UtcNow.AddMonths(3),
                    Description = "Activity 3 months in future",
                    Category = "drinks",
                    City = "London",
                    Venue = "Another pub",
                },
                new Activity
                {
                    Title = "Future Activity 4",
                    Date = DateTime.UtcNow.AddMonths(4),
                    Description = "Activity 4 months in future",
                    Category = "drinks",
                    City = "London",
                    Venue = "Yet another pub",
                },
                new Activity
                {
                    Title = "Future Activity 5",
                    Date = DateTime.UtcNow.AddMonths(5),
                    Description = "Activity 5 months in future",
                    Category = "drinks",
                    City = "London",
                    Venue = "Just another pub",
                },
                new Activity
                {
                    Title = "Future Activity 6",
                    Date = DateTime.UtcNow.AddMonths(6),
                    Description = "Activity 6 months in future",
                    Category = "music",
                    City = "London",
                    Venue = "Roundhouse Camden",
                },
                new Activity
                {
                    Title = "Future Activity 7",
                    Date = DateTime.UtcNow.AddMonths(7),
                    Description = "Activity 2 months ago",
                    Category = "travel",
                    City = "London",
                    Venue = "Somewhere on the Thames",
                },
                new Activity
                {
                    Title = "Future Activity 8",
                    Date = DateTime.UtcNow.AddMonths(8),
                    Description = "Activity 8 months in future",
                    Category = "film",
                    City = "London",
                    Venue = "Cinema",
                }
            };

            await context.Activities.AddRangeAsync(activities);
            await context.SaveChangesAsync();
        }
    }
}
  • Press “Ctrl + R” to restart. Though in .NET 7 it does not always work but appears it should have, don’t use and wait for .NET 8 to see if they fix it to work more accurately. So now press “Ctrl + C” to stop.
    • Instead, use file watcher by running: dotnet watch --no-hot-reload.
  • To view table with data, open “SQLITE EXPLORER” and click on the “play” button to the right of “Activities” database
⚠️ **GitHub.com Fallback** ⚠️