How to use Entity Framework + PostgreSQL from connection?

Question

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?

Answer 1

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.