Welcome to weblogs.com.pk Sign in | Join | Help

Dotnet Core :: PostgreSQL

postgresql Dotnet Core Series

Given, Dotnet Core can run on Linux; and in the series we have been exploring different aspects of having a Microservice based Containerized Dotnet Core application running on a Linux in Docker Containers. SQL Server has been the defacto database for .NET application, there even exists SQL Server for Linux (in a Public Preview form at the time of this post) and there is even an official image of SQL Server for Linux for Docker Engine that we can use; and connect our existing beloved SQL Tools to connect to it; but it needs 3.25GB memory and its an evaluation version.


db-pg-pgadminPostgreSQL is ACID compliant transactional object-relational database available for free on Windows, Linux and Mac. Its not as popular as mySQL; but it does provide serveral indexing functions, asynchronous commit, optimizer, synchronous and asynchronous replication that makes it technically more solid choice. Given its available for Windows; we can install it on the Windows development machines along with pgAdmin; the SQL Management Studio like client tool.

Entity Framework Core

Entity Framework Core is a cross platform data access technology for Dotnet Core. Its not EF 7 or EF6.x compatible; its developed from scratch and supports many database engines through Database Providers. Npgsql is an excellent .NET data provider for PostreSQL (Their GitHub Repositories) and it supports EF Core. All you need to do is install the Npgsql.EntityFrameworkCore.PostgreSQL NUGET package using the dotnet core CLI (dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL) It will bring along the EF Core and Npgsql libraries into your project.

We can now write our Entity classes and a DbContext class. For Npgsql, in OnConfiguring override, we will use UseNpgsql instead of UseSqlServer with DbContextOptionsBuilder passing on required PostgreSQL connection string. Here’s one entity class and context file I made for testing!

We can use the Context class with all the LINQ goodness; similar to SQL Server; for instance here’s the controller class

And here’s displaying the products in the View

  • If the entities are in different namespace; either import the namespace in the web.config file in the Views folder; or add the namespace in particular View by adding the using at the top @using ECommMvc.Models;

Entity Framework Core Command Line Tools

EF Core .NET Command Line Tools extends Dotnet CLI; and add ef commands to the dotnet (CLI) We need to add Microsoft.EntityFrameworkCore.Tools.Dotnet and Microsoft.EntityFrameworkCore.Design NUGET packages using dotnet add package NUGET; dotnet restore them and then add PackageReference using Design and DotnetCliToolReference using the Tools.Dotnet package; and you should end up having the dotnet ef commands in the project


Using the ef commands; we can add the Migration (dotnet ef migration add Migration-Name), remove it; update the database (dotnet ef database update) and more. Once we have the Migrations in place; we can continue to evolve our Entities and Database accordingly.


Using the Migrations; we can Seed our database as well; we can create a Migration naming Seed; and add the required seeding code in the migration’s CS file

When deploying into the Docker Container; we often need “side kick” container that “seeds” the cache or database (for details see Dockerizing PHP + MySQL Application Part 2); as when container is started we get the clean slate. Given the Migrations code become part of the MVC project; and in .NET Core; there is a Program.cs the entry point where Kestrel / MVC is initialized; we can add more code there as well. We can use the Context that’s in place and Migrations and update the database (that will initialize and seed)


Now with database work in place and Docker building techniques shown in previous posts (Redis Clients -- ASP.NET Core and Jenkins); we can have v2 Compose file or v3 Compose file (for Docker Swarm) and deploy our .NET Core MVC application that’s using Redis as Caching and PostgreSQL as Database into Docker Containers running on Linux node(s)

Project is available at https://github.com/khurram-aziz/HelloDotnetCore
Published Friday, March 31, 2017 2:08 PM by khurram


No Comments

New Comments to this post are disabled