How to setup PostgreSQL with Visual Studio 2017 or .NET 4.5+?

Question

There are several posts and articles related to the installation of PostgreSQL, but they are a bit confusing and provides different approaches for different versions.

This post will help those wanting to use PostgreSQL with Entity framework - Database first approach.

Requirements : VS 2017 PostgreSQL DB 11.1 EF 6+

Answer 1

STEP 1 :

Install latest version of PostgreSQL v-11.1 (did not try-out lower version)

STEP 2 :

Install npgsql .NET connector (highlighted in below image) by using the stack builder tool. [ Find it inside - ..\PostgreSQL\11\bin\stackbuilder.exe ]

STEP 3 :

Go to environment variable, modify path under System Variables and add lib and bin folder paths of PostgreSQL

• [Installed Drive]:\...\PostgreSQL\11\bin
• [Installed Drive]:\...\PostgreSQL\11\lib

STEP 4 :

Important! Download and install Npgsql.vsix extension for Visual Studio (Needed for Data Source wizard to provide you PostgreSQL as an option, when you want to assign connection to Entity Framework for DB access)

STEP 5 :

Create a new project. For EF 6+, add the following Nuget package -> EntityFramework6.Npgsql

Note : the above package will download the required dependencies for you

STEP 6 :

Add Entity Framework model and related connection settings to config file.

Finally add the below code snippet to config file :

<system.data>
    <DbProviderFactories>
      <remove invariant="Npgsql" />
      <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" support="FF" />
    </DbProviderFactories>
</system.data>

SIDE NOTE : No need for installing npgsql dll separately in GAC or VS assemblies as suggested in some articles, because the new version does this for you.