In this post, I will walkthrough the steps required to setup Postgres database for a code-first approach with Entity Framework Core. Code first approach is a way to write the data model classes in the code and then have the tooling generate and update the target database. Tooling in this case means the dotnet CLI commands.

This application is created and tested only on a Debian Linux machine

Create .NET Core application

We will build a tiny products application to store and display warranty information for the products we purchase. On the terminal, create a folder and change into it.

mkdir netcore-postgres-sample1
cd netcore-postgres-sample1

Create a command line application using dotnet new. Once the command finishes, open VS Code with this project to view our humble beginnings.

If you have the C# extension installed, VS Code will ask to fetch required assets to build the code. Say yes. This command will generate the necessary launch.json and tasks.json configuration files (under the .vscode folder).

dotnet new console
code .

Create a model class Product to denote a product we purchased.

namespace netcore_postgres_sample1
{
    public class Product
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime PurchaseDate { get; set; }
        public DateTime WarrantyDate { get; set; }
    }
}

By default, there is no scaffolding functionality in VS Code that will help us generate a C# Class file. I use an extension called C# IDE extensions to create a class or an interface from the file explorer.

I have added a DataAnnotation Key for the Id property to tell Entity Framework that we want to use this property as the primary key. This is one way to configure our data models. Now that our model class is ready, let us move on to working with Entity Framework Core.

Setup Entity Framework Core

Install the following two packages using dotnet CLI.

  1. Microsoft.EntityFrameworkCore.Design
  2. Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

The first package is the main dependency that enables us to work with Postgres databases. It is a .NET Core port of the Npgsql package which is the third party data access provider for PostgreSQL for .NET. The second package is useful for the dotnet ef tools for code generation.

Once the package installs finish, open up the project file netcore-postgres-sample1.csproj and notice that two new package references are added.

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.2.1" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" />

Next, we need to configure the database context class, DbContext. In entity framework terms, database context is a representation of session with the database and is essentially the data access layer of your application.

Create a C# class called ProductsContext and derive from DbContext class.

namespace netcore_postgres_sample1
{
    public class ProductsContext : DbContext
    {
        public DbSet<Product> Products { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {
            builder.UseNpgsql(@"Server=localhost;Database=myproductsdb;Username=myproductsusr;Password=sEcur3P455w0rd");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {

        }
    }
}

In this class, we define the list of tables we want in our database using DbSet class. A DbSet<T> object is a table in the database which is mapped to the class T. Since we have one data model Product, we add a Products property of type DbSet<Product>. To skip the whole debate about pluralized table names, I would just say that a single object is product, but Products is a set of products objects or, more specifically, database rows of product type.

We need to override two methods from the DbContext class - OnConfiguring and OnModelCreating. The OnConfiguring method is used to configure the database itself and any context specific options. The OnModelCreating method is used to configure the model using the ModelBuilder API. This is one other way to configure the model apart from data annotations and conventions.

Now that we have the database context, ProductsContext created, we can move onto creating our first migration. But what is a migration? Migration is a database agnostic representation of your data model(s) written in C#. Thankfully, we do not have to write a migration ourselves manually. The dotnet CLI tool comes with a special command called ef that handles creating migrations from data model.

With that knowledge, let us create our first migration with the name Initial.

dotnet ef migrations add Initial

This creates a few C# files in a new folder Migrations. Examine the file that has the name in the format 20181127183840_Initial.cs.

migrationBuilder.CreateTable(
    name: "Products",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
        Name = table.Column<string>(nullable: true),
        Description = table.Column<string>(nullable: true),
        PurchaseDate = table.Column<DateTime>(nullable: false),
        WarrantyDate = table.Column<DateTime>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Products", x => x.Id);
    });

As you see, this is a migration for our products class with all its properties and constraints. Some interesting things to note:

  1. Id column is set to SerialColumn type which is a mapping to the postgres serial type unlike IdentityColumn which is used in SQL Server.
  2. Name column is set as nullable automatically though we did not specify it to be a required field. C# types which can have null as a value are by default taken as nullable: true. This is an EF Core thing rather than postgres.

Once we have the migration files, the next step is to apply the migrations to the database. Let us now proceed to creating the database.

dotnet ef database update

We get the following error.

Npgsql.PostgresException (0x80004005): 42501: permission denied to create database

I have skimmed a lot of resources on multiple days and have not yet found a conclusive answer on why this fails. With SQL Server, there would not be a problem as the database is created when this command is run, based on the connection string. Based on my understanding so far, at this moment, it is not possible to create a database directly. It is also not possible to use HasData method to provide seed data in the OnModelCreating Method. However, I would love to be proven wrong. I am not saying it is impossible, but rather I couldn't gather enough information to understand that this is possible.

The other way to proceed to create the database manually.

Setup database

If you do not have postgres database server installed, let us start with installing Postgres database. Run the following command on the terminal.

sudo apt-get install postgresql postgresql-client

At this moment, we only need to install the postgres database server and client.

If you do have postgres database server installed, let us connect to the database using psql. To do so, we need to run the following command. Here, we are logging in as user postgres to be able to access the psql tool.

sudo -u postgres psql

Let us next create our database. To do this, run the following command in psql.

postgres=# CREATE DATABASE myproductsdb;
CREATE DATABASE

In psql, when the command finishes running, it will output the name of the command. In this case, it will output CREATE DATABASE. If you miss the semicolon at the end, nothing happens. This is because the command is only executed when it is ends with a semicolon.

This creates a database called myproductsdb on the database server. Let us associate a user to this myproductsdb. To do this, we need to create a user and grant it all privileges on our database. Run the following commands in psql.

postgres=# CREATE USER myproductsusr WITH PASSWORD 'sEcur3P455w0rd';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE myproductsdb TO myproductsusr;
GRANT

Let us try the to run the dotnet ef database update command again.

$ dotnet ef database update
Applying migration '20181127183840_Initial'.

This means that the database we created myproductsdb, is found and the migration Initial is applied to it. We can verify this in psql by connecting to this database and using dt (display tables) command.

postgres=# \c myproductsdb
myproductsdb=# \dt

                   List of relations
 Schema |         Name          | Type  |     Owner     
--------+-----------------------+-------+---------------
 public | Products              | table | myproductsusr
 public | __EFMigrationsHistory | table | myproductsusr

All good. Now let us check the Products table.

myproductsdb-# \d Products
Did not find any relation named "Products".

What happened to our Products table? Why did it show up when we ran the \dt command and not when specifically requesting the table itself? In the world of Postgres, table and column names are case sensitive! When you type \d and press space and then press tab, it shows the following output. Note the space after \d.

myproductsdb-# \d 

"__EFMigrationsHistory"  pg_catalog.              pg_toast.                "Products"               
information_schema.      pg_temp_1.               pg_toast_temp_1.         public.

Note the Products table being surrounded with double quotes. To get around this problem, you could specify the double quotes in the previous command and everything is good again. Well, almost.

myproductsdb-# \d "Products"

                                          Table "public.Products"
    Column    |            Type             | Collation | Nullable |                Default                 
--------------+-----------------------------+-----------+----------+----------------------------------------
 Id           | integer                     |           | not null | nextval('"Products_Id_seq"'::regclass)
 Name         | text                        |           |          | 
 Description  | text                        |           |          | 
 PurchaseDate | timestamp without time zone |           | not null | 
 WarrantyDate | timestamp without time zone |           | not null | 
Indexes:
    "PK_Products" PRIMARY KEY, btree ("Id")

Note the constraints and field names also follow casing as per the data model. Uh oh! Hope you understood where this might create a problem. You would have qualify every single thing about the table with double quotes. To view the "Products" table data, you would have to write the query like this. For the table name, you would have to specify the default schema. Writing queries with double quotes will be a pain and embedding them in the C# code will be an even bigger pain.

myproductsdb-# select * from public."Products";

 Id | Name | Description | PurchaseDate | WarrantyDate 
----+------+-------------+--------------+--------------
(0 rows)

myproductsdb-# select "Id","Name" from public."Products";

