This blog demonstrates connecting a C#.NET console application on an IBM Z machine running Ubuntu 20.04LTS with a PostgreSQL server hosted locally to perform CRUD operations.

The application uses the Npgsql.EntityFrameworkCore.PostgreSQL driver, published by Npgsql under PostgreSQL license and Microsoft.EntityFrameworkCore.Design library to perform database migration.

Prerequisites

• A partition running Ubuntu 20.04 or later on an IBM Z machine.

• .NET6 or a later version of the framework with s390x architecture support.

Installing PostgreSQL Server

Set up the PostgreSQL database engine.

• Install PostgreSQL packages

  # Install PostgreSQL server packages
  $ sudo apt install postgresql postgresql-contrib

• Start the PostgreSQL Server.

  # Initialize and start the PostgeSQL server 
  $ pg_ctlcluster 12 main start  

• Verify that the PostgreSQL server is running. 

  $ systemctl status postgresql

• which should produce an output similar to: 

  ● postgresql.service - PostgreSQL RDBMS
       Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
       Active: active (exited) since Sat 2023-07-01 19:30:06 UTC; 2min 45s ago
     Main PID: 55959 (code=exited, status=0/SUCCESS)
        Tasks: 0 (limit: 4762)
       Memory: 0B
       CGroup: /system.slice/postgresql.service

• To configure the postgres user with a password:

  # Change user to postgres
  $ su - postgres 
  
  postgres$ psql -c "ALTER USER postgres WITH PASSWORD '<custom_password>';"
  
  # Exit from postgres 
  postgres$ exit;

• To connect to the server through the psql client or a console application the authentication method should be configured to md5.

• Locate the pg_hba.conf

  $ find / -name pg_hba.conf
  /etc/postgresql/12/main/pg_hba.conf

• Open the conf file in text editor and change the authentication method from peer to md5.

• Change the following lines

  # IPv4 local connections:
  host    all             all             127.0.0.1/32            md5
  # IPv6 local connections:
  host    all             all             ::1/128                 md5

• Restart the Postgres service to apply the recent changes in the configuration.

  $ systemctl reload postgresql

• Connect to the database server and create a database.

  $ sudo -u postgres psql
  psql (12.15 (Ubuntu 12.15-0ubuntu0.20.04))
  Type "help" for help.
  
  postgres=# create database <your_database_name>;
  CREATE DATABASE

Install Dotnet Framework

Cross build and install dotnet framework and install in the Z machine.

• Verify that dotnet is installed correctly.

  $ dotnet --info

• The output of the command should produce result as below.

  .NET SDK:
   Version:   7.0.100-rc.1.22431.12
   Commit:    f1cf61e1c0
  
  Runtime Environment:
   OS Name:     ubuntu
   OS Version:  20.04
   OS Platform: Linux
   RID:         linux-s390x
   Base Path:   /root/dotnet/sdk/7.0.100-rc.1.22431.12/
  
  Host:
    Version:      7.0.0-rc.1.22426.10
    Architecture: s390x
    Commit:       06aceb7015
  
  .NET SDKs installed:
    7.0.100-rc.1.22431.12 [/root/dotnet/sdk]
  
  .NET runtimes installed:
    Microsoft.AspNetCore.App 7.0.0-rc.1.22427.2 [/root/dotnet/shared/Microsoft.AspNetCore.App]
    Microsoft.NETCore.App 7.0.0-rc.1.22426.10 [/root/dotnet/shared/Microsoft.NETCore.App]

Performing Database Migration and CRUD Operations through C#.NET

1. Create a console program that connects to the PostgreSQL database and performs CRUD operations.

   $ dotnet new console -o EFPostrgreSQL 
   $ cd EFPostrgreSQL

2. Create the Model.cs file with the following contents.

• This includes all the models of tables in the database that are required in the program.

  using System.Collections.Generic;
  using Microsoft.EntityFrameworkCore;
  
  public class BloggingContext : DbContext
  {
      public DbSet<Blog> blogs { get; set; }
      public DbSet<Post> posts { get; set; }
  
      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
          => optionsBuilder.UseNpgsql("host=localhost;database=<your_database_name>;user id=postgres;password=<your_password>;");
  }
  
  public class Blog
  {
      public int blogid { get; set; }
      public string url { get; set; }
  
      public List<Post> Posts { get; set; }
  }
  
  public class Post
  {
      public int postid { get; set; }
      public string title { get; set; }
      public string content { get; set; }
  
      public int blogid { get; set; }
      public Blog Blog { get; set; }
  }

