Entity Framework 6 Programmatically Connect to Postgres

Question

I'm working on programmatically establishing a connection to PostgresSQL using Entity Framework 6. I have this class:

public class ClearspanDatabaseContext : DbContext

with this constructor:

public ClearspanDatabaseContext()
    : base(buildConnectionString())
{
}

Here's the static method that makes the connection string programmatically:

private static string buildConnectionString()
{
    RegisterDbProvider("Npgsql", ".Net Framework Data Provider for Postgresql", "Npgsql Data Provider", "Npgsql.NpgsqlFactory, Npgsql");
    EntityConnectionStringBuilder entityConnectionStringBuilder = new EntityConnectionStringBuilder();
    entityConnectionStringBuilder.Provider = "Npgsql";
    entityConnectionStringBuilder.ProviderConnectionString = "host=192.168.168.140;Port=5432;username=ClearspanDevLogin;password=*******;database=ClearspanWebServerDev";
    return entityConnectionStringBuilder.ToString();
}

And here's the method that registers Npgsql as a database provider, taken from this source:

public static bool RegisterDbProvider(string invariant, string description, string name, string type)
{
    try
    {
        DataSet ds = ConfigurationManager.GetSection("system.data") as DataSet;
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            if (row["InvariantName"].ToString() == invariant)
            {
                return true;
            }
        }
        ds.Tables[0].Rows.Add(name, description, invariant, type);
        return true;
    }
    catch
    {
    }
    return false;
}

This generates a string like this:

"provider=Npgsql;provider connection string=\"host=192.168.168.140;Port=5432;username=ClearspanDevLogin;password=********;database=ClearspanWebServerDev\""

But I get an ArgumentException:

Keyword not supported: 'provider'.

I think I am close to the programmatic connection, but am missing something small. What can I do to resolve this exception and properly setup this connection programmatically? No app.config answers, I'm working in a class library, which ignores app.config (see the comments of the accepted answer to this question). This program must remain this way because it is used as a plugin - it does not (nor should it) run on its own. Thanks in advance.

Answer 1

Ok, here is working example for you which I verified is working. Using dummy code-first EF 6 model + custom DbConfiguration class:

public class Enrollment {
    public int EnrollmentID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
}

[DbConfigurationType(typeof (NpgsqlConfiguration))]
public class SchoolContext : DbContext {
    public SchoolContext(string cs) : base(cs) {
    }

    public DbSet<Enrollment> Enrollments { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {

    }
}

class NpgsqlConfiguration : System.Data.Entity.DbConfiguration
{
    public NpgsqlConfiguration()
    {
        SetProviderServices("Npgsql", Npgsql.NpgsqlServices.Instance);
        SetProviderFactory("Npgsql", Npgsql.NpgsqlFactory.Instance);
        SetDefaultConnectionFactory(new Npgsql.NpgsqlConnectionFactory());
    }
}

Then, instead of your buildConnectionString(), just pass postgre connection string in constructor:

using (var ctx = new SchoolContext("host=192.168.168.40;port=5432;...")) {                
            Console.WriteLine(ctx.Enrollments.ToArray());
        } 

And that is all. Config file is completely empty during that, and it works.

Answer 2

1. Have you looked at Code-Based Configuration? Create a DbConfiguration class with a public parameterless constructor in the same assembly as your DbContext

   class MyConfiguration : System.Data.Entity.DbConfiguration
   {
       public MyConfiguration()
       {
           SetProviderServices("Npgsql", Npgsql.NpgsqlServices.Instance);
           SetProviderFactory("Npgsql", Npgsql.NpgsqlFactory.Instance);
       }
   }
   

   Now I think the DbContext should use that provider factory by default, and you can construct the DbContext with just the connection string. But if it's in a different assembly, then you have a bit more work to do, but that can be found in the link above.

2. A potential problem with the above solution is that any configuration in the config file will take precedence, so maybe it would be safer to use the option described in here:

   var conn = DbProviderFactories.GetFactory("MY_CONN_PROVIDER").CreateConnection();
   conn.ConnectionString = "MY_CONN_STR";
   
   new DbContext(conn, true);
   

   where your provider is "Npgsql", which was registered in RegisterDbProvider above.

   Also see https://msdn.microsoft.com/en-us/library/dd0w4a2z(v=vs.110).aspx