Entity Framework 5.0 PostgreSQL (Npgsql) default connection factory

Jaded picture Jaded · Feb 4, 2013 · Viewed 13.1k times · Source

I'm trying to get EF 5.0 code first working with PostgreSQL (Npgsql provider). I have Npgsql 2.0.12.1 installed via NuGet (referenced assembly is 2.0.12.0 though). I have Npgsql declared in app.config (both default connection factory and provider factory):

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

I have following test running successfully :

[Test]
public void DatabaseConnection_DatabaseFactoryTest()
{
    var factory = DbProviderFactories.GetFactory("Npgsql");
    var conn = factory.CreateConnection();
    conn.ConnectionString = _connectionString;
    var npg = (NpgsqlConnection)conn;
    var result = TestConnectionHelper(npg); // scalar select version(), nothing particular
    Assert.AreEqual(result, "PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit");            
}

That means at least database instance is running and provider is configured successfully. Now what i want is to use custom database context inherited from DbContext which will be tied to same provider and initialized via connection string :

public class InventoryContext : DbContext
{
    public InventoryContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
    }
    // mappings and properties, cut for conciseness
}

Following test fails :

[Test]
public void DatabaseConnection_DatabaseContextTest()
{
    using (var ctx = new InventoryContext(_connectionString))
    {
        //var db = ctx.Database;
        ctx.InventoryObjects.Add(_inventoryObject); // exception here
        ctx.SaveChanges();
    }
}   

It says

Failed to set Database.DefaultConnectionFactory to an instance of the 'Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' type as specified in the application configuration. See inner exception for details.

Inner exception is InvalidOperationException :

{"Constructor for type \"Npgsql.NpgsqlFactory\" is not found."}

I guess there is a problem with connection string (it does not contain Npgsql provider) :

"Server=127.0.0.1;Port=5432;User Id=postgres;Password=p4ssw0rd;Database=InventoryDatabase;";

What is the most elegant way to solve this problem programmatically? Just tried passing connectionString from app.config to context's constructor, it works.
edit
Uploaded test project to Dropbox - VS2012 solution, 10 mb

Answer

Jaded picture Jaded · Feb 11, 2013

Dug deeper into the problem, found out that it is caused by that fact that Npgsql is referencing EntityFramework 4.4.0 assembly. Solved as follows :

  1. Added EF Nuget package to test project (which is build against FW 4.5);
  2. Manually added reference to EntityFramework.dll version 5 in Npgsql2010 project (Nuget adds 4.4.0 by default);
  3. Changed assembly binding in Npgsql app.config to "EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
  4. Fixed described above "Constructor for type "Npgsql.NpgsqlFactory" is not found error by making constructor public;
  5. Fixed following error "Unable cast NpgsqlFactory to IDbConnectionFactory" by implementing IDbConnectionFactory interface on NpgsqlFactory :

    using System.Data.Entity.Infrastructure;
    ...
    public sealed class NpgsqlFactory : DbProviderFactory, IServiceProvider, IDbConnectionFactory
    ...
    public DbConnection CreateConnection(string nameOrConnectionString)
    {
    return new NpgsqlConnection(nameOrConnectionString);
    }
    d

Now i'm experiencing "Error: 3F000: schema "dbo" doesn't exist" which is related to EF. I have mapping to PostgreSQL standard public schema in DbContext's OnModelCreating : modelBuilder.Entity().ToTable("TableName", "public") though. Looking forward to solution of this problem.