c#.net postgresql entity-framework-6 npgsql

Specifying database connection string for EntityFramework6.Npgsql - Code First

I have an issue regarding the .NET postgreSQL provider package EntityFramework6.Npgsql. I have installed the following packages: EntityFramework 6.0.0, EntityFramework6.NpgSql 3.1.1, Npgsql 3.1.0.

Currently I have just started a WPF project so I'm using a simple standard DbContext and it looks like this:

public class TimetrackerDbContext : DbContext
{
    public TimetrackerDbContext()
        : base("name=DbConnection")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("public");
        base.OnModelCreating(modelBuilder);
    }

    public virtual DbSet<MyEntity> MyEntities { get; set; }
}

public class MyEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Now in my App.config file I specify the connection string. I've been trying to use this connection:

<add name="DbConnection"
   connectionString="User ID=xxx;Password=xxx;Host=xxx;Port=5432;Database=xxx;Pooling=true;"
   providerName="System.Data.EntityClient" />

But when I run enable-migrations -contexttypename MyDbContext in the PM Console I get the following error:

Keyword not supported: 'user id'.

I tried specyfying the metadata, provider connection string but it also threw errors and it does not make much sense to use it in Code First. In case somebody it could be useful here's my whole App.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <!--<provider invariantName="System.Data.SqlClient"     type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />-->
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
    </providers>
  </entityFramework>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-3.1.0.0" newVersion="3.1.0.0"         />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <connectionStrings>
     <add name="DbConnection"
   connectionString="User ID=xxx;Password=xxx;Host=xxx;Port=5432;Database=xxx;Pooling=true;"
   providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Solution

The providerName of your connection string System.Data.EntityClient is for Database First (edmx) type connections where the actual provider is encoded in the connectionString value.

For Code First connections, the providerName should match the invariantName from the providers section. In your case, replace

providerName="System.Data.EntityClient"

providerName="Npgsql"