ASP.NET Entity Framework Core MVC Core Postgres

Getting started with Postgresql (npgsql), Entity Framework 7 (EF Core), and ASP.NET MVC 6

Finally, Entity Framework has support for databases other than SQL Server, and as many of us know, MVC 6 is cross-platform. This means we .NET devs can now launch our ASP.NET applications on a Linux server, saving huge on server costs and giving us some freedom. EF7 now supports SQL Server, Postgres, and SQLite, with Azure Table Storage and other non-relational data store support on the way. However, there is a noticable lack of offical MySQL support.

Why Postgres?

Linux

So why Postgresql? Well, for one, MySQL is not available and it doesn’t seem to be in the works (let me know if I’m wrong). So that makes Postgresql the only Linux-based full-featured database provider.

Strengths over SQL Server

A nameless author published an interesting blog comparing SQL Server and Postgres. There are a lot of strong arguments as to why Postgres is stronger, or at least comparable, to SQL Server. Technical reasons aside, Postgresql has zero licensing fees. 2nd Quandrant posted a related blog on this at with some valuable information.

JSON Support

Non-relational data support in your relational database. The best of both worlds! There isn’t direct EF7 LINQ support for this yet, but it is on the way. When it hits, this will solidify NPGSQL as my favorite provider.

Software Requirements

To get started, you’ll need the following:
1. The latest Visual Studio 2015 update and templates.
2. Postgesql
3. pgAdmin

Setting up your project

The following short steps will help you set up a Postgres database and update your ASP.NET application to use it as its default database connection.

1. Set up your database

Boot up pgAdmin and login with the super user credentials you supplied on installation. To create a database, drill into your localhost server, right click “Databases” and click “New Database”. Enter your database name and create it.

2. Set up your credentials

To create a user for your database, open up the query window for your localhost (the SQL icon in the icon bar). Run the following script:

create user {{username}} with password '{{password}}'; 
grant all privileges on database {{database name}} to {{username}};

This will give your new user all the priveleges on your new database.

3. Create your project

When you create your project, select the latest ASP.NET MVC 6 Web Application template. This will scaffold your user authentication models.

4. Add your npgsql dependencies

Here’s all of the Entity Framework packages you’ll need (as of the date this blog was posted). So, go to your project.json file and replace the existing references with these:

"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.Core": "7.0.0-rc1-final",
"EntityFramework.Relational": "7.0.0-rc1-final",
"EntityFramework7.Npgsql": "3.1.0-rc1-3",

NuGet should auto-restore these (isn’t MVC6 package management so much nicer?).

5. Delete all files in the Migrations folder

The current templates automatically add a starter migration for your application. However, they are specific to SQL Server, so they have to be deleted. Otherwise, you won’t be able to build.

6. Update the EF7 Startup configuration

In Startup.cs, you should see the following code:

services.AddEntityFramework()
.AddSqlServer()
.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

This sets up your EF7 context to work with the SQL Server provider. We don’t want that! Swap that code out with the following:

services.AddEntityFramework()
.AddNpgsql()
.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql(Configuration["Data:DefaultConnection:ConnectionString"]));

7. Update your DefaultConnection in your appsettings.json file

If you open up appsettings.json, you should see the following:

"Data": {
    "DefaultConnection": {
        "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database={{dbname}};Trusted_Connection=True;MultipleActiveResultSets=true"
    }
},

Postgres connection strings are a little different, so replace with the following:

"Data": {
    "DefaultConnection": {
        "ConnectionString": "Host=localhost;Username={{username}};Password={{password}};Database={{database name}}"
    }
},

8. Create and run the initialization migration

We will need to jump to command prompt/PowerShell for this, so open it up and CD to the folder with your XPROJ file. Once you’re there, execute the following:

dnx ef migrations add init
dnx ef database update

This will add your first migration with required ASP.NET Identity modeling and then run the migration against your database. After you’ve run these, refresh your server tree in pgAdmin and navigate to localhost -> Databases -> {{database name}} -> Schemas -> public -> Tables. You’ll see your new AspNet user tablesthere.

9. Run your application

That’s it! You’ve configured your application to use a Postgres database. You can register and look at your data in pgAdmin.

Have fun!

EF7 has a lot of changes, but for the most part, managing your context models is the same.

If something didn’t go right or you’re seeing some errors, let me know on Twitter @kerryritter.

20 Comments

  1. Dear author,
    thank you for your article. It’s a great and detailed tutorial for MVC6 and NPGSQL.

    The only one what worked for me!

  2. Hi,
    I am getting the following error after running database update command.
    The database is in my laptop and I can connect using pgAdmin with the same user. Please help.
    Npgsql.NpgsqlException (0x80004005): 28000: role “{{postgres}}” does not exist
    at Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage)
    at Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)
    at Npgsql.NpgsqlConnector.HandleAuthentication(NpgsqlTimeout timeout)
    at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout)
    at Npgsql.NpgsqlConnector.Open()
    at Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection)
    at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection connection)
    at Npgsql.NpgsqlConnection.OpenInternal(NpgsqlTimeout timeout)
    at Npgsql.NpgsqlConnection.Open()
    at Microsoft.Data.Entity.Storage.RelationalConnection.Open()
    at Microsoft.Data.Entity.Storage.Internal.NpgsqlDatabaseCreator.Exists()
    at Microsoft.Data.Entity.Migrations.HistoryRepository.Exists()
    at Microsoft.Data.Entity.Migrations.Internal.Migrator.Migrate(String targetMigration)
    at Microsoft.Data.Entity.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
    at Microsoft.Data.Entity.Commands.Program.Executor.c__DisplayClass7_0.b__0()
    at Microsoft.Data.Entity.Commands.Program.Executor.Execute(Action action)
    28000: role “{{postgres}}” does not exist

    1. Hey Kris, sorry for the late reply. Unfortunately I don’t know the issue here is. What version of DNX are you on, and what version of Postgres are you using?

    2. Hi, change in the connection string on appsettings.json {{postgres}} to just postgres, same for pass and database, that work for me, example:

      “ConnectionString”: “Host=localhost;Username=postgres;Password=desa2016;Database=sitsgc”

  3. Hi, I have a problem with executing command (“dnx ef migrations add init” for example) in Developer Command Prompt for VS2015 – I still get error “The name ‘dnx’ is not recognized as internal or external command…”. I did ‘cd’ to my project catalog. What should I do? Thanks!

    1. Have you tried running it in the Windows Command Prompt instead? I’m wondering if the DNX is not accessible in the Developer Command Prompt PATH.

  4. So I followed the guide and came across a few things. By the way this guide is really handy but there are few issues now. To get the database first style of development you need to add “EntityFramework7.Npgsql.Design” : “3.1.0-rc1-5”, after that you then need to add a port tot he connection string like so “Host=localhost;Port=5432;Username=aspnet_user;Password=aspnet2016;Database=aspnet”. Then one last thing If your on linux like I am make sure to run this in mono. The coreCLR doesnt play well with npgsql at all. It will time out with in milliseconds.

    Thank you all for taking the time to see my comment, Also thank you to the author for pointing me into the correct direction to get this working.

  5. Hi, i have done this and it worked, do someone know how to add an edmx of the postgres database, or hot to use existing schemas and tables in postgres, i haven’t foud how to add an ado .NET entity data model (edmx)

Leave a Reply

Your email address will not be published. Required fields are marked *