How to Connect PostgreSQL Database with Entity Framework Core in .NET 6

In this tutorial, you will learn how to connect a PostgreSQL database to your .NET 6 application using Entity Framework Core and how you can apply migrations to the database using EF Core migrations.

Using EF Core to create/update migrations on the database makes it easier to deploy it with containers, etc... Imagine having a dockerized .NET solution that automatically configures the database upon deployment of the stack.

I have prepared a .NET 6 CRUD API that you can download from my GitHub account. Let us update that in order for it to connect to the PostgreSQL database and run the EF Core Migrations, so you can make your application fully code-first and sit back while you deploy the solution. I will not go in-depth on how the PostgreSQL database works - only show you how to start one using Docker and connect to it.

GitHub - Christian-Schou/PostgreSQL.Demo.API: Connect PostgreSQL with .NET 6 Web API using EF Core
Connect PostgreSQL with .NET 6 Web API using EF Core - GitHub - Christian-Schou/PostgreSQL.Demo.API: Connect PostgreSQL with .NET 6 Web API using EF Core

Requirements

To keep up with this tutorial you will need the following tools installed on your computer.

  • .NET SDK - This includes both the CLI (Command Line Tools), .NET Runtime and libraries, and the dotnet driver.
  • Visual Studio Code or IDE. Both these code editors are capable of running on Linux, macOS, and Windows. You can also use Rider or any other code editor you prefer.
  • If you decide to use Visual Studio Code, you would need to install the C# Extension by Microsoft.
  • Docker or native installation of PostgreSQL - Docker will allow you to accelerate how you build, share, and run modern applications. In this tutorial, I will use Docker Compose to deploy two containers. The first one will be PostgreSQL and the second one will be an admin interface for PostgreSQL.
What is Docker? Docker beginner guide - Christian Schou
Get started with Docker and learn how to create your first container running Nginx and serve your own webpage written in HTML. Docker is a very powerful tool to run applications without having to install a lot of dependencies.

Create PostgreSQL database + PgAdmin4 with Docker Compose

For this project, I will be using a simple docker-compose file for configuring and running a PostgreSQL Database along with an Admin UI for PostgreSQL. I have prepared a compose file you can copy and paste below - it is also available in the project repository, and at the reference bookmark below:

version: '3.5'

services:
  postgres:
    container_name: postgres_db_container
    image: postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped
  
  pgadmin:
    container_name: pgadmin_db_container
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:[email protected]}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
       - pgadmin:/var/lib/pgadmin

    ports:
      - "${PGADMIN_PORT:-5050}:80"
    networks:
      - postgres
    restart: unless-stopped

networks:
  postgres:
    driver: bridge

volumes:
    postgres:
    pgadmin:

This Compose file contains the following environment variables:

  • POSTGRES_USER the default value is postgres
  • POSTGRES_PASSWORD the default value is changeme
  • PGADMIN_PORT the default value is 5050
  • PGADMIN_DEFAULT_EMAIL the default value is [email protected]
  • PGADMIN_DEFAULT_PASSWORD the default value is admin

Access PostgreSQL Database

  • localhost:5432
  • Username: postgres (as a default)
  • Password: changeme (as a default)

Access PgAdmin

  • URL: http://localhost:5050
  • Username: [email protected] (default)
  • Password: admin (default)

Add Server in PgAdmin

  • Host name/address postgres (service name)
  • Port 5432
  • Username as POSTGRES_USER, by default: postgres
  • Password as POSTGRES_PASSWORD, by default changeme
GitHub - khezen/compose-postgres: Postgresql & pgadmin4 powered by compose
Postgresql & pgadmin4 powered by compose. Contribute to khezen/compose-postgres development by creating an account on GitHub.

Clone and run the .NET 6 CRUD Web API

I have created a simple .NET 6 Web API with a CRUD repository implementation for this tutorial. By the end of this tutorial, we will have a fully implemented database context communicating with PostgreSQL.

You can either apply the code in your own application or you can clone my project and continue developing that along with me. It is available at the link below.

Created CRUD API · Christian-Schou/[email protected]
Connect PostgreSQL with .NET 6 Web API using EF Core - Created CRUD API · Christian-Schou/[email protected]

