Search code examples
postgresqlef-code-firstnpgsql

Connection string changed unexpectedly in PostgreSQL code first

1- Installed Npgsql 3.1.9.0 and EntityFramework6.Npgsql.dll 31.0.0 dlls

2- Defined a DbContext as following

  public class MyDbContext : DbContext
{ 
    public MyDbContext()
        : base("myConnectionString")
    {

    }  
    public virtual DbSet<Tag> Tags { get; set; } 
     
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    { 
        modelBuilder.HasDefaultSchema("public"); 
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 
    }
}

3- And my app.config :

<configuration>
  <connectionStrings>
    <add name="myConnectionString" providerName="Npgsql" connectionString="Host=localhost;Port=5432;Database=mv_test;User Id=postgres;Password=devel;" />
  </connectionStrings>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v13.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
    </providers>
  </entityFramework>
</configuration>

4- My test snipped code

 MyDbContext myContext = new MyDbContext();
 int c = myContext.Tags.Count();

the exception :

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

but I can see the connection is default, is that connection string is not correct or something else? enter image description here

UPDATE:

After many tries and Installing Npgsql again and again I knew that <configSections> must be the first child of the root <configuration>, corrected it and run again hope to run properly but at the end I have got this exception:

Could not load file or assembly 'Npgsql, Version=3.1.2.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

I think some dependensies need to be referenced but I am newbie with Npgsl can anybody knows what went wrong.

Solution

  • YES, No pain No gain , There is no any barrier for a developer!

    After many problems with Npgsql and code first I get succeeded to run my snipped code but here some notes that my useful for someone

    As they said by installing Install-Package EntityFramework6.Npgsql by Nuget , Npgsql and all it's dependencies will be referenced without any problem but I have got many fails so by installing Install-Package EntityFramework6.Npgsql the following libraries be referenced as desc given bellow (Tuesday, November 22, 2016):

    1- Npgsql version 3.1.0.0

    2-EntityFramework6.Npgsql version 3.1.1.0

    3-EntityFramework and EntityFramework.SqlSrver version 6.0.0.0

    after establishing DbContext and data model you may get exception that said

    Could not load file or assembly 'Npgsql, Version=3.1.2.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

    because Ngpsql is not compatible with Entityframework6.Npgsql or another reason should be located Ngpsql located in C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.1.9.0__5d8b90d52f46fda7 is 3.1.9

    but to bypass this exception I have added Npgsql version 3.1.9.0 manually then code runs like a charm.

    so the App.config should like:

        <?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>
   <connectionStrings>
    <add name="myConnectionString" providerName="Npgsql" connectionString="Host=localhost;Port=5432;Database=mv_test;User Id=postgres;Password=devel;" />
  </connectionStrings>

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v13.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>
</configuration>

    hope this be useful for you.