How to use Entity Framework + PostgreSQL from a connection?

Question

I have already seen discussion topics about using Entity Framework and PostgreSQL with official instructions. These instructions should run gacutil for each installation, which is not very convenient for deployment.

What I want to do is pass the PostgreSQL connection directly to the DbContext constructor. This is enough for me, because I will use CodeFirst without a designer. This is what I do:

 public class Context : DbContext { Context(System.Data.Common.DbConnection connection) : base(connection, true) { } public static Context CreateContext() { NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=********;Database=xxx;"); conn.Open(); return new Context(conn); } } 

But using this method, I get a NotSupportedException message with the message:

Unable to determine the provider name for the connection type 'Npgsql.NpgsqlConnection'.

What should I do?

Answer 1

You need to register the Npgsql provider in app/web.config . See Section 3.4 Using Npgsql with ProviderFactory in the Npgsql Manual.

When you install the ADO.NET provider for databases (MySQL, PostgreSQL, etc.), installers usually register the vendor assembly in the GAC and add an entry to machine.config . If you want to deploy without the need to install a provider, you will need to specify a copy of the vendor assembly (set the link to the Npgsql assembly as Copy local for your project) and add an entry to the app/web.config application as follows:

 <configuration> ... <system.data> <DbProviderFactories> <clear /> <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" /> </DbProviderFactories> </system.data> ... </configuraiton> 

Make sure the version matches the exact version of the Npgsql assembly that you are deploying (or just omit Version / Culture / PublicKeyToken). <Clear /> exists to avoid conflicts when starting up on a machine that already has an entry for Npgsql in its machine.config . Without a clear, you will get an exception. However, this also assumes that you do not rely on other providers specified in machine.config for your application.