3. Edit the Program.cs file with the following contents to connect to the PostgreSQL database and perform CRUD operations.

   using System;
   using System.Linq;
   
   public class Program
   {
     public static void DumpDatabaseSnapshot(BloggingContext db)
     {
       // Read
       Console.WriteLine("\nDumping database snapshot...");
       var blogs = db.blogs
               .OrderBy(b => b.blogid)
               .ToList();
     
       Console.WriteLine("Number of records = {0}", blogs.Count);
     
       foreach(Blog b in blogs)
       {
         Console.WriteLine("BlogId = {0}, Url = {1}", b.blogid, b.url);
         if (b.Posts != null)
         {
           foreach(Post p in b.Posts)
           {
             Console.WriteLine("----->PostId = {0}, Title = {1}, Content = {2}", p.postid, p.title, p.content);
           }
         }
       }
     
       Console.WriteLine("\n");
     }
   
     public static void Main(String[] args)
     {
       using var db = new BloggingContext();
     
       // Create
       Console.WriteLine("Inserting a new blog");
       db.Add(new Blog { url = "http://blogs.msdn.com/adonet" });
       db.SaveChanges();
     
       DumpDatabaseSnapshot(db);
     
       var blog = db.blogs
               .OrderBy(b => b.blogid)
               .First();
     
       // Update
       Console.WriteLine("Updating the blog and adding a post, blogid = {0}", blog.blogid);
       blog.url = "https://devblogs.microsoft.com/dotnet";
       blog.Posts = new List<Post>();
       blog.Posts.Add(
       new Post { title = "Hello World", content = "I wrote an app using EF Core!" });
       db.blogs.Update(blog);
       db.SaveChanges();
     
       DumpDatabaseSnapshot(db);
     
       // Delete
       Console.WriteLine("Delete the blog");
       db.Remove(blog);
       db.SaveChanges();
     
       DumpDatabaseSnapshot(db);
     }
   }

4. Add the Entity Framework (EF) Core Npgsql and the EntityFrameworkCore.Design packages to the C# project file.

• The database provider allows EF Core to be used with PostgreSQL database.

  $ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
  $ dotnet add package "Microsoft.EntityFrameworkCore.Design

• • Once above commands are run, the contents of EFPostrgreSQL.csproj file should look as follows

    <Project Sdk="Microsoft.NET.Sdk">
    
      <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net7.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
      </PropertyGroup>
    
      <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.8">
          <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
          <PrivateAssets>all</PrivateAssets>
        </PackageReference>
        <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.4" />
      </ItemGroup>

5. Create the database schema using following commands. This will update the database to the state of last migration performed. See, EF Core tools reference (.NET CLI) - EF Core | Microsoft Learn for more information.

• Install the dotnet-ef tool inside the console.

  $ dotnet new tool-manifest
  $ dotnet tool install --local dotnet-ef

• • Create a database migration.

    $ dotnet dotnet ef migrations add InitialCreate
    Build started...
    Build succeeded.
    Done. To undo this action, use 'ef migrations remove'
    $ dotnet dotnet ef database update
    Build started...
    Build succeeded.
    Applying migration '20230701202313_InitialCreate'.
    Done.

The migration creates tables in the database and can be verified by: 

$ sudo -u postgres psql
psql (12.15 (Ubuntu 12.15-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \c <your_database_name>;
You are now connected to database "<your_database_name>" as user "postgres".

<your_database_name>=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner
--------+-----------------------+-------+----------
 public | __EFMigrationsHistory | table | postgres
 public | blogs                 | table | postgres
 public | posts                 | table | postgres
(3 rows)

Running the Application

$ dotnet run

The output of the program after it runs successfully is displayed below:

Inserting a new blog

Dumping database snapshot...
Number of records = 1
BlogId = 1, Url = http://blogs.msdn.com/adonet


Updating the blog and adding a post, blogid = 1

Dumping database snapshot...
Number of records = 1
BlogId = 1, Url = https://devblogs.microsoft.com/dotnet
----->PostId = 1, Title = Hello World, Content = I wrote an app using EF Core!


Delete the blog

Dumping database snapshot...
Number of records = 0

Conclusion

By implementing this functionality to connect from .NET applications on Linux on Z to a PostgreSQL database using EF Core, users will be able to modernise their apps.

References

For more information see:

• Overview of Entity Framework Core - EF Core | Microsoft Learn

• EF Core tools reference (.NET CLI) - EF Core | Microsoft Learn