PostgreSQL with EF
Tags: postgres, csharp, dotnet, docker
Published: 2021-01-01 18:10:04

Happy new year ladies and gentlemen. Today is the first day of the 2021 year and vacation day. I prepare new study topics for myself every year and plan to write projects. In this post, you will learn how to use PostgreSQL with entity framework code first. Also, you will learn how to get docker image PostgreSQL and install it. At the same time, you'll learn Entity Framework migrations after that write some basic queries in Visual Studio Code. Let's start.

**Important: Please install Visual Studio Code and .Net Core 3.1 or the latest version.**

# Create Project

Let's create a new Web API project. Here is the project structure. Also please install these packages.

![Screen Shot 2021-01-01 at 7.55.26 PM](https://dev-to-uploads.s3.amazonaws.com/i/ggttia99b18bbl53mlkn.png)

Also here is the appsettings.Development.json

```
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "Postgre": "UserID=postgres;Password=123;Server=localhost;Port=5432;Database=Community;Integrated Security=true;Pooling=true;"
  }
}

```
 
# Migrations

Create Initial Migration.

`dotnet ef migrations add InitialCreate -p Infrastructure/ -s API -o Data/Migrations`

API :
 
* Microsoft.EntityFrameworkCore.Design

Infrastructure: 

* Microsoft.EntityFrameworkCore.Design
* Microsoft.EntityFrameworkCore
* Microsoft.EntityFrameworkCore.Tools
* Npqsql.EntityFrameworkCore.PostgreSQL

Let's update the database. 

` dotnet ef database update -p Infrastructure/ -s API `

If you want to remove migration you can use this command.

` dotnet ef migrations remove -p Infrastructure -s API `

Open the terminal go to the project location

![Screen Shot 2021-01-01 at 7.59.43 PM](https://dev-to-uploads.s3.amazonaws.com/i/f16c4xi88v0ts9lvhxde.png)

If you do not install "Microsoft.EntityFrameworkCore.Design" package you'll get this error.

![Screen Shot 2021-01-01 at 8.01.18 PM](https://dev-to-uploads.s3.amazonaws.com/i/3i5x9q26iu4oq9uuoo3c.png)

Let's migrate our entity

![Screen Shot 2021-01-01 at 8.03.11 PM](https://dev-to-uploads.s3.amazonaws.com/i/jyzuluhdhu0247e2ms1d.png)

After this command, InitialMigration will be created

![Screen Shot 2021-01-01 at 8.21.41 PM](https://dev-to-uploads.s3.amazonaws.com/i/fh41xxigvz4z4y1y922h.png)

then update database 

![Screen Shot 2021-01-01 at 8.20.13 PM](https://dev-to-uploads.s3.amazonaws.com/i/3v3r5rg9vhtwbn7c6ip5.png)

# Pull Image

`docker pull postgres`
 
`docker run --name postgres -e POSTGRES_PASSWORD="123" -d -p 5432:5432 -v /var/lib/postgresql/data  postgres`

check docker image status

![Screen Shot 2021-01-01 at 8.26.55 PM](https://dev-to-uploads.s3.amazonaws.com/i/pi0m7atunm64igr2m2wc.png)

# Install Query Editor

I found a very useful tool for PostgreSQL

[Query Editor](https://marketplace.visualstudio.com/items?itemName=ckolkman.vscode-postgres)

```
{
  "label": "postgre",
  "host": "localhost",
  "user": "postgres",
  "port": 5432,
  "ssl": false,
  "database": "Community",
  "password": "123"
}
```
![Screen Shot 2021-01-01 at 9.06.59 PM](https://dev-to-uploads.s3.amazonaws.com/i/80pm918jo6iek8hr6fpc.png)

# Basic Queries

`SELECT * FROM "Authors" LIMIT 1000;`

![Screen Shot 2021-01-01 at 9.06.40 PM](https://dev-to-uploads.s3.amazonaws.com/i/k8u7mjyr0viazy7n40i9.png)

[Part II](https://dev.to/bseyhan/postgresql-with-ef-part-ii-5cf)
Happy new year 🎄