First Experience with ASP.NET Core MVC & PostgreSQL -

Goal

To build an ASP.NET Core 5 MVC web app linked with a PostgreSQL.

Motivation

SQL Server is proprietary.
SQLite used in Microsoft’s ASP.NET Core 5 MVC tutorial isn’t made for web apps.
MySQL doesn’t perform well with concurrent read-writes. It’s dual-licensed like GitLab.
Some users find PostgreSQL cost-effective.

Useful tutorials

MS’s tutorial in item 2 above.
Wes Doyle’s YouTube video goes through the steps
MS’s tutorial for Razor Pages with EF Core migrations

Steps

Create a superuser in the database.
```
sudo -u postgres psql
```
By default the user postgres is the admin of the DBMS.
```
CREATE USER mydev WITH SUPERUSER;
ALTER USER mydev WITH PASSWORD 'my0eV';
```
Then we go to pgAdmin and grant super user mydev all privileges.
dotnet mvc -o Foo

Create framework-independent model Models/Movie.cs.

using System;
using System.ComponentModel.DataAnnotations;

namespace Foo.Models
{
    public class Movie
    {
        public int Id { get; set; }
        public string Title { get; set; }

        [DataType(DataType.Date)]
        public DateTime ReleaseDate { get; set; }
        public string Genre { get; set; }
        public decimal Price { get; set; }
    }
}

Add necessary NuGet Packages.

dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Tools.Dotnet
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

The Foo.csproj file will automatically get updated.

Create a database context class Data/MovieContext.cs. It’s responsible for connecting the database with the app.

using Microsoft.EntityFrameworkCore;
using Foo.Models;

namespace Foo.Data
{
    public class MvcMovieContext : DbContext
    {
        public MvcMovieContext (DbContextOptions<MvcMovieContext> options)
            : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Port=5432;Database=MvcMovie;Username=mydev;Password=my0eV;Integrated Security=true;Pooling=true")
            .UseSnakeCaseNamingConvention();

        public DbSet<Movie> Movies { get; set; }
    }
}

The UseSnakeCaseNamingConvention method converts ReleaseDate in the model class to release_date in the final database.

DbSet<TE> TEs is a space to hold instances of TEs.

I got stuck by mistakingly putting the OnConfiguring method inside the constructor.

Startup settings. In the Startup.ConfigureServices method, register the database context with the service during app startup.

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

    services.AddDbContext<MvcMovieContext>(options =>
            options.UseNpgsql(Configuration.GetConnectionString("MvcMovieContext")));
}

At the top of Startup.cs, we add two packages.

using Microsoft.EntityFrameworkCore;
using Foo.Data;

There’s no need to import Npgsql.EntityFrameworkCore.PostgreSQL. The package import in the Foo.csproj file in step 3 will do.

Add the ConnectionStrings in appsettings.json. In the YouTube videos, it appears as Server and User Id, but after a code search in the package’s GitHub repo, I’m convinced that the documented connection strings parameters are valid.
```
"AllowedHosts": "*",
  "ConnectionStrings": {
    "MvcMovieContext": "Host=localhost;Port=5432;Database=MvcMovie;Username=mydev;Password=my0eV;Integrated Security=true;Pooling=true"
}
```
Execute the following two commands. The build should succeed in seconds.
```
dotnet ef migrations add InitialCreate
dotnet ef database update
```
I forgot to install dotnet-ef, so those two didn’t run at first.
A new database MvcMovie is generated. The table movies is empty. Observe that the columns are in snake case: ReleaseDate in the model class file is converted to release_date. That’s a common practice in PostgreSQL.