Database Context

Add the ConnectionStrings to the appsettings.json pointing to the local install of the PostgreSQL like seen below.

.\appsettings.json

{
	"Logging": {
		"LogLevel": {
			"Default": "Information",
			"Microsoft": "Warning",
			"Microsoft.Hosting.Lifetime": "Information"
		}
	},
	"AllowedHosts": "*",
	"ConnectionStrings": {
		"SlackCloneDb": 
		"Username=postgres;Password=postgres;Host=localhost;Database=SlackCloneDb;"
	}
}

Next we need to create a DbContext, create a SlackCloneDbContext.cs file in the Entities folder.

.\Entities\SlackCloneDbContext.cs

using Microsoft.EntityFrameworkCore;
using System;

namespace SlackClone.Entities
{
    public class SlackCloneDbContext : DbContext
    {
        public SlackCloneDbContext(
            DbContextOptions<SlackCloneDbContext> options) : base(options) { }

        // Creates the Tables
        public DbSet<User> Users { get; set; }
        public DbSet<UserStatus> UserStatuses { get; set; }
        public DbSet<DirectMessage> DirectMessages { get; set; }
        public DbSet<Channel> Channels { get; set; }
        public DbSet<ChannelMessage> ChannelMessages { get; set; }
        public DbSet<ChannelChannelMessage> ChannelsChannelMessages { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Allows auto generation of UUID
            modelBuilder.HasPostgresExtension("uuid-ossp");

            // Many to Many Table Mapping
            modelBuilder.Entity<ChannelChannelMessage>()
                .HasKey(bc => new { bc.ChannelId, bc.MessageId });
            modelBuilder.Entity<ChannelChannelMessage>()
                .HasOne(bc => bc.Channel)
                .WithMany(b => b.ChannelsChannelMessages)
                .HasForeignKey(bc => bc.ChannelId);
            modelBuilder.Entity<ChannelChannelMessage>()
                .HasOne(bc => bc.ChannelMessage)
                .WithMany(c => c.ChannelsChannelMessages)
                .HasForeignKey(bc => bc.MessageId);

            // Generates random uuid for Id fields
            modelBuilder.Entity<User>().Property(p => p.Id)
                .HasDefaultValueSql("uuid_generate_v4()");
            modelBuilder.Entity<UserStatus>().Property(p => p.Id)
                .HasDefaultValueSql("uuid_generate_v4()");
            modelBuilder.Entity<ChannelMessage>().Property(p => p.Id)
                .HasDefaultValueSql("uuid_generate_v4()");
            modelBuilder.Entity<Channel>().Property(p => p.Id)
                .HasDefaultValueSql("uuid_generate_v4()");

            // Creates initial data for user statuses    
            modelBuilder.Entity<UserStatus>().HasData(
                new UserStatus
                {
                    Id = Guid.NewGuid(),
                    Status = "Available",
                    Description = "User is online",
                    Rank = 1,

                },
                new UserStatus
                {
                    Id = Guid.NewGuid(),
                    Status = "Busy",
                    Description = "User is busy",
                    Rank = 2,
                },
                new UserStatus
                {
                    Id = Guid.NewGuid(),
                    Status = "Do Not Disturb",
                    Description = "User oes not want to be disturbed",
                    Rank = 3,
                },
                new UserStatus
                {
                    Id = Guid.NewGuid(),
                    Status = "Away",
                    Description = "User is away",
                    Rank = 4,
                },
                new UserStatus
                {
                    Id = Guid.NewGuid(),
                    Status = "Offline",
                    Description = "User is offline",
                    Rank = 5
                }
            );

            // Creates an initial general channel
            modelBuilder.Entity<Channel>().HasData(
                new Channel { Id = Guid.NewGuid(), Name = "general" }
            );
        }
    }
}

Once the DbContext is created, its time to create migrations and update the database to create the initial tables, relationships and data.

Before you can perform migrations you need to install dotnet-ef tools.

dotnet tool install --global dotnet-ef

After dotnet-ef tools are installed, we can create the initial migrations by typing the following command.

dotnet ef migrations add InitialDbCreation

This will create db migrations in the ./Migrations folder. After the migrations are created we need to update the database so the tables can be created, this can be done using the following command.

dotnet ef database update

If all has gone well you can use pgAdmin to view the database we just created like seen below.