Entity Framework Core 3 code first with PostgreSQL provider

Following my posts on how to build a docker host with a Raspberry Pi and deploy a PostgreSQL container on Raspberry Pi, now I want to show you how you could use the new Entity Framework Core 3 library based on the last dotnet core 3 that is ready for production from preview 7.

As usual we start to prepare our environment to code so download and install the latest dotnet core 3 sdk.
To edit the project I prefer to use VS-CODE , so create a folder DemoEFCore and open with the editor, we will create the project and add Entity Framework Core 3 and the PostgreSQL provider.

The code

so open the terminal and type:

dotnet create console
dotnet add package Microsoft.EntityFrameworkCore --version 3.0.0-preview8.19405.11
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 3.0.0-preview8

Now we create our entities to map and store our data.

Create a TableMaster.cs file, you can see also a configuration class used to define an alternate Unique Constraint Key other than the Primary Key assigned to Id property by conventions.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace DemoEFCore
{
    public class TableMaster
    {
        public int Id { get; set; }
        public string MasterKey { get; set; }
        public DateTime InsertDate { get; set; }

        public ICollection<TableDetail> Details { get; set; }
    }

    public class TableMasterConfiguration : IEntityTypeConfiguration<TableMaster>
    {
        public void Configure(EntityTypeBuilder<TableMaster> builder)
        {
            builder.HasAlternateKey(p => p.MasterKey);
        }
    }
}

Create a TableDetail.cs file; this time we configure a simple index on DetailCode property.

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace DemoEFCore
{
    public class TableDetail
    {
        public int Id { get; set; }
        public TableMaster TableMaster { get; set; }
        public DateTime InsertDate { get; set; }
        public string DetailCode { get; set; }
        public decimal Quantity { get; set; }
    }

    public class TableDetailConfiguration : IEntityTypeConfiguration<TableDetail>
    {
        public void Configure(EntityTypeBuilder<TableDetail> builder)
        {
            builder.HasIndex(p => p.DetailCode);
        }
    }
}

The relationship between master and detail is assured by the the two property:

public ICollection Details { get; set; }

public TableMaster TableMaster { get; set; }

Than we create the context; by overriding OnModelCreating we can apply our custom configurations.

using Microsoft.EntityFrameworkCore;

namespace DemoEFCore
{
    public class DemoEFCoreContext : DbContext
    {
        public DemoEFCoreContext(DbContextOptions<DemoEFCoreContext> options) : base(options) { }

        public DbSet<TableMaster> TableMasters { get; set; }
        public DbSet<TableDetail> TableDetails { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new TableMasterConfiguration());
            modelBuilder.ApplyConfiguration(new TableDetailConfiguration());

            base.OnModelCreating(modelBuilder);
        }

    }
}

On the Main of Program.cs we add the code to create the options to use and connect to a PostgreSQL server with a single line of code (remember to change the IP). You can see that it is very simple to try with a different provider.

var optionsBuilder = new DbContextOptionsBuilder<DemoEFCoreContext>();           optionsBuilder.UseNpgsql($"Server=192.168.74.10;Port=5432;Database=DemoEFCore;User Id=postgres;Password = dbpass;");

using var db = new DemoEFCoreContext(optionsBuilder.Options);
db.Database.EnsureDeleted();    // ensure to delete the database if present
db.Database.EnsureCreated();    // ensure the database creation

// add some data to Master and Detail
db.Add(new TableMaster
{
    InsertDate = DateTime.Now,
    MasterKey = "KEY001",
    Details = new TableDetail[] {
        new TableDetail {
            InsertDate = DateTime.Now,
            DetailCode = "Code001",
            Quantity = 1
        },
        new TableDetail {
            InsertDate = DateTime.Now,
            DetailCode = "Code002",
            Quantity = 9
        }

    }
});

db.SaveChanges();   // save the changes

The execution

Run the code by executing on the terminal:

dotnet run

Check the results

Now connect with pgAdmin to your host and you should see our new DemoEFCore database

Navigate to Schemas > public > Tables and you could see the main and detail tables structure.
By convention Id field is of type Identity with PrimaryKey and the property TableMaster in TableDetail has been remappet to TableMasterId by a shadow property.

And finally have a look on the data

You can find the whole project on github.

Enjoy the code!