How to use Entity Framework + PostgreSQL from connection?

entity-framework-4, npgsql, postgresql-9.1

Solution

You'll need to register the Npgsql provider in the `app/web.config`. See section 3.4 Using Npgsql with ProviderFactory of the Npgsql manual.

When you install an ADO.NET provider for databases (MySQL, PostgreSQL, etc.) the installers will usually register the provider assembly in the GAC and add an entry to the `machine.config`. If you want to deploy without having to install the provider you'll need to include a copy of the provider assembly (set Npgsql assembly reference as Copy Local for your project) and add an entry to your application's `app/web.config` 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 exactly the version of the Npgsql assembly you deploy with (or just omit the Version/Culture/PublicKeyToken). The `<Clear />` is there to avoid conflicts if running on a machine that already has entry for Npgsql in its `machine.config`. Without the clear you would get an exception. However, that's also assuming you're not relying on any other providers specified in the `machine.config` for your application.

Problem

I have already seen threads discussing the use of Entity Framework and PostgreSQL with official instructions. Those instructions need to run `gacutil` for every install which is not so handy for deployment purposes. What I want to do here is passing PostgreSQL connection directly to the `DbContext` constructor. This is enough for me because I am going to use `CodeFirst` without 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` with message: Unable to determine the provider name for connection of type 'Npgsql.NpgsqlConnection'. What should I do?

Original source