 Id | Name 
----+------
(0 rows)

To get around this, we would need to either change the properties in the data model(s) or provide some sort of a mapping between the data models and the actual tables and fields. Andrew Lock, author of the ASP.NET Core in Action book, has a great way to do this. He prescribes mapping all types of database objects to their snake case equivalents in the OnModelCreating method.

protected override void OnModelCreating(ModelBuilder builder)
{
    foreach(var entity in builder.Model.GetEntityTypes()) 
    {
        entity.Relational().TableName = entity.Relational().TableName.ToSnakeCase();

        foreach(var property in entity.GetProperties()) 
            property.Relational().ColumnName = property.Name.ToSnakeCase();

        foreach(var key in entity.GetKeys()) 
            key.Relational().Name = key.Relational().Name.ToSnakeCase();

        foreach(var key in entity.GetForeignKeys())
            key.Relational().Name = key.Relational().Name.ToSnakeCase();

        foreach(var index in entity.GetIndexes())
            index.Relational().Name = index.Relational().Name.ToSnakeCase();
    }
}

ToSnakeCase() is an string extension method which changes the word Id to id and WarrantyDate to warranty_date.

public static string ToSnakeCase(this string input)
{
    if (string.IsNullOrEmpty(input)) { return input; }
    var startUnderscores = Regex.Match(input, @"^_+");
    return startUnderscores + Regex.Replace(input, @"([a-z0-9])([A-Z])", "$1_$2").ToLower();
}

Before running the migration and database update commands again, we need to reset the database.

$ dotnet ef database update 0
Reverting migration '20181127183840_Initial'.
Done.
$ dotnet ef migrations remove
Removing migration '20181127183840_Initial'.
Removing model snapshot.
Done.

Now let us do over the migrations and database update.

$ dotnet ef migrations add Initial
Done. To undo this action, use 'ef migrations remove'
$ dotnet ef database update
Applying migration '20181127193840_Initial'.
Done.

Now if you check the migration file, you will notice that the table, column and constraint names are all lowercase or snake case.

migrationBuilder.CreateTable(
    name: "products",
    columns: table => new
    {
        id = table.Column<int>(nullable: false)
            .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
        name = table.Column<string>(nullable: true),
        description = table.Column<string>(nullable: true),
        purchase_date = table.Column<DateTime>(nullable: false),
        warranty_date = table.Column<DateTime>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("pk_products", x => x.id);
    });

Now that the database is created, let us get started with some data. Create a csv file like this.

name,description,purchase_date,warranty_date
Asus Zenbook,windows laptop,2014-10-10,2015-10-10
Lenovo Vibe K5 Note,android phone,2017-01-31,2017-07-31
Jio Phone,feature phone,2018/12/25,2021/12/25
ThinkPad X220,laptop,2016/10/16,2016/10/16

Run the following copy command to fill the products table with the csv file.

myproductsdb=# \copy products(name,description,purchase_date,warranty_date) FROM '/home/animesh/data/myproducts.csv' DELIMITER ',' CSV HEADER;
COPY 4

Check the data.

myproductsdb=# select * from products;
 id |        name         |  description   |    purchase_date    |    warranty_date    
----+---------------------+----------------+---------------------+---------------------
  1 | Asus Zenbook        | windows laptop | 2014-10-10 00:00:00 | 2015-10-10 00:00:00
  2 | Lenovo Vibe K5 Note | android phone  | 2017-01-31 00:00:00 | 2017-07-31 00:00:00
  3 | Jio Phone           | feature phone  | 2018-12-25 00:00:00 | 2021-12-25 00:00:00
  4 | ThinkPad X220       | laptop         | 2016-10-16 00:00:00 | 2016-10-16 00:00:00
(4 rows)

Note that you did not have to add any double quotes. Now let us complete the application.

Read and modify data

We need to refer to the ProductsContext to read and update the data. Let us do that in the Program.cs file.

namespace netcore_postgres_sample1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new ProductsContext())
            {
                var products = context.Products
                    .OrderBy(p => p.PurchaseDate)
                    .ToList();

                foreach (var item in products)
                {
                    Console.WriteLine($"{item.Name}");
                    Console.WriteLine($"Bought on {item.PurchaseDate}");

                    if (item.WarrantyDate > DateTime.Now)
                        Console.WriteLine($@"Warranty expires in {GetTimeRemaining(item.WarrantyDate)}");
                    else
                        Console.WriteLine("Warranty expired");

                    Console.WriteLine();
                }
            }
        }
    }
}

Now let us run the program and check the output.

$ dotnet run
Asus Zenbook
Bought on 10/10/14 12:00:00 AM
Warranty expired

ThinkPad X220
Bought on 10/16/16 12:00:00 AM
Warranty expired

Lenovo Vibe K5 Note
Bought on 1/31/17 12:00:00 AM
Warranty expired

Jio Phone
Bought on 12/25/18 12:00:00 AM
Warranty expires in 1059d

To add a product to the database, we could use the context again. For example, like this:

Product product = new Product
{
    Name = "Travel Suitcase",
    Description = "Luggage",
    PurchaseDate = new DateTime(2016, 9, 4),
    WarrantyDate = new DateTime(2022, 9, 4)
};

context.Products.Add(product);
context.SaveChanges();

Here is a naive GetTimeRemaining method to calculate the relative time remaining.

private static string GetTimeRemaining(DateTime warrantyDate)
{
    var time = warrantyDate - DateTime.Now;
    string output = String.Empty;

    if (time.Days > 0)
        output += time.Days + "d";

    if ((time.Days == 0 || time.Days == 1) && time.Hours > 0)
        output += time.Hours + "h";

    if (time.Days == 0 && time.Minutes > 0)
        output += time.Minutes + "min";

    if (output.Length == 0)
        output += time.Seconds + "s";

    return output;
}

All the code for this post is available on my gitlab repository.