Inside this project I have used the following technologies:

swagger, connect postgresql to .net, crud
PostgreSQL CRUD API - Swagger

Add PostgreSQL support in .NET 6 Web API

Let's move on to the fun part and implement support for communicating with a PostgreSQL database using EF Core and .NET.

Add PostgreSQL database provider package using NuGet

Open up the Package Manager Console from the root of your project where you have the database context stored. Then enter the command below to install the EF Core database provider for PostgreSQL using the NuGet Package Manager.

Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

Add a connection string to appsettings.json

Now open up appsettings.json and add the following code to define a new connection string entry in your application. You can set the name of the connection string to what you prefer. Then enter the connection string in this format: User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;

PostgreSQL connection strings - ConnectionStrings.com
Connection strings for PostgreSQL. Connect using Devarts PgSqlConnection, PgOleDb, OleDbConnection, psqlODBC, NpgsqlConnection and ODBC .NET Provider.

When we are running the migrations using Entity Framework Core, the values in our code at runtime will be replaced by the values defined in our connection string.

Your updated appsettings.json file should look like this if you use my CRUD template.

{
  "ConnectionStrings": {
    "PostgreSQL": "User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

It might look different if you have other configurations inside it. I always place my connection strings at the beginning of this file, if I do not configure it in a separate configuration file.

Change Database context to use PostgreSQL

In the CRUD Web API, the DataContext class is named LibraryContext and is located at /Data/LibraryContext.cs. I have named it LibraryContext since the CRUD is about authors and books. In .NET we use this file for accessing the data through Entity Framework.

As you can see in the code we derive from the Entity Framework DbContext class and got two public properties for accessing and managing authors and books.

To use PostgreSQL we have to update the OnConfiguring() method to PostgreSQL instead of the current configured in-memory database. This is done by replacing optionsBuilder.UseInMemoryDatabase("LibraryDatabase"); with

If you have done it correctly, you should have a LibraryContext class that looks like this:

using Microsoft.EntityFrameworkCore;
using PostgreSQL.Demo.API.Entities;

namespace PostgreSQL.Demo.API.Data
{
    public class LibraryContext : DbContext
    {
        protected readonly IConfiguration _configuration;

        public LibraryContext(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public DbSet<Author> Authors { get; set; }
        public DbSet<Book> Books { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql(_configuration.GetConnectionString("PostgreSQL"));
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Author>()
                .HasMany(b => b.Books)
                .WithOne(a => a.Author)
                .OnDelete(DeleteBehavior.Cascade);
        }
    }
}

As you also might have noticed, we got a relationship defined in the OnModelCreating() method. This is defining a one-to-many relationship using Entity Framework.

Create PostgreSQL Database using Code-First approach with Entity Framework Migrations

Now that we have connected the PostgreSQL database to our .NET 6 Web API, let's make our lives a bit easier and apply automatic database creation using Entity Framework Core.

Install required tools globally

To create new migrations for our Web API, we can utilize the EF Core Tools to make the heavy lifting for us. You must install the EF Core tools globally on your computer using the following command in your PowerShell Terminal.

dotnet tool install -g dotnet-ef

You can read more about the EF Core tools here.

EF Core tools reference (.NET CLI) - EF Core
Reference guide for the Entity Framework Core .NET Core CLI tools

Add EF Core Design Library/Package to Web API

Inside your Package Manager Console, you can run the command below to install the EF Core Design library into your .NET application. This library gives you access to generate the EF Core Migrations, that will be applied to the database.

Install-Package Microsoft.EntityFrameworkCore.Design

Generate EF Core migrations manually

Generate new EF Core migration files by running the command dotnet ef migrations add Initial. You have to do it from the root of the project where the database context is located.

For this project, it is stored inside PostgreSQL.Demo.API. When you execute this command EF Core will create the migration that will contain the code to create the database and tables for your application.

Execute EF Core migrations manually

With the dotnet CLI we can update our database from the code (Code First Approach). All you have to do is open your Package Manager Console and select the project where your database context lives. Set your API as the startup project and then run this command in the terminal: dotnet ef database update and watch the magic happen.

Now open up PostgreSQL and see the database with the tables you have specified in the Database Context (DbSet<T>) and the _EFMigrationsHistory.

pgadmin, postgresql, ef core, migrations, postgresql with .net
PgAdmin showing DemoAPI Tables from Migration

As you can see we now got the tables from our Database Context (LibraryContext). This is a manual process and I like to automate things, but keep in mind that automation can be dangerous! Below is a full description of how you can make automatic migrations using the Entity Framework Core Database Context Service.

Execute database migrations automatically using the EF Core DB Context Service

It is possible to make the application do the migrations for us during the startup of the application. This is great when we run the application in our own development environment and during migrations tests.

I would not recommend you to do it when it comes to your production database unless you really know what you are doing, because of the following points I have stated below.
  • Suppose you got multiple instances of your applications running. In that case, both applications could attempt to apply the migrations concurrently and that would in most cases fail and in some cases lead to data corruption.
  • Imagine having an application running using the old version of the database and then upgrading the database to a new version. That could lead to severe issues.
  • You need to assign elevated access for the application to modify the database schema. I would not recommend allowing an application to have this permission in a production environment.
  • If you use the manual strategy where you use the dotnet CLI to upgrade the database you are able to roll back an applied migration to the database in case something should break.
  • If the application is running the migration update, you will not see any issues and be able to inspect them. In a production environment, this is in my opinion very dangerous.

Due to the reasons above, I will not include code to run automatic migrations in this project as I like to run my migrations manually in order to have full control of what is going on.

Update Repository and Controllers to work with PostgreSQL

At the moment we are returning void when creating and updating authors and books in the application. This will result in an exception from Entity Framework Core like this: Invalid attempt to call ReadAsync when reader is closed. The problem is that we are Y and that needs to be fixed by firing the triggers async.

Luckily for us, it is easy to fix. We only need to replace void with Task as return type and change the methods to be awaitable (async methods).

The updated AuthorService looks as follows:

using AutoMapper;
using Microsoft.EntityFrameworkCore;
using PostgreSQL.Demo.API.Data;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Helpers;
using PostgreSQL.Demo.API.Models.Authors;

namespace PostgreSQL.Demo.API.Services
{
    public interface IAuthorService
    {
        /// <summary>
        /// Get all authors in database. Set includeBooks to true if you want to include all books made by the author.
        /// </summary>
        /// <param name="includeBooks">Optional parameter to include books</param>
        /// <returns>All authors in database</returns>
        Task<IEnumerable<Author>> GetAllAuthorsAsync(bool includeBooks = false);

        /// <summary>
        /// Get a single author by Id and include books if requested by the includeBooks boolean.
        /// </summary>
        /// <param name="id">Id of Author</param>
        /// <param name="includeBooks">Optional parameter to include books</param>
        /// <returns>A single author</returns>
        Task<Author> GetAuthorByIdAsync(int id, bool includeBooks = false);

        /// <summary>
        /// Create a new author in the database
        /// </summary>
        /// <param name="model">Create Author request model</param>
        Task<int> CreateAuthor(CreateAuthorRequest model);

        /// <summary>
        /// Update an author in the database if the author already exists.
        /// </summary>
        /// <param name="id"></param>
        /// <param name="model"></param>
        Task UpdateAuthor(int id, UpdateAuthorRequest model);

        /// <summary>
        /// Delete a single author in the dabase. Will delete the author if the author exists in the database.
        /// Cascading is enabled and will delete the authors books from the database at the same time. Use with caution.
        /// </summary>
        /// <param name="id">Id of the author to delete</param>
        Task DeleteAuthor(int id);
    }

    public class AuthorService : IAuthorService
    {
        private LibraryContext _dbContext;
        private readonly IMapper _mapper;

        public AuthorService(LibraryContext dbContext, IMapper mapper)
        {
            _dbContext = dbContext;
            _mapper = mapper;
        }

        public async Task<int> CreateAuthor(CreateAuthorRequest model)
        {
            // Validate new author
            if (await _dbContext.Authors.AnyAsync(x => x.Name == model.Name))
                throw new RepositoryException($"An author with the name {model.Name} already exists.");

            // Map model to new author object
            Author author = _mapper.Map<Author>(model);

            // Save Author
            _dbContext.Authors.Add(author);
            await _dbContext.SaveChangesAsync().ConfigureAwait(true);

            if (author != null)
            {
                return author.Id; // Author got created
            }

            return 0;
            
        }

        public async Task DeleteAuthor(int id)
        {
            Author? author = await _getAuthorById(id);

            _dbContext.Authors.Remove(author); // Delete the author and books (Cascading is enabled)
            await _dbContext.SaveChangesAsync().ConfigureAwait(true);
        }

        public async Task<IEnumerable<Author>> GetAllAuthorsAsync(bool includeBooks = false)
        {
            if (includeBooks)
            {
                // Get all authors and their books
                return await _dbContext.Authors
                    .Include(b => b.Books)
                    .ToListAsync().ConfigureAwait(true);
            }
            else
            {
                // Get all authors without including the books
                return await _dbContext.Authors
                    .ToListAsync().ConfigureAwait(true);
            }
        }

        public async Task<Author> GetAuthorByIdAsync(int id, bool includeBooks = false)
        {
            return await _getAuthorById(id, includeBooks).ConfigureAwait(true);
        }

        public async Task UpdateAuthor(int id, UpdateAuthorRequest model)
        {
            Author? author = await _getAuthorById(id).ConfigureAwait(true);

            // Validation
            if (model.Name != author.Name && await _dbContext.Authors.AnyAsync(x => x.Name == model.Name))
                throw new RepositoryException($"An author with the name {model.Name} already exists.");

            // copy model to author and save
            _mapper.Map(model, author);
            _dbContext.Authors.Update(author);
            await _dbContext.SaveChangesAsync();

        }

        /// <summary>
        /// Get a single author and the books if requested. Looks in the database for an author and returns null, if the author did not exist.
        /// </summary>
        /// <param name="id">Author ID</param>
        /// <param name="includeBooks">True to include books</param>
        /// <returns>A single author</returns>
        private async Task<Author> _getAuthorById(int id, bool includeBooks = false)
        {
            if (includeBooks)
            {
                Author? author = await _dbContext.Authors
                    .AsNoTracking()
                    .Where(x => x.Id == id)
                    .Include(b => b.Books)
                    .FirstOrDefaultAsync().ConfigureAwait(true);

                if (author == null)
                {
                    throw new KeyNotFoundException("Author not found");
                }

                return author;
            }
            else
            {
                Author? author = await _dbContext.Authors
                    .AsNoTracking()
                    .Where(x => x.Id == id)
                    .FirstOrDefaultAsync().ConfigureAwait(true);

                if (author == null)
                {
                    throw new KeyNotFoundException("Author not found");
                }

                return author;
            }
        }
    }
}

The updated AuthorController looks as follows:

using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Models.Authors;
using PostgreSQL.Demo.API.Services;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace PostgreSQL.Demo.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        private IAuthorService _authorService;
        private IMapper _mapper;

        public AuthorController(IAuthorService authorService, IMapper mapper)
        {
            _authorService = authorService;
            _mapper = mapper;
        }

        // GET: api/<AuthorController>
        [HttpGet]
        public async Task<IActionResult> GetAllAuthors()
        {
            IEnumerable<Author> authors = await _authorService.GetAllAuthorsAsync();
            return Ok(authors);
        }

        // GET api/<AuthorController>/5
        [HttpGet("{id}")]
        public async Task<IActionResult> GetAuthorById(int id, bool includeBooks = false)
        {
            Author author = await _authorService.GetAuthorByIdAsync(id, includeBooks);
            return Ok(author);
        }

        // POST api/<AuthorController>
        [HttpPost]
        public async Task<IActionResult> CreateAuthor(CreateAuthorRequest model)
        {
            int authorId = await _authorService.CreateAuthor(model);

            if (authorId != 0)
            {
                return Ok(new { message = $"Author was successfully created in database with the id {authorId}" });
            }

            return StatusCode(StatusCodes.Status500InternalServerError, "The author was not created in the database.");
        }

        // PUT api/<AuthorController>/5
        [HttpPut("{id}")]
        public async Task<IActionResult> UpdateAuthor(int id, UpdateAuthorRequest model)
        {
            await _authorService.UpdateAuthor(id, model);
            return Ok(new { message = "Author was successfully updated in database" });
            
        }

        // DELETE api/<AuthorController>/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteAuthor(int id)
        {
            await _authorService.DeleteAuthor(id);
            return Ok(new { message = "Author was successfully deleted in database" });

        }
    }
}

The updated BookService looks as follows:

using AutoMapper;
using Microsoft.EntityFrameworkCore;
using PostgreSQL.Demo.API.Data;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Helpers;
using PostgreSQL.Demo.API.Models.Books;

namespace PostgreSQL.Demo.API.Services
{
    public interface IBookService
    {
        /// <summary>
        /// Get all book in database.
        /// </summary>
        /// <returns>All books in database</returns>
        Task<IEnumerable<Book>> GetAllBooksAsync();

        /// <summary>
        /// Get a single book by Id
        /// </summary>
        /// <param name="id">Id of book</param>
        /// <returns>A single book</returns>
        Task<Book> GetBookByIdAsync(int id);

        /// <summary>
        /// Create a new book in the database
        /// </summary>
        /// <param name="model">Create book request model</param>
        Task<int> CreateBook(CreateBookRequest model);

        /// <summary>
        /// Update a book in the database if the book already exists.
        /// </summary>
        /// <param name="id"></param>
        /// <param name="model"></param>
        Task UpdateBook(int id, UpdateBookRequest model);

        /// <summary>
        /// Delete a single book in the dabase. Will delete the book if the book exists in the database.
        /// </summary>
        /// <param name="id">Id of the book to delete</param>
        Task DeleteBook(int id);
    }

    public class BookService : IBookService
    {
        private LibraryContext _dbContext;
        private readonly IMapper _mapper;

        public BookService(LibraryContext dbContext, IMapper mapper)
        {
            _dbContext = dbContext;
            _mapper = mapper;   
        }

        public async Task<int> CreateBook(CreateBookRequest model)
        {
            // Validate new book
            if (await _dbContext.Books.AnyAsync(x => x.ISBN13 == model.ISBN13))
                throw new RepositoryException($"A book with the ISBN {model.ISBN13} already exist in the database");

            // Map model to new book object
            Book book = _mapper.Map<Book>(model);

            // Save book in database
            _dbContext.Books.Add(book);
            await _dbContext.SaveChangesAsync().ConfigureAwait(true);

            return book.Id;
        }

        public async Task DeleteBook(int id)
        {
            Book? book = await _getBookById(id);

            _dbContext.Books.Remove(book);
            await _dbContext.SaveChangesAsync().ConfigureAwait(true);
        }

        public async Task<IEnumerable<Book>> GetAllBooksAsync()
        {
            return await _dbContext.Books
                .ToListAsync()
                .ConfigureAwait(true);
        }

        public async Task<Book> GetBookByIdAsync(int id)
        {
            return await _getBookById(id);
        }

        public async Task UpdateBook(int id, UpdateBookRequest model)
        {
            Book? book = await _getBookById(id);

            // Validate the book
            if (model.ISBN13 != book.ISBN13 && await _dbContext.Books.AnyAsync(x => x.ISBN13 == model.ISBN13))
                throw new RepositoryException($"A book with the ISBN number {model.ISBN13} already exist in the database.");

            // Copy model data to book object and save it in the database
            _mapper.Map(model, book);
            _dbContext.Books.Update(book);
            await _dbContext.SaveChangesAsync().ConfigureAwait(true);
        }

        private async Task<Book> _getBookById(int id)
        {
            Book? book = await _dbContext.Books
                .AsNoTracking()
                .Where(x => x.Id == id)
                .FirstOrDefaultAsync().ConfigureAwait(true);

            if (book == null)
            {
                throw new KeyNotFoundException("Book was not found in database");
            }

            return book;
        }
    }
}

The updated BooksController looks as follows:

using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using PostgreSQL.Demo.API.Entities;
using PostgreSQL.Demo.API.Models.Books;
using PostgreSQL.Demo.API.Services;

namespace PostgreSQL.Demo.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BooksController : ControllerBase
    {
        private readonly IBookService _bookService;
        private IMapper _mapper;

        public BooksController(IBookService bookService, IMapper mapper)
        {
            _bookService = bookService;
            _mapper = mapper;
        }

        // GET: api/<BooksController>
        [HttpGet]
        public async Task<IActionResult> GetAllBooks()
        {
            IEnumerable<Book> books = await _bookService.GetAllBooksAsync();
            return Ok(books);
        }

        // GET api/<BooksController>/5
        [HttpGet("{id}")]
        public async Task<IActionResult> GetBookById(int id)
        {
            Book book = await _bookService.GetBookByIdAsync(id);
            return Ok(book);
        }

        // POST api/<BooksController>
        [HttpPost]
        public async Task<IActionResult> CreateBook(CreateBookRequest model)
        {
            int book = await _bookService.CreateBook(model);

            if (book != 0)
            {
                return Ok("The book was successfully added to the database");
            }

            return StatusCode(StatusCodes.Status500InternalServerError, "The book was successfully added to the database");
        }

        // PUT api/<BooksController>/5
        [HttpPut("{id}")]
        public async Task<IActionResult> UpdateBook(int id, UpdateBookRequest model)
        {
            await _bookService.UpdateBook(id, model);
            return Ok("The book was successfully updated in the database");
        }

        // DELETE api/<BooksController>/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteBook(int id)
        {
            await _bookService.DeleteBook(id);
            return Ok("The book was successfully deleted in the database");
        }
    }
}

