Entity Framework connection failed to postgres: "The underlying provider failed on Open"

Question

I am testing an Azure function project in a local environment, using Npgsql as a driver to postgres, connection strings defined in local.settings.json instead of App.config.

When I set Npgsql as the provider for Entity Framework and set the default connection factory to NpgsqlConnectionFactory, reading a DbSet in the DbContext produces the following error:

The underlying provider failed on Open. -> 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) -> The system cannot find the file specified

Here is my DbContext:

public partial class PostgresContext : DbContext
{
    public PostgresContext() : base("postgres-key")
    {

    }
}

public class NpgSqlConfiguration:DbConfiguration
{
    public NpgSqlConfiguration()
    {
        SetProviderFactory("Npgsql",NpgsqlFactory.Instance);
        SetDefaultConnectionFactory(new NpgsqlConnectionFactory());
    }
}

Here is my connection string:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "AzureWebJobsDashboard": ""
  },
  "ConnectionStrings": {
    "postgres-key": "Server=127.0.0.1;Port=5432;Database=postgres;User Id=postgres;Password=postgres;"
  }
}

I have tried replacing the Server section of the connection string with localhost, checking to see if the postgres 64 service is running, checked that port 5432 is open, checking whether Entity Framework works with local.settings.json.

The only way I have been able to move forward was creating my own NpgsqlConnection for manual database queries. I am trying to avoid plan B as much as possible and would appreciate any assistance in getting the ORM working.

Update: using .net framework 4.7.1

Answer 1

I revisited my reproduction of the issue thanks to the surge of helpers recently. I managed to get it working when this stack overflow post made me realize, I had omitted DbConfiguration.SetProviderServices() from the dbconfiguration inheritor. The npgsql documentation is terrible since it failed to mention this was required.

public class NpgSqlConfiguration : DbConfiguration
{
    public NpgSqlConfiguration()
    {
        SetProviderFactory("Npgsql", NpgsqlFactory.Instance);
        SetProviderServices("Npgsql", provider: NpgsqlServices.Instance);
        SetDefaultConnectionFactory(new NpgsqlConnectionFactory());
    }
}

here is my full reproduction (if anyone runs into a similar issue in the future): https://bitbucket.org/DCDprivate/entityframeworkissuereproduction