Connect to Postgres with EF Core

We can use an ASP.NET Core Web Application template to create our Web API. We will use Npgsql, a PostgreSQL database provider for Entity Framework Core. Install its package from NuGet with the command below.

PM>  Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

If you already have a Postgres database, add its connection string to appsettings.json as PostgreConnection or you can read Installing PostgreSQL and Loading Sample Data post in this series to install a database on your machine.

appsettings.json

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "Data": {
    "PostgreConnection": {
      "ConnectionString": "User ID=dvdclerk;Password=dvdsafe;Host=localhost;Port=5432;Database=dvdrental"
    }
  }
}

Write a model for film table of the sample data. Important part is Postgres is case sensitive here, so we need to define column and table names explicitly. Also EF wants to know the identity column of the table.

Models/Film.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFCore_Postgres.Models
{
  [Table("film", Schema="public")]
  public class Film
  {
    [Column("film_id")]
    [Key]
    public int ID { get; set; }

    [Column("title")]
    public string Title { get; set; }

  }
}

Now we have our model so we can create our database context like below;

ElephantContext.cs

using Microsoft.EntityFrameworkCore;

namespace EFCore_Postgres
{
  public class ElephantContext: DbContext
  {
    public ElephantContext(DbContextOptions<ElephantContext> options)
      : base(options)
    {
    }

    public DbSet<Models.Film> Films { get; set; }
  }
}

We have to add this context to container in ConfigureServices function in Startup class of our web application. Remember to add Microsoft.EntityFrameworkCore reference to usings.

Startup.cs

using Microsoft.EntityFrameworkCore;

...

public void ConfigureServices(IServiceCollection services)
{
  // Add framework services.
  services.AddMvc();

  services.AddDbContext<ElephantContext>(options => options.UseNpgsql(Configuration["Data:PostgreConnection:ConnectionString"]));
}

Congratulations, it is ready to use now. Let the built-in dependency injection of ASP.NET Core to initialize the database context for us in the sample ValuesController from Web API template.

Controllers/ValuesController.cs

private readonly ElephantContext _context;

public ValuesController(ElephantContext context)
{
  _context = context;
}

// GET api/values
[HttpGet]
public IEnumerable<string> Get()
{
  var films = _context.Films.Select(f => f.Title).ToList();

  return films;
}

We will have a list of movie titles as a response.

You can find source code of the project and my notes in olcay/EFCore_Postgres repo.