Entity Framework Core with Postgres

With last week’s post, I now have Postgres running in a Docker container. This post is going to cover using Entity Framework Core with Postgres which will happen to be running in the Docker container from last week, but that bit isn’t a requirement for this post. This post is going to be very similar to this one which covered Entity Framework Core with SQLite.

Starting Point

Using Visual Studio 2017 I started with a new ASP.NET Core project using the Web Application template with Individual User Accounts for Authentication. Using Individual User Accounts is an easy way to get all the Entity Framework Core stuff setup.

Add Postgres Nuget Package

Right-click on the project file and click Manage NuGet Packages.

On the Browse tab search for Npgsql.EntityFrameworkCore.PostgreSQL and then click install.

Configuration Changes

Open the appsettings.json file and change the DefaultConnection in the ConnectionString section to a valid Postgres connection string as in the following example.

Before:
"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-Postgres-53bc9b9d-9d6a-45d4-8429-2a2761773502;Trusted_Connection=True;MultipleActiveResultSets=true"
}

After:
"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=aspnet-Postgres-53bc9b9d-9d6a-45d4-8429-2a2761773502;UserId=yourUserName;Password=yourPassword"
}

Not that a user ID and password are needed. If you are going to be checking your project into source control I recommend in addition to the above you only store the connection string with the real user ID and password in user secrets which don’t get checked into source control. You can find more information on user secrets here.

Startup Changes

The final change needed before running the application is in the ConfigureServices function of the Startup class to switch out SQL Server for Postgres. The following shows an example of the change for the ApplicationDbContext.

Before:
services.AddDbContext<ApplicationDbContext>(options =>
    options
      .UseSqlServer(Configuration
                    .GetConnectionString("DefaultConnection")));

After:
services.AddDbContext<ApplicationDbContext>(options =>
    options
      .UseNpgsql(Configuration
                 .GetConnectionString("DefaultConnection")));

Run the App

Running the application at this point will work fine until you hit a function that wants to talk to the database. For example, attempting to register a user would result in the following error.

If you see this error don’t panic just follow the instructions and they will get you going. The simplest solution is to just click the blue Apply Migrations button and continue your testing.

Wrapping Up

The application is now ready to go using Postgres. The Entity Framework Core team, as well as the Postgres Entity Framework Core Provider team, have done a great job making everything work with no pain.

The official docs for the provider can be found here. I would love to offer some tooling recommendations for working with Postgres, but I haven’t been working with it enough yet to provide any. If you have any Postgres tools you recommend please leave a comment.

The code in its final state, which has been expanded to include my standard contacts example, can be found here.

---

Also published on Medium.