You can see all changes in the commit details below:

Fixed ReadAsync issue for database · Christian-Schou/[email protected]
Fixed an issue with accessing the Database Context concurrently using different threads by implementing async methods returning a Task instead of void.

Fix Date and Time Handling for PostgreSQL

Npgsql 6.0 introduced some important changes to how timestamps are mapped. I have added a Switch to enable the legacy timestamp behavior in order to store values in the database without receiving an error at creation and update of entities in the database.

Open Program.cs and add the following line, just below your .AddDbContext<T>(); method:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

In the Program.cs file it would look like this:

using PostgreSQL.Demo.API.Data;
using PostgreSQL.Demo.API.Middleware;
using PostgreSQL.Demo.API.Services;
using System.Text.Json.Serialization;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<LibraryContext>();
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

builder.Services.AddCors();

...

You can see the full change at this commit:

Fix Date and Time Handling for PostgreSQL · Christian-Schou/[email protected]
Npgsql 6.0 introduced some important changes to how timestamps are mapped. I have added a Switch to enable the legacy timestamp behavior in order to store values in the database.

Time for test

The development of the API is done and we have connected it to the PostgreSQL database + created the database and updated it to be at the same level as our domain model in the application.

I have decided to make this as a video, as it would end up in a lot of sections showing test results of each endpoint in the API.

As you can see it works like a charm. A fully connected and ready to use .NET Web API connected to PostgreSQL with CRUD functionality using AutoMapper.

Summary

PostgreSQL is very easy to integrate with .NET. You can either run PostgreSQL on a separate server, in a container, or on your development machine (as I showed in the video above).

The final code is available at my Github profile if you would like to fork it or just need some part of the code for your next project.

GitHub - Christian-Schou/PostgreSQL.Demo.API: Connect PostgreSQL with .NET 6 Web API using EF Core
Connect PostgreSQL with .NET 6 Web API using EF Core - GitHub - Christian-Schou/PostgreSQL.Demo.API: Connect PostgreSQL with .NET 6 Web API using EF Core

I hope you learned something new from this tutorial about connecting PostgreSQL to .NET. If you got any questions, please let me know in the comments below. If you liked the tutorial and wann help me spread the word about TWC, then please help me share the article.

You've successfully subscribed to Tech with Christian
Great! Next, complete checkout to get full access to all premium content.
Error! Could not sign up. invalid link.
Welcome back! You've successfully signed in.
Error! Could not sign in. Please try again.
Success! Your account is fully activated, you now have access to all content.
Error! Stripe checkout failed.
Success! Your billing info is updated.
Error! Billing info